Help with proc transpose

[statadm]

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!

[statadm]

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]

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]

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!