Transposing variables to observations

[mlogan]

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

patient_ID      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:

patient_ID      Admission_01     Admission_02     Admission_03     Admission_04

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,

[M_Maldonado]

Use proc transpose by patient_id and id admission_date

I hope it helps!

[mlogan]

thx Miguel

[ballardw]

proc transpose data=work.junk out=work.trans prefix=Admission_;

by patient_ID;

var Admission_Date;

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.

[mlogan]

thanks ballardw, your code worked!

[data_null__ · Accepted solution]

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;
   input patient_ID:$3.   Admission_Date: anydtdte.;
   format admission_date date11.;
   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;
proc transpose data=work.test out=work.trans(rename=(adm1-adm99=Admission_01-Admission_99)) prefix=Adm;
   by patient_ID;
   var Admission_Date;
   run;
proc print;
   run;

[M_Maldonado]

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

(rename=(adm1-adm99=Admission_01-Admission_99))

!

True tip of the hat!

[mlogan]

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;

   input patient_ID:$3.   Admission_Date: anydtdte. Hospital$;

   format admission_date date11.;

   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:

patient_ID      Admission_01     Admission_02     Admission_03     Admission_04  Hosp_01     Hosp_02     Hosp_03       Hosp_04 

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,

[data_null__]

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 $;
   format admission_date date11.;
   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 :obs 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_
            rename=(admission_date_1-admission_date_99=Admission_Date_01-Admission_Date_99 hospital_1-hospital_99=Hospital_01-Hospital_99)
         )
      idgroup(out[&obs](admission_date hospital)=);
   run;
options dkrocond=warn;
proc print;
   run;