create a summary table

[apolitical]

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?

[Shmuel]

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;

[art297]

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

[apolitical]

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

[art297]

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

Art, CEO, AnalystFinder.com

[apolitical]

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.

[art297]

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

[Shmuel]

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;