BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sharonlee
Quartz | Level 8

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

2 REPLIES 2
Reeza
Super User
proc sort data=have;
by teacher year;
run;

proc transpose data=have out=want prefix=Y;
id year;
by teacher;
var count;
run;
sharonlee
Quartz | Level 8
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!

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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