Hi,
I'm using Proc FREQ to generate an output dataset like this:
Obs | teacher | Year | COUNT |
1 | Mr Smith | 2011/2012 | 16 |
2 | Mr Smith | 2012/2013 | 17 |
3 | Ms Blaire | 2011/2012 | 19 |
4 | Ms Blaire | 2012/2013 | 14 |
5 | Mr Patterson | 2011/2012 | 17 |
6 | Mr Patterson | 2012/2013 | 18 |
Ideally, I'd like my dataset to look like this:
Teacher | 2011/2012 | 2012/2013 |
Mr Smith | 16 | 17 |
Ms Blaire | 19 | 14 |
Mr Patterson | 17 | 18
|
So what I do is output a dataset for each teacher and use a macro to transpose the variables. The SAS code is like this:
Data mr_smith ms_blaire mr_patterson;
set db;
if teacher in: ("Mr Smith") then output mr_smith;
else if teacher in: ("Ms Blaire") then output ms_blaire;
else if teacher in: ("Mr Patterson") then output mr_patterson;
run;
%macro change (in=, out=, var=);
PROC TRANSPOSE DATA=&in. OUT=&out.;
VAR &variable.;
id year ;
run;
%mend;
%change(in=mr_smith out = mr_smith2, var=count);
%change(in=ms_blaire out = ms_blaire2, var=count);
%change(in=mr_patterson out = mr_patterson2, var=count);
data all;
set mr_smith2 ms_blaire2 mr_patterson2;
run;
This is pretty labourious, since I have multiple teachers. Is there any easier way to do this? I looked at proc tabulate, but it doesn't generate an appropriate output dataset. I thought an array would work, but I couldn't get that to work either.
Suggestions?
proc sort data=have;
by teacher year;
run;
proc transpose data=have out=want prefix=Y;
id year;
by teacher;
var count;
run;
proc sort data=have;
by teacher year;
run;
proc transpose data=have out=want prefix=Y;
id year;
by teacher;
var count;
run;
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.
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.