DATA Step, Macro, Functions and more

create a summary table

Reply
Contributor
Posts: 73

create a summary table

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?

Trusted Advisor
Posts: 1,586

Re: create a summary table

Posted in reply to apolitical

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;
PROC Star
Posts: 7,492

Re: create a summary table

Posted in reply to apolitical

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

 

Contributor
Posts: 73

Re: create a summary table

This seems to do everything but not give the bottom row of my desired table (total), does it?
PROC Star
Posts: 7,492

Re: create a summary table

Posted in reply to apolitical

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

 

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 73

Re: create a summary table

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.
PROC Star
Posts: 7,492

Re: create a summary table

Posted in reply to apolitical

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

 

Trusted Advisor
Posts: 1,586

Re: create a summary table

Posted in reply to apolitical

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;
Ask a Question
Discussion stats
  • 7 replies
  • 162 views
  • 0 likes
  • 3 in conversation