What do you do when your data’s in rows, but you want columns? SAS’ Robert Allison shows you how to restructure your data in this SAS Learning Post blog.
Have you tried this? How'd it work out for you?
Great article; I do have to admit that PROC Transpose has always baffled me. I've never been able to easily figure out what variables go where. Occasionally I have created the output that I wanted, but I have to confess there have been a number of times where I had to give up, throw the data into Excel, and done the transpose there.
The last time I needed to flip data, I was getting some bizarre error messages ("ERROR: The ID value "var_a" occurs twice in the same BY group"). I was frustrated - I couldn't figure out how to do what I needed. I went to SAS-L and emailed a couple of friends of mine, and the code below is the end result; basically, I'm assigning a sequence number to each combination of Rec_No and Var_Name. My dataset has three columns - Rec_No, Var_Name, Var_Value, and I'm trying to turn it so each Rec_No is on it's own row.
proc sort data=work.test;
by Rec_no Var_name;
by Rec_no var_name;
if (first.var_name eq 1)
then __obsnum =1;
proc sort data=work.test2 equals;
by rec_no __obsnum var_name;
proc transpose data=work.test2 out=work.test3(drop=_NAME_ _LABEL_ __obsnum);
by rec_no __obsnum;
How would you handle this? I'm curious to see what other SAS users would do.
Want to write an article? Sign in with your profile.
Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.