BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

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,

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

9 REPLIES 9
M_Maldonado
Barite | Level 11

Use proc transpose by patient_id and id admission_date

I hope it helps!

mlogan
Lapis Lazuli | Level 10

thx Miguel

ballardw
Super User

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
Lapis Lazuli | Level 10

thanks ballardw, your code worked!

data_null__
Jade | Level 19

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
Barite | Level 11

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

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

!

True tip of the hat!

mlogan
Lapis Lazuli | Level 10

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__
Jade | Level 19

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;

Capture.PNG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

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