SAS reporting totals

Question

I have a dataset that looks somewhat similar to the example below:

ID Year College Dept Deg Status

12 2014 Engineering CS MS Applied

12 2014 Engineering CS MS Admitted

12 2014 Engineering CS MS Enrolled

88 2013 Engineering ME PHD Applied

88 2013 Engineering ME PHD Admitted

44 2014 Engineering CE MS Applied

I am trying to produce a report that's more like this:

Year Applied Admitted Enrolled

2013 X Y Z

2014 X Y Z

I know how to filter the datasets for the College/Dept/Deg, but how can I use SAS to essentially make fields within a column headers? Any help is appreciated.

Answer 1

Assuming X, Y, and Z are supposed to be counts, proc sql works great for this sort of thing. Below, four columns are defined in the select clause. The first column is year. The second column, sum(status='applied'), counts the number of rows where the expression status='applied' is true, and so on for the next two columns.

proc sql;
select year,
       sum(status='Applied') as applied,
       sum(status='Admitted') as admitted,
       sum(status='Enrolled') as enrolled
from yourdata
group by year
;
quit;

You could also use proc report, and easily generate the columns dynamically (one column for each value found in status). year and group are both define as group variables. Across puts the statuses in columns rather than creating a row for each status:

proc report data=yourdata;
  columns year status;
  define year / group;
  define status / group across;
run;

You can get something similar to this with proc freq by just requesting a twoway frequency table with year*status. Use the norow, nocol, nopercent options to suppress all the junk that `proc freq normally spits out:

proc freq data=yourdata;
  tables year*status / norow nocol nopercent;
run;

Or you could use proc tabulate. Define year and status as class variables, and the following table statement will get you the counts you want:

proc tabulate data=yourdata;
  class year status;
  table year, status*n;
run;