BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
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
1 ACCEPTED SOLUTION

Accepted Solutions
deleted_user
Not applicable
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

View solution in original post

7 REPLIES 7
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
This is a test reply - please ignore. Forum back-end server appears to be sick or maybe not.
deleted_user
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Not applicable
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
Not applicable
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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 35800 views
  • 0 likes
  • 2 in conversation