Solved
Contributor
Posts: 25

# Sum by two groups

I have two datasets as below:

data2016:

 item quality Jun Jul Aug apple bad 1 1 3 apple good 6 8 9 orange bad 1 2 1 orange good 4 9 6 peach bad 2 2 3 peach good 10 12 17

data2017:

 item quality Jun Jul Aug apple bad 2 2 3 apple good 13 14 15 orange bad 3 5 1 orange good 8 7 13 cherry bad 1 4 2 cherry good 7 9 5

Now I need to make a summary result as below:

summary:

item quality Jun Jul Aug

 item quality Jun Jul Aug apple bad 3 3 6 apple good 19 22 24 orange bad 4 7 2 orange good 12 16 19 cherry bad 1 4 2 cherry good 7 9 5
 peach bad 2 2 3 peach good 10 12 17

Logic is that sum the data of 2016 and 2017 result for each item, quality and month. I tried use cumulative sum and remove some rows but do not know how to calculate cumulative sum for several month like Jun Jul Aug, or is there any other easier way of doing this?

My cumulative sum code:

proc datasets;
append base=data2016 data=data2017;
run;

proc sort data=data2016;
by item quality;
run;

data summary;
set data2016;
by item quality;
if first.flag then June=Jun July=Jul August=Aug;
else June=Jun+June July=Jul+July August=Aug+August;
run;

then I will drop and rename columns and join summary having Aug=max(Aug), haven't done this step because the sum is still not working.

Please help to see how make the sum step working, and is there any other easier way to do this?

Thanks,

Accepted Solutions
Solution
‎05-02-2018 01:56 PM
Super User
Posts: 23,262

## Re: Sum by two groups

Don't use a data step to calculate summary statistics unless you're doing really customized calculations.

Try PROC MEANS instead and include both Item and Quality in your BY or CLASS statements.

This shows you how to use PROC MEANS with multiple variables and save the output to a data set. You can replace my feature1-feature3 with your Jun-Aug. You can run the code in the example below to see how the data is structured and how the results are generated.

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

All Replies
Posts: 2,812

## Re: Sum by two groups

Step 1: append the two data sets

Step 2: use PROC SUMMARY or PROC MEANS to do the sums, you need to set ITEM and QUALITY as CLASS variables.

--
Paige Miller
Contributor
Posts: 25

## Re: Sum by two groups

thanks, can you show me the code please?

Posts: 2,812

Contributor
Posts: 25

## Re: Sum by two groups

thank you
Solution
‎05-02-2018 01:56 PM
Super User
Posts: 23,262

## Re: Sum by two groups

Don't use a data step to calculate summary statistics unless you're doing really customized calculations.

Try PROC MEANS instead and include both Item and Quality in your BY or CLASS statements.

This shows you how to use PROC MEANS with multiple variables and save the output to a data set. You can replace my feature1-feature3 with your Jun-Aug. You can run the code in the example below to see how the data is structured and how the results are generated.

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

Contributor
Posts: 25

## Re: Sum by two groups

thans, I use

output out=summary sum=/ autoname;

then it works.

Super User
Posts: 23,262

## Re: Sum by two groups

Yes, or you can explicitly name them as desired. I usually try to have a common prefix since SAS works on prefixes not suffixes.

``output out=want mean(jun)= Avg_Jun mean(jul)=Avg_Jul .. etc;``
☑ This topic is solved.