DATA Step, Macro, Functions and more

Sum by two groups

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Sum by two groups

I have two datasets as below:

data2016:

itemqualityJunJulAug
applebad113
applegood689
orangebad121
orangegood496
peachbad223
peachgood101217

data2017:

itemqualityJunJulAug
applebad223
applegood131415
orangebad351
orangegood8713
cherrybad142
cherrygood795

 

Now I need to make a summary result as below:

summary:


item quality Jun Jul Aug

itemqualityJunJulAug
applebad336
applegood192224
orangebad472
orangegood121619
cherrybad142
cherrygood795
   peachbad           2           2           3
peachgood101217

 

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

Posted in reply to leonzheng

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

View solution in original post


All Replies
Respected Advisor
Posts: 2,812

Re: Sum by two groups

Posted in reply to leonzheng

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

Posted in reply to PaigeMiller

thanks, can you show me the code please?

Respected Advisor
Posts: 2,812

Re: Sum by two groups

Posted in reply to leonzheng
Contributor
Posts: 25

Re: Sum by two groups

Posted in reply to PaigeMiller
thank you
Solution
‎05-02-2018 01:56 PM
Super User
Posts: 23,262

Re: Sum by two groups

Posted in reply to leonzheng

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

Posted in reply to leonzheng

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 161 views
  • 2 likes
  • 3 in conversation