topic Re: PROC transpose on multiple columns? in SAS Procedures

PROC transpose on multiple columns?

deleted_user — Fri, 29 May 2009 14:31:54 GMT

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

Re: PROC transpose on multiple columns?

sbb — Fri, 29 May 2009 15:20:52 GMT

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.

Re: PROC transpose on multiple columns?

sbb — Fri, 29 May 2009 15:39:14 GMT

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

Re: PROC transpose on multiple columns?

deleted_user — Fri, 29 May 2009 17:37:19 GMT

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

Re: PROC transpose on multiple columns?

deleted_user — Fri, 29 May 2009 18:52:56 GMT

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.

Re: PROC transpose on multiple columns?

deleted_user — Fri, 29 May 2009 19:01:45 GMT

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.

Re: PROC transpose on multiple columns?

deleted_user — Fri, 29 May 2009 19:04:14 GMT

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.

Re: PROC transpose on multiple columns?

sbb — Fri, 29 May 2009 19:11:36 GMT

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.