BookmarkSubscribeRSS Feed
statadm
Fluorite | Level 6
I have a dataset with 65 variables and over 100,000 observations that I need to transpose. It has to be done in two ways:

DATA survey;
INPUT id case result a1 a2 a3 ;
DATALINES;
12 1 1 1 0 1
12 2 1 0 1 1
12 2 2 1 1 1
12 3 1 1 0 1
12 3 2 1 1 1
13 1 1 0 0 0
13 1 2 1 1 1
13 2 1 0 0 0
13 3 1 1 0 1
;

I would like the output to look as follows:

id case result1 result2 result3 a1_1 a1_2 a1_3 a2_1 a2_2 a2_3 a3_1 a3_2 a3_3
12 1 1 . . 1 . . 0 . . 1 . .
12 2 1 2 . 0 1 . 1 1 . 1 1 .
12 3 1 2 . 1 1 . 0 1 . 1 1 .
13 1 1 2 . 0 1 . 0 1 . 0 1 .
13 2 1 . . 0 . . 0 . . 0 . .
13 3 1 . . 1 . . 0 . . 1 . .

This is so each case is in separate rows, but each associated result and all other variables are in the same row.

Thanks for any suggestions in advance. I have a macro (%macro split) that can transpose the whole dataset if there was only one result per case, but I'm not sure how to do this when I want multiple results associated with the same case.

Thanks!
3 REPLIES 3
statadm
Fluorite | Level 6
Correction to above:

The %split macro works when I want to associate all cases and results to one id so all data is in one row. I don't know how to associate the appropriate data to separate cases and different results to each associated case.

Thanks again!
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Consider splitting your file by CASE into separate files, prior to using PROC TRANSPOSE, and use a new PREFIX= value for each transpose execution, and lastly bring your files back together with SET (interleave) after ensuring you have unique SAS variables for each set of CASE observations. I do expect that a macro-driven process would apply here, creating a macro variable with the count of unique CASE values to drive the incrementing processing to build your PROC TRANSPOSE execution code.


Scott Barry
SBBWorks, Inc.
statadm
Fluorite | Level 6
Great thanks for your recommendation. I actually ended up doing it a little differently, but I couldn't have done it without your suggestions.

I did break it out by case and then broke it down again by result and ran the split macro on each result dataset and then merged back the results and set the cases.

Thank you, you saved me tons of time!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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