BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DeepakSwain
Pyrite | Level 9

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,

 

Swain
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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;

View solution in original post

10 REPLIES 10
art297
Opal | Level 21

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

 

DeepakSwain
Pyrite | Level 9

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

Swain
Astounding
PROC Star

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.

DeepakSwain
Pyrite | Level 9

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,

Swain
Astounding
PROC Star

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;

DeepakSwain
Pyrite | Level 9
Hi Astounding,
This is a great solution. Only correction, I did is the number of intervention from 4 to 3.
By the way, can you suggest me the way to address it using proc transpose. Just for my knowledge. Regards,
Swain
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
art297
Opal | Level 21

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

 

DeepakSwain
Pyrite | Level 9

Thanks art297. 

With regards,

 

Swain
DeepakSwain
Pyrite | Level 9

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,

 

Swain

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1078 views
  • 4 likes
  • 4 in conversation