DATA Step, Macro, Functions and more

data manipulation in SAS

Reply
Occasional Contributor
Posts: 17

data manipulation in SAS

Spoiler
 

I have a table like this

patient idSurgery date
116Aug2013
207Aug2013
209Aug2013

I would like to create two variables as the following table.  'After surgery days' field starts with 0, if there are more than 2 surgeries and the dates between them are more than 1 day, add date, 'After surgery days' between them; if less than 5 days, added at the end for date, and 'After surgery days' . Can anyone help me? Thanks.

patient idSurgery datedateAfter surgery days
116Aug201316Aug20130
1 17Aug20131
1 18Aug20132
1 19Aug20133
1 20Aug20134
207Aug201307Aug20130
2 08Aug20131
209Aug201309Aug20132
2 10Aug20133
2 11Aug20134
Super User
Posts: 5,518

Re: data manipulation in SAS

What if a patient had 2 surgeries, but they were 10 days apart?  Do you want a set of five days (beginning with 0) for each surgery for that patient?

Occasional Contributor
Posts: 17

Re: data manipulation in SAS

Posted in reply to Astounding
I only want 1 set of surgery day after the 1st surgery which means no reset
even there is a 2nd surgery.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
--
Thanks,
Lily
PROC Star
Posts: 7,492

Re: data manipulation in SAS

If I correctly understand what you are trying to do, the following will accomplish the task:

data have;
  input patient_id Surgery_date date9.;
  format Surgery_date date9.;
  cards;
1 16Aug2013
2 07Aug2013
2 09Aug2013
3 10Aug2013
3 20Aug2013
;

data want (drop=i day:);
  set have;
  array surgeries(10) _temporary_;
  by patient_id;
  if first.patient_id then do;
    call missing(of surgeries(*));
    i=0;
  end;
  i+1;
  surgeries(i)=Surgery_date;
  if last.patient_id then do;
    if i eq 1 then days=4;
    else days=surgeries(i)-surgeries(1);
    do After_surgery_days=0 to days;
      day=surgeries(1)+After_surgery_days;
      if day in surgeries then Surgery_date=day;
      else call missing(Surgery_date);
      output;
    end;
  end;
run;

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 17

Re: data manipulation in SAS

Art 297: Thank you very much for this! This almost accomplish what I need. But I would like to have fixed length of day for each patient . For example, length of 15 days or more for these all of patients which means the 1st one add 14 days after the 1st surgery; the 2nd one needs insert 1 day between two surgery days and 12 days after the 2nd surgery; the 3rd one need to insert 9 days between two surgery and add 4 days at the end.

PROC Star
Posts: 7,492

Re: data manipulation in SAS

@grace999: based on the example data I provided, please post what you want the output file to look like.

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 17

Re: data manipulation in SAS

@art297 The output looks like the following

patient_idSurgery_datedayAfter_surgery_days
116Aug201316Aug20130
1 17Aug20131
1 18Aug20132
1 19Aug20133
1 20Aug20134
1 21Aug20135
1 22Aug20136
1 23Aug20137
1 24Aug20138
1 25Aug20139
1 26Aug201310
1 27Aug201311
1 28Aug201312
1 29Aug201313
1 30Aug201314

2

07Aug201307Aug20130
2 08Aug20131
209Aug201309Aug20132
2 10Aug20133
2 11Aug20134
2 12Aug20135
2 13Aug20136
2 14Aug20137
2 15Aug20138
2 16Aug20139
2 17Aug201310
2 18Aug201311
2 19Aug201312
2 20Aug201313
2 21Aug201314
310Aug201310Aug20130
3 11Aug20131
3 12Aug20132
3 13Aug20133
3 14Aug20134
3 15Aug20135
3 16Aug20136
3 17Aug20137
3 18Aug20138
3 19Aug20139
320Aug201320Aug201310
3 21Aug201311
3 22Aug201312
3 23Aug201313
3 24Aug201314
Super User
Posts: 5,518

Re: data manipulation in SAS

[ Edited ]

It took me a while to come up with this (and it's untested ... can't test it until Wednesday), but here goes.  This assumes that all dates are valid SAS dates:

 

data want;

set have;

by patient_ID;

if first.patient_ID then after_surgery_days=0;

else after_surgery_days + 1;

output;

surgery_date = .;

if last.patient_ID then do after_surgery_days = after_surgery_days + 1 to 14;

   after_surgery_days + 1;

   output;

end;

else do;

   next_record = _n_ + 1;

   set have point=next_record (keep=surgery_date rename=(surgery_date = next_date));

   do after_surgery_days = after_surgery_days + 1 to next_date - 1;

      output;

   end;

end;

drop next_date next_record;

run;

 

The logic relies on SAS automatically skipping loops that run backwards, so it definitely requires testing to confirm that the values are coming out correctly for AFTER_SURGERY_DAYS.

PROC Star
Posts: 7,492

Re: data manipulation in SAS

