topic Re: Transposing data following replacement of missing dates in SAS Programming

Transposing data following replacement of missing dates

DeepakSwain — Mon, 17 Apr 2017 15:50:11 GMT

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,

Re: Transposing data following replacement of missing dates

art297 — Mon, 17 Apr 2017 16:36:09 GMT

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

Re: Transposing data following replacement of missing dates

Astounding — Mon, 17 Apr 2017 16:38:35 GMT

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;

Re: Transposing data following replacement of missing dates

Jagadishkatam — Mon, 17 Apr 2017 16:49:10 GMT

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;

Re: Transposing data following replacement of missing dates

DeepakSwain — Mon, 17 Apr 2017 17:20:57 GMT

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,

Re: Transposing data following replacement of missing dates

DeepakSwain — Mon, 17 Apr 2017 17:26:23 GMT

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

Re: Transposing data following replacement of missing dates

art297 — Mon, 17 Apr 2017 17:47:45 GMT

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

Re: Transposing data following replacement of missing dates

DeepakSwain — Mon, 17 Apr 2017 19:30:43 GMT

Thanks art297.

With regards,

Re: Transposing data following replacement of missing dates

Astounding — Mon, 17 Apr 2017 19:51:04 GMT

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.

Re: Transposing data following replacement of missing dates

DeepakSwain — Mon, 17 Apr 2017 20:19:11 GMT

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,

Re: Transposing data following replacement of missing dates

DeepakSwain — Tue, 18 Apr 2017 12:27:38 GMT

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,