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;
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.
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.