topic create a summary table in SAS Programming

create a summary table

apolitical — Fri, 03 Mar 2017 00:12:51 GMT

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?

Re: create a summary table

Shmuel — Fri, 03 Mar 2017 01:27:46 GMT

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;

Re: create a summary table

art297 — Fri, 03 Mar 2017 01:28:06 GMT

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

Re: create a summary table

apolitical — Fri, 03 Mar 2017 14:56:12 GMT

This seems to do everything but not give the bottom row of my desired table (total), does it?

Re: create a summary table

art297 — Fri, 03 Mar 2017 15:05:17 GMT

It gives the bottom row, just doesn't label it 'Total'.

Art, CEO, AnalystFinder.com

Re: create a summary table

Shmuel — Fri, 03 Mar 2017 15:34:11 GMT

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;

Re: create a summary table

apolitical — Fri, 03 Mar 2017 16:19:16 GMT

yes the proc print at the end shows the table i want, but how do i save it as a dataset in the work library? thanks.

Re: create a summary table

art297 — Fri, 03 Mar 2017 17:25:55 GMT

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