## Transposing variables to observations

Solved
Regular Contributor
Posts: 218

# Transposing variables to observations

Hi All,

I have the following table with patients' admission dates at different time. They are sorted by patient_ID and then by Admission_Date

101               Jan-01-2014

101               Jan-04-2014

101               Jan-09-2014

102               Jan-09-2014

102               Jan-10-2014

103               Jan-13-2014

103               Jan-14-2014

103               Jan-20-2014

103               Jan-29-2014

Can anyone tell me the code so that I can arrange my output table like this:

101               Jan-01-2014          Jan-04-2014     Jan-09-2014

102               Jan-09-2014          Jan-10-2014

103               Jan-13-2014          Jan-14-2014     Jan-20-2014        Jan-29-2014

Thanks,

Accepted Solutions
Solution
‎04-27-2015 02:01 PM
Posts: 3,799

## Re: Transposing variables to observations

You could rename the enumerated list without knowing the exact dimension by employing a friendly system option and the somewhat new rename enhancement.

data test;

cards;
101               Jan-01-2014
101               Jan-04-2014
101               Jan-09-2014
102               Jan-09-2014
102               Jan-10-2014
103               Jan-13-2014
103               Jan-14-2014
103               Jan-20-2014
103               Jan-29-2014
;;;;
run;
proc print;

run;
options dkrocond=nowarn;
by patient_ID;
run;
proc print;

run;

All Replies
Super Contributor
Posts: 337

## Re: Transposing variables to observations

Use proc transpose by patient_id and id admission_date

I hope it helps!

Regular Contributor
Posts: 218

thx Miguel

Super User
Posts: 11,343

## Re: Transposing variables to observations

by patient_ID;

run;

The variables will be Admission_1 not Admission_01. If you know that here will not be more than 9 values for any patient you could use prefix=Admission_0 to force the appearance of the 0 but if there are more than 9 you would get things like Admission_010 instead of Admission_10.

Regular Contributor
Posts: 218

## Re: Transposing variables to observations

Solution
‎04-27-2015 02:01 PM
Posts: 3,799

## Re: Transposing variables to observations

You could rename the enumerated list without knowing the exact dimension by employing a friendly system option and the somewhat new rename enhancement.

data test;

cards;
101               Jan-01-2014
101               Jan-04-2014
101               Jan-09-2014
102               Jan-09-2014
102               Jan-10-2014
103               Jan-13-2014
103               Jan-14-2014
103               Jan-20-2014
103               Jan-29-2014
;;;;
run;
proc print;

run;
options dkrocond=nowarn;
by patient_ID;
run;
proc print;

run;
Super Contributor
Posts: 337

## Re: Transposing variables to observations

Nice! I didn't know rename would work like that

!

True tip of the hat!

Regular Contributor
Posts: 218

## Re: Transposing variables to observations

Hi data _null_, you helped me a while ago with transposing one variable and I really liked the way. Can you please tell me if it is possible to transpose two variables at the same time. My table is:

data Apple.test;

cards;

101               Jan-01-2014        A

101               Jan-04-2014        B

101               Jan-09-2014        C

102               Jan-09-2014        F

102               Jan-10-2014        G

103               Jan-13-2014        T

103               Jan-14-2014        N

103               Jan-20-2014        F

103               Jan-29-2014        T

;

run;

Expected output:

101               Jan-01-2014          Jan-04-2014     Jan-09-2014                                A               B               C

102               Jan-09-2014          Jan-10-2014                                                       F               G

103               Jan-13-2014          Jan-14-2014     Jan-20-2014        Jan-29-2014      T               N               F                    T

Thanks,

Posts: 3,156

Posts: 3,799

## Re: Transposing variables to observations

You can't do that transpose, two or more variable to wide, with one PROC TRANSPOSE step.  You can use the PROC SUMMARY trick if you have LE 100 obs per PATIENT_ID.

If you want the leading zero you can use the rename trick but there is really no need for the leading zeros.

data Apple;
input patient_ID:\$3.   Admission_Date: anydtdte. Hospital \$;

cards;
101               Jan-01-2014        A
101               Jan-04-2014        B
101               Jan-09-2014        C
102               Jan-09-2014        F
102               Jan-10-2014        G
103               Jan-13-2014        T
103               Jan-14-2014        N
103               Jan-20-2014        F
103               Jan-29-2014        T
;;;;
run;
proc print;

run;
%let obs=1;
proc sql noprint;

select max(obs) into bs from (select count(*) as obs from apple group by patient_id);
quit;

run;
options dkrocond=nowarn;
proc summary data=apple;
by patient_id;
output

out=wide
(

drop=_type_

)

run;
options dkrocond=warn;
proc print;

run;

🔒 This topic is solved and locked.