I added a 3rd surgery for patient_id 3. Let us know if the following does what you want:

data have;
  input patient_id Surgery_date date9.;
  format Surgery_date date9.;
  cards;
1 16Aug2013
2 07Aug2013
2 09Aug2013
3 10Aug2013
3 20Aug2013
3 30Aug2013
;

data want (drop=i day:);
  set have;
  array surgeries(10) _temporary_;
  by patient_id;
  if first.patient_id then do;
    call missing(of surgeries(*));
    i=0;
  end;
  i+1;
  surgeries(i)=Surgery_date;
  if last.patient_id then do;
    days=14;
    if i gt 1 and surgeries(i)-surgeries(1) gt  14 then
      days=surgeries(i)-surgeries(1);
    do After_surgery_days=0 to days;
      day=surgeries(1)+After_surgery_days;
      if day in surgeries then Surgery_date=day;
      else call missing(Surgery_date);
      output;
    end;
  end;
run;

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 17

Re: data manipulation in SAS

[ Edited ]

@art297 Thanks a lot! Could you please help me to understand array surgeries(10. what does "

if day in surgeries " mean?

PROC Star
Posts: 7,492

Re: data manipulation in SAS

[ Edited ]

The surgeries array has to have the maximum number of surgeries that a patient might have. It has nothing to do with the number of records that you have.

 

As for the line: 

if day in surgeries then Surgery_date=day;

 the code, at that point, is looping through all of the dates being output and that line is simply checking to see if a surgery happened (for that patient) on the date being output. If it was, it adds the date to the surgery_date variable. 

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 17

Re: data manipulation in SAS

@art297 Thank you so much explaining this. I only used array for variables, not for records as you did for surgeries within each patient. It's good to know!

Valued Guide
Posts: 765

Re: data manipulation in SAS

[ Edited ]

Hi, using Art's data, here's another idea that get's the same results as Art's last posting ...

 

data x;
input id date date9. @@;
format date date9.;
datalines;
1 16Aug2013
2 07Aug2013 2 09Aug2013
3 10Aug2013 3 20Aug2013 3 30Aug2013
;

 

data y (drop=first s: diff);
array s(0:30) s0-s30;
do until(last.id);
  set x;
  by id;
  if first.id then first = date;
  s(date - first) = date;
end;
diff = coalesce(of s30-s0)-s0;
do after=0 to ifn(diff gt 14, diff, 14);
  date = s(after);
  output;
end;
run;

 

Occasional Contributor
Posts: 17

Re: data manipulation in SAS

[ Edited ]

@MikeZdeb Thank you very much for giiving me another solution. Would you please add one column  'day' variable in it?

patient_idSurgery_datedayAfter_surgery_days
116Aug201316Aug20130
1 17Aug20131
1 18Aug20132
1 19Aug20133
1 20Aug20134
1 21Aug20135
1 22Aug20136
1 23Aug20137
1 24Aug20138
1 25Aug20139
1 26Aug201310
1 27Aug201311
1 28Aug201312
1 29Aug201313
1 30Aug201314

2

07Aug201307Aug20130
2 08Aug20131
209Aug201309Aug20132
2 10Aug20133
2 11Aug20134
2 12Aug20135
2 13Aug20136
2 14Aug20137
2 15Aug20138
2 16Aug20139
2 17Aug201310
2 18Aug201311
2 19Aug201312
2 20Aug201313
2 21Aug201314
310Aug201310Aug20130
3 11Aug20131
3 12Aug20132
3 13Aug20133
3 14Aug20134
3 15Aug20135
3 16Aug20136
3 17Aug20137
3 18Aug20138
3 19Aug20139
320Aug201320Aug201310
3 21Aug201311
3 22Aug201312
3 23Aug201313
3 24Aug201314
PROC Star
Posts: 7,492

Re: data manipulation in SAS

Code with a day variable added:

data have;
  input patient_id Surgery_date date9.;
  format Surgery_date date9.;
  cards;
1 16Aug2013
2 07Aug2013
2 09Aug2013
3 10Aug2013
3 20Aug2013
3 30Aug2013
;

data want (drop=i days);
  set have;
  array surgeries(10) _temporary_;
  by patient_id;
  format day date9.;
  if first.patient_id then do;
    call missing(of surgeries(*));
    i=0;
  end;
  i+1;
  surgeries(i)=Surgery_date;
  if last.patient_id then do;
    days=14;
    if i gt 1 and surgeries(i)-surgeries(1) gt  14 then
      days=surgeries(i)-surgeries(1);
    do After_surgery_days=0 to days;
      day=surgeries(1)+After_surgery_days;
      if day in surgeries then Surgery_date=day;
      else call missing(Surgery_date);
      output;
    end;
  end;
run;

Art, CEO, AnalystFinder.com

Ask a Question
Discussion stats
  • 21 replies
  • 236 views
  • 5 likes
  • 4 in conversation