BookmarkSubscribeRSS Feed
grace999
Obsidian | Level 7
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
21 REPLIES 21
Astounding
PROC Star

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?

grace999
Obsidian | Level 7
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
art297
Opal | Level 21

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

 

grace999
Obsidian | Level 7

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.

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

grace999
Obsidian | Level 7

@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
Astounding
PROC Star

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.

art297
Opal | Level 21

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

grace999
Obsidian | Level 7

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

if day in surgeries " mean?

art297
Opal | Level 21

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

 

grace999
Obsidian | Level 7

@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!

MikeZdeb
Rhodochrosite | Level 12

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;

 

grace999
Obsidian | Level 7

@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
art297
Opal | Level 21

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

sas-innovate-2024.png

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.

 

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
  • 21 replies
  • 1711 views
  • 5 likes
  • 4 in conversation