BookmarkSubscribeRSS Feed
apolitical
Obsidian | Level 7

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?

7 REPLIES 7
Shmuel
Garnet | Level 18

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
Opal | Level 21

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
Obsidian | Level 7
This seems to do everything but not give the bottom row of my desired table (total), does it?
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

apolitical
Obsidian | Level 7
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
Opal | Level 21

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
Garnet | Level 18

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1634 views
  • 0 likes
  • 3 in conversation