Hi there,
For your kind information, I am having a datset with multiple intervention dates in each row which I want to transpose to get one intervention date per row. At the same time, as some of the intervention dates are missing, I want to replace with the missing intervention date with the admission date.
data have; id=1; admission_date="20140102"; intervention_1=101 ; intervention_1_date="20140102" ; intervention_2=102 ; intervention_2_date="20140103" ; intervention_3=103 ; intervention_3_date="20140103" ;output; id=2; admission_date="20140102"; intervention_1=102 ; intervention_1_date="20140102" ; intervention_2=103 ; intervention_2_date="20140102" ; intervention_3=102 ; intervention_3_date="20140104" ;output; id=3; admission_date="20140103"; intervention_1=103 ; intervention_1_date="20140103" ; intervention_2=102 ; intervention_2_date=" " ; intervention_3=101 ; intervention_3_date="20140104" ;output; id=4; admission_date="20140104"; intervention_1=103 ; intervention_1_date=" " ; intervention_2=102 ; intervention_2_date="20140104" ; intervention_3=101 ; intervention_3_date="20140105" ;output; run; data want; input id admission_date intervention intervention_date; cards; 1 20140102 101 20140102 1 20140102 102 20140103 1 20140102 103 20140103 2 20140102 102 20140102 2 20140102 103 20140102 2 20140102 102 20140104 3 20140103 103 20140103 3 20140103 102 20140103 3 20140103 101 20140104 4 20140104 103 20140104 4 20140104 102 20140104 4 20140104 101 20140105 ; run;
Can somebody help me to solve the problem. Thank you in advance for your kind reply.
Regards,
Here's a way:
data want;
set have;
array inter {4} intervention_1 - intervention_4;
array interdt {4} intervention_1_date intervention_2_date intervention_3_date intervention_4_date;
do i=1 to 4;
intervention = inter{i};
intervention_date = interdt{i};
if intervention_date = ' ' then intervention_date = admission_date;
output;
end;
keep id admission_date intervention intervention_date;
run;
Easiest to do with arrays. e.g.:
data want (keep=id admission_date intervention intervention_date); set have; array int(*) intervention_1-intervention_3; array intd(*) $ intervention_1_date intervention_1_date intervention_3_date; do i=1 to dim(int); intervention=int(i); intervention_date=coalescec(intd(i),admission_date); output; end; run;
Art, CEO, AnalystFinder.com
Hi art297,
This is a great solution. By the way, can you suggest me the way to address it using proc transpose. Just for my knowledge.
Regards,
Deepak
Since you already have a PROC TRANSPOSE solution, I would suggest another line of study. When you are working with date variables, SAS has a preferred method of storing them that gives you great flexibility in displaying them and using them for calculations. That would be a worthwhile area to spend some time.
Hi Astounding,
Thanks for your advice. For your kind information, I am still trying to explore "Proc transpose" to transpose all 3 interventions to one column and all 3 intervention dates to another column without using array.
Till now what I learnt is transpose each intervention with associated with date and then use set. If I have large number of interventions, it becomes cumbersome process.
Can you suggest some efficient code to do that.
Thank you in advance for your kind reply.
Regards,
Here's a way:
data want;
set have;
array inter {4} intervention_1 - intervention_4;
array interdt {4} intervention_1_date intervention_2_date intervention_3_date intervention_4_date;
do i=1 to 4;
intervention = inter{i};
intervention_date = interdt{i};
if intervention_date = ' ' then intervention_date = admission_date;
output;
end;
keep id admission_date intervention intervention_date;
run;
PLease try the arrays which is more easier
data have;
id=1; admission_date="20140102"; intervention_1=101 ; intervention_1_date="20140102" ; intervention_2=102 ; intervention_2_date="20140103" ; intervention_3=103 ; intervention_3_date="20140103" ;output;
id=2; admission_date="20140102"; intervention_1=102 ; intervention_1_date="20140102" ; intervention_2=103 ; intervention_2_date="20140102" ; intervention_3=102 ; intervention_3_date="20140104" ;output;
id=3; admission_date="20140103"; intervention_1=103 ; intervention_1_date="20140103" ; intervention_2=102 ; intervention_2_date=" " ; intervention_3=101 ; intervention_3_date="20140104" ;output;
id=4; admission_date="20140104"; intervention_1=103 ; intervention_1_date=" " ; intervention_2=102 ; intervention_2_date="20140104" ; intervention_3=101 ; intervention_3_date="20140105" ;output;
run;
data have2;
set have;
by id admission_date;
array misd(3) intervention_1_date intervention_2_date intervention_3_date;
array misi(3) intervention_1 -intervention_3;
do i = 1 to 3;
if misd(i)='' then misd(i)=admission_date;
end;
do j= 1 to 3;
if misd(j) ne '' then do;
intervention_date=misd(j);
intervention_id=misi(j);
end;
output;
end;
run;
Only because you asked:
data have; id=1; admission_date="20140102"; intervention_1=101 ; intervention_1_date="20140102" ; intervention_2=102 ; intervention_2_date="20140103" ; intervention_3=103 ; intervention_3_date="20140103" ;output; id=2; admission_date="20140102"; intervention_1=102 ; intervention_1_date="20140102" ; intervention_2=103 ; intervention_2_date="20140102" ; intervention_3=102 ; intervention_3_date="20140104" ;output; id=3; admission_date="20140103"; intervention_1=103 ; intervention_1_date="20140103" ; intervention_2=102 ; intervention_2_date=" " ; intervention_3=101 ; intervention_3_date="20140104" ;output; id=4; admission_date="20140104"; intervention_1=103 ; intervention_1_date=" " ; intervention_2=102 ; intervention_2_date="20140104" ; intervention_3=101 ; intervention_3_date="20140105" ;output; run; proc transpose data=have out=one (drop=_: rename=(col1=intervention intervention_1_date=intervention_date)); by id admission_date; var intervention_1; copy intervention_1_date; run; proc transpose data=have out=two (drop=_: rename=(col1=intervention intervention_2_date=intervention_date)); by id admission_date; var intervention_2; copy intervention_2_date; run; proc transpose data=have out=three (drop=_: rename=(col1=intervention intervention_3_date=intervention_date)); by id admission_date; var intervention_3; copy intervention_3_date; run; data want; set one two three; intervention_date=coalescec(intervention_date,admission_date); run;
Art, CEO, AnalystFinder.com
Thanks art297.
With regards,
Hi art297,
Thank you for taking the pain to answer my query. You have nicely addressed by issue using proc transpose. For 3 interventions, this approach is manageable. If I have 10 intervention-codes and 10 intervention-dates. Can you kindly guide me the way to transpose all 10 intervention-codes to one column and all 10 intervention-dates to another column using proc transpose. By the way, use of array and do loop adviced by you is "Great".
Regards,
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.