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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.