We’re smarter together. Learn from this collection of community knowledge and add your expertise.

How to turn rows into columns in SAS University Edition

by Community Manager ‎10-09-2015 04:46 PM - edited ‎10-11-2015 08:21 PM (212 Views)

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?

by Super Contributor
on ‎10-12-2015 01:29 PM

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;
data work.test2;
retain __obsnum;
set work.test;
by Rec_no var_name;
if (first.var_name eq 1)
      then __obsnum =1;
      else __obsnum=__obsnum+1;
output work.test2;
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;
    id var_name;
    var var_value;

How would you handle this?  I'm curious to see what other SAS users would do.


Happy Coding!


Your turn
Sign In!

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.