PROC transpose on multiple columns?

[deleted_user]

I know how PROC transpose works on a single column. But when you have 10 columns, how do you transpose them?

I have a data set like the following:

ID date1 date2 date3 date4 date5 rec1 rec2 rec3 rec4 rec5

I need to create a data set like this:

ID date rec

So, in the original data set, I have everything for ID 1 in one row, and everything for ID 2 in one row...I need to change to a data set that there are 5 rows for each ID. So for ID 1, I will have 5 rows with ID 1 repeating 5 times. How do you do this?

Or can I do it the hard way? Instead of trying to create a data set directly from the old data set, I can break down the old data set into 5 different data set and then use set to combine them back together. Will this work?

For example:

Creating data set 1:
data set1;
set old;
keep ID date1 rec1;
run;

Then I rename date1 and rec1 to date and rec and I will do this for date2-date5 and rec2-rec5. Then at the end, I can just combine them using a set statement like:

data new;
set set1 set2 set3 set4 set5;
run;

Will this work? In theory.


thanks Message was edited by: cosmid

[sbb]

This topic has been discussed on the SAS forums. Recommend a forum search for untranspose. Also, you should find information at the SAS support http://support.sas.com/ where you will want to focus on Data Step programming and using ARRAYs with a DO / END loop to take your horizontal (one observation, multiple analysis variables) data and make it vertical (one observation per analysis variable set, along with some set of key variables).

Scott Barry
SBBWorks, Inc.

[sbb]

This is a test reply - please ignore. Forum back-end server appears to be sick or maybe not.

[deleted_user]

Sorry, I wasn't aware there is already a topic about untranspose. And the website you provided, I don't think they provide anything for free. Thanks for the help.

[sbb]

Sorry for any confusion.

My suggestion was to search the SAS Discussion Forums using the term "untranspose" - and you would find this post:

http://support.sas.com/forums/thread.jspa?messageID=17509


Scott Barry
SBBWorks, Inc.

[deleted_user · Accepted solution]

One way of doing this through Proc Transpose each set of variables and merge together. See the below code. Let me know if it doen't work.

proc sort data=test;
by id;
run;

proc transpose data=test out=test1(rename=(col1=dat));
by id;
var dat1-dat5;
run;

proc transpose data=test out=test2(rename=(col1=rec));
by id;
var rec1-rec5;
run;

data test3 (drop=_name_);
merge test1 test2;
by id;
run;

~ Sukanya E

[deleted_user]

First time when I tried was using multiple proc transpose and then tried to merge the data back together. I don't remember what it did wrong but it didn't work out right. The method I listed above actually worked. Using multiple sets. I will try the multiple proc transpose again later and let you know if it works.

[deleted_user]

Multiple proc transpose works. I remember now. I just went through the data that I created before using multiple proc transpose. I think the multiple sets I posted earlier actually works better. You get more control over each variable compared to using multiple proc transpose.