Adding rows with aggregate / average values for all / some cases

[Robert Peterson]

Hi everyone,

I got the following (simplified) dataset that lists annual revenue for six
companies (firmID) located in three states (state = 1 to 3) for two years
(year = 2019, 2020).

Now I would like to do two things:

1. Add four rows per year that contain in "sales total" the aggregate sales
for all companies (firmID = -10) and and for the states (firmID = -3 to -1).
2. Add an additional column "average sales" that contains the sales average
for all companies (firmID = -10) and by state (firmID = -3 to -1).

In Excel I would simply define the cells accordingly and copy/paste, but how
would I do this in SPSS?

I understand how to compute new variables in SPSS but this doesn't seem to
be helpful since my datasheet will contain both values for individual
companies as well as aggregate / average values, sometimes mixed in the same
column as in the case of "sales total".

I've uploaded an excel sheet containing mock data.
200920_company-revenue_10.xlsx
<http://spssx-discussion.1045642.n5.nabble.com/file/t341838/200920_company-revenue_10.xlsx>  

Thanks in advance for your help!



--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Jon Peck]

You can do this with AGGREGATE followed by a merge (ADD CASES(), but I question the advisability of the resulting data structure.  What do you plan to do with it?  Statistical procedures would normally expect all the rows to have the same unit of observations in order for statistical results to be valid.

On Sun, Sep 20, 2020 at 9:48 AM homoludens1000 <[hidden email]> wrote:

Hi everyone,

I got the following (simplified) dataset that lists annual revenue for six
companies (firmID) located in three states (state = 1 to 3) for two years
(year = 2019, 2020).

Now I would like to do two things:

1. Add four rows per year that contain in "sales total" the aggregate sales
for all companies (firmID = -10) and and for the states (firmID = -3 to -1).
2. Add an additional column "average sales" that contains the sales average
for all companies (firmID = -10) and by state (firmID = -3 to -1).

In Excel I would simply define the cells accordingly and copy/paste, but how
would I do this in SPSS?

I understand how to compute new variables in SPSS but this doesn't seem to
be helpful since my datasheet will contain both values for individual
companies as well as aggregate / average values, sometimes mixed in the same
column as in the case of "sales total".

I've uploaded an excel sheet containing mock data.
200920_company-revenue_10.xlsx
<http://spssx-discussion.1045642.n5.nabble.com/file/t341838/200920_company-revenue_10.xlsx> 

Thanks in advance for your help!



--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Robert Peterson]

Hi Jon,

Thanks for the quick reply. I see your point and you are of course right.
The reason for this unorthodox (?) structure is that the dataset will be
used by a visualization software, and the idea is that you would be able to
choose for example your company and meaningful benchmark values (the total
sales / average sales in your state).

Best,

Rob



--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Jon Peck]

I would suggest saving all the aggregates as new variables rather than creating cases with a different observation unit.  That would be risky with most software.  Excel is totally undisciplined in this respect, but statistical software and databases would be problematic.

On Sun, Sep 20, 2020 at 10:10 AM Robert Peterson <[hidden email]> wrote:

Hi Jon,

Thanks for the quick reply. I see your point and you are of course right.
The reason for this unorthodox (?) structure is that the dataset will be
used by a visualization software, and the idea is that you would be able to
choose for example your company and meaningful benchmark values (the total
sales / average sales in your state).

Best,

Rob



--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD