Let's say we have a table like this:
Name Year Amount
Jim 2001 50
Jim 2001 60
Jim 2002 40
Jim 2002 60
Jason 2002 40
Jason 2002 45
Jason 2003 40
Jason 2003 60
How do I get to a table like this, with the most efficient way to code. Also suppose there are many years, and I prefer not to hand spell all the year values.
Name 2001_total 2002_total 2003_total Total
Jim 110 100 . 210
Jason . 85 100 185
Total 110 185 100 395
Particularly, when you add up two variables, one of which take a missing value, the resulting value is also missing, instead of just equal to the non-missing value and treating the missing value as 0. How to get around that?
The easyest way to gat a suumary table is by PROC TABULATE:
proc tabulate data=have missing ;
calss name year;
var amount;
table name, (year all)*amount*f=comma12.;
run;
The sum function ignores missing values. However, you could create what you want with either proc report, or code like:
proc summary data=have; var amount; by name year notsorted; output out=want (drop=_:) sum=total; run; proc transpose data=want prefix=total_ out=want (drop=_:); var total; by name notsorted; id year; run; data want; set want; total=sum(of total:); run; proc print data=want; sum total:; run;
Art, CEO, AnalystFinder.com
It gives the bottom row, just doesn't label it 'Total'.
Art, CEO, AnalystFinder.com
Not sure why you would want that, but you could use a datastep. It would required customizing the last step to account for all years actually represented in your data:
data have; input Name $ Year Amount; cards; Jim 2001 50 Jim 2001 60 Jim 2002 40 Jim 2002 60 Jason 2002 40 Jason 2002 45 Jason 2003 40 Jason 2003 60 ; proc summary data=have; var amount; by name year notsorted; output out=want (drop=_:) sum=total; run; proc transpose data=want prefix=total_ out=want (drop=_:); var total; by name notsorted; id year; run; data want (drop=_:); set want end=eof; total=sum(of total:); _total_2001+total_2001; _total_2002+total_2002; _total_2003+total_2003; _total+total; output; if eof then do; total_2001=_total_2001; total_2002=_total_2002; total_2003=_total_2003; total=_total; Name='Total'; output; end; run; proc print data=want; run;
Art, CEO, AnalystFinder.com
Sorry, I missed the bottomn total row. Check next code:
proc tabulate data=have missing ;
calss name year;
var amount;
table (name all='Total'),
(year all='Total')*amount*f=comma12.;
run;
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.