DATA Step, Macro, Functions and more

Transposing data following replacement of missing dates

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Transposing data following replacement of missing dates

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

Accepted Solutions
Solution
‎04-17-2017 01:21 PM
Super User
Posts: 5,504

Re: Transposing data following replacement of missing dates

Posted in reply to DeepakSwain

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


All Replies
PROC Star
Posts: 7,473

Re: Transposing data following replacement of missing dates

Posted in reply to DeepakSwain

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

 

Frequent Contributor
Posts: 104

Re: Transposing data following replacement of missing dates

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
Super User
Posts: 5,504

Re: Transposing data following replacement of missing dates

Posted in reply to DeepakSwain

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.

Frequent Contributor
Posts: 104

Re: Transposing data following replacement of missing dates

Posted in reply to Astounding

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
Solution
‎04-17-2017 01:21 PM
Super User
Posts: 5,504

Re: Transposing data following replacement of missing dates

Posted in reply to DeepakSwain

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;

Frequent Contributor
Posts: 104

Re: Transposing data following replacement of missing dates

Posted in reply to Astounding
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
Trusted Advisor
Posts: 1,137

Re: Transposing data following replacement of missing dates

Posted in reply to DeepakSwain

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
PROC Star
Posts: 7,473

Re: Transposing data following replacement of missing dates

Posted in reply to Jagadishkatam

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

 

Frequent Contributor
Posts: 104

Re: Transposing data following replacement of missing dates

Thanks art297. 

With regards,

 

Swain
Frequent Contributor
Posts: 104

Re: Transposing data following replacement of missing dates

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
☑ This topic is solved.

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

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