Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Transposing variables to observations

Accepted Solution Solved
Reply
Regular Contributor
Posts: 218
Accepted Solution

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

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,


Accepted Solutions
Solution
‎04-27-2015 02:01 PM
Respected Advisor
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;
   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;

View solution in original post


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

Re: Transposing variables to observations

Posted in reply to M_Maldonado

thx Miguel

Super User
Posts: 11,343

Re: Transposing variables to observations

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.

Regular Contributor
Posts: 218

Re: Transposing variables to observations

thanks ballardw, your code worked!

Solution
‎04-27-2015 02:01 PM
Respected Advisor
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;
   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;
Super Contributor
Posts: 337

Re: Transposing variables to observations

Posted in reply to data_null__

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

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

!

True tip of the hat!

Regular Contributor
Posts: 218

Re: Transposing variables to observations

Posted in reply to data_null__

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,


Respected Advisor
Posts: 3,156

Re: Transposing variables to observations

Respected Advisor
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 $;
   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 Smiley Surprisedbs 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;

Capture.PNG
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 619 views
  • 2 likes
  • 5 in conversation