BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leonzheng
Obsidian | Level 7

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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
leonzheng
Obsidian | Level 7

thanks, can you show me the code please?

PaigeMiller
Diamond | Level 26
leonzheng
Obsidian | Level 7
thank you
Reeza
Super User

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

leonzheng
Obsidian | Level 7

thans, I use 

output out=summary sum=/ autoname;

 

then it works.

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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