Simplifying SAS code: Array or proc tranpose?

[sharonlee]

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?

[Reeza · Accepted solution]

proc sort data=have;
by teacher year;
run;

proc transpose data=have out=want prefix=Y;
id year;
by teacher;
var count;
run;

[sharonlee]

Brilliant, Reeza!
You have saved me so much time!!
Appreciate the quick response.
Silly me, I should have realized that the "by teacher" would solve the trick!