topic Re: Transposing variables to observations in SAS Data Science

Transposing variables to observations

mlogan — Mon, 27 Apr 2015 17:11:43 GMT

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,

Re: Transposing variables to observations

M_Maldonado — Mon, 27 Apr 2015 17:14:04 GMT

Use proc transpose by patient_id and id admission_date

I hope it helps!

Re: Transposing variables to observations

ballardw — Mon, 27 Apr 2015 17:28:09 GMT

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.

Re: Transposing variables to observations

mlogan — Mon, 27 Apr 2015 17:32:55 GMT

thx Miguel

Re: Transposing variables to observations

mlogan — Mon, 27 Apr 2015 17:35:05 GMT

thanks ballardw, your code worked!

Re: Transposing variables to observations

data_null__ — Mon, 27 Apr 2015 18:01:55 GMT

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;

Re: Transposing variables to observations

M_Maldonado — Mon, 27 Apr 2015 19:45:44 GMT

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

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

True tip of the hat!

Re: Transposing variables to observations

mlogan — Wed, 19 Aug 2015 20:18:14 GMT

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,

Re: Transposing variables to observations

Haikuo — Wed, 19 Aug 2015 20:41:12 GMT

Re: Transposing variables to observations

data_null__ — Wed, 19 Aug 2015 21:00:32 GMT

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;