Good afternoon everyone! As always, I thought its pretty simple but got stuck. Here is the data-
Data have;
input code $ description $ totalSum totalUnits year;
cards;
A APPLE 100 3 2016
A APPLE 200 4 2015
A APPLE 300 5 2014
B BANANA 234 7 2015
C CIDER 20 1 2016
C CIDER 25 3 2016
C CIDER 12 1 2015
C CIDER 21 4 2015
C CIDER 23 4 2014
D DATE 200 4 2015
D DATE 567 34 2016
;
RUN;
What I'm looking for is that for those records within the same code, description and year, totalsum and totalunits values are added. For ex, if there is only one unique value for C, Cider and 2014, it should remain the same, but when C, Cider and 2014 are repeated again, totalsum values should be added to give one unique record for that year. To make it more clear-here is what I want-
data want;
input code $ description $ totalSum totalUnits year;
cards;
A APPLE 100 3 2016
A APPLE 200 4 2015
A APPLE 300 5 2014
B BANANA 234 7 2015
C CIDER 45 4 2016
C CIDER 33 5 2015
C CIDER 23 4 2014
D DATE 200 4 2015
D DATE 567 34 2016
;
RUN;
Thanks so much!
Not sure what you data actually look like but, from what I can tell, your description field may be the culpret. Try:
proc summary data=have nway; class code year; var totalSum totalUnits; output out=want (drop=_:) sum=; run;
Art, CEO, AnalystFinder.com
proc summary data=have nway; by code description year notsorted; var totalSum totalUnits; output out=want (drop=_:) sum=; run;
Art, CEO, AnalystFinder.com
It still gives me multiple code, description and year values.
If you are getting multiple values, it is because your data isn't sorted as shown in your example.
Use @Shmuel's code which does the same thing, but uses a class rather than a by statement. That way your data doesn't have to be presorted.
Art, CEO, AnalystFinder.com
proc summary data=have missing nway;
class code description year;
var totalSum totalUnits;
output out=want sum=;
run;
you can also use proc means instead proc summary.
Thanks sir, tried yours as well with my original data and the same result, my year is in form of d_2014 d_2015 and d_2016..hope that is not affecting results. Also, some descriptions are blank.
How a -ve sign in your code is showing the impact .pls explain in detail
Not sure what you data actually look like but, from what I can tell, your description field may be the culpret. Try:
proc summary data=have nway; class code year; var totalSum totalUnits; output out=want (drop=_:) sum=; run;
Art, CEO, AnalystFinder.com
Thanks again! Yes, that might be a problem. As I mentioned earlier, some of the description values are blank. I'm out now, but will try again tomorrow morning and will let you know if this works.
This seems to have worked. I guess I have to now merge this with original dataset on codes to get descriptions. Thanks again.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.