DATA Step, Macro, Functions and more

Simplifying SAS code: Array or proc tranpose?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Simplifying SAS code: Array or proc tranpose?

Hi,

I'm using Proc FREQ to generate an output dataset like this:

 

ObsteacherYearCOUNT
1Mr Smith2011/201216
2Mr Smith2012/201317
3Ms Blaire2011/201219
4Ms Blaire2012/201314
5Mr Patterson2011/201217
6Mr Patterson2012/201318

 

Ideally, I'd like my dataset to look like this:

 

Teacher2011/20122012/2013
Mr Smith1617
Ms Blaire1914
Mr Patterson17

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?

 


Accepted Solutions
Solution
‎03-13-2017 07:15 PM
Super User
Posts: 17,852

Re: Simplifying SAS code: Array or proc tranpose?

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

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

View solution in original post


All Replies
Solution
‎03-13-2017 07:15 PM
Super User
Posts: 17,852

Re: Simplifying SAS code: Array or proc tranpose?

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

proc transpose data=have out=want prefix=Y;
id year;
by teacher;
var count;
run;
Occasional Contributor
Posts: 14

Re: Simplifying SAS code: Array or proc tranpose?

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!
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 158 views
  • 2 likes
  • 2 in conversation