topic Calculate new dates based on a number of days but only if there is not an overlap in New SAS User

Calculate new dates based on a number of days but only if there is not an overlap

NewUsrStat — Tue, 07 Jan 2025 17:36:27 GMT

Hi guys,

suppose to have the following:

data DB;
  input ID :$20. Admission :date09. Discharge :date09.; 
  format Admission date9. Discharge date9.;
cards;
0001 13JAN2017 25JAN2017 
0001 27JAN2017 07MAR2017 
0001 11MAR2017 16MAY2017 
0001 30JAN2019 04MAR2019 
0002 11SEP2014 15SEP2014 
0002 28DEC2014 03JAN2015 
0002 05JAN2015 12MAR2015 
;

Now I want to calculate -14 days from Admission and +30days from Discharge but: if the days discharge-next admission is < 44 days then insert a missing.

The desired output is the following:


data DB1;
  input ID :$20. Admission :date09. Discharge :date09. Limit1 :date09. Limit2 :date09.; 
  format Admission date9. Discharge date9. Limit1 date9. Limit2 date9.;
cards;
0001 13JAN2017 25JAN2017  30DEC2016     .
0001 27JAN2017 07MAR2017      .         .  
0001 11MAR2017 16MAY2017      .     15JUN2017 
0001 30JAN2019 04MAR2019  16JAN2019 03APR2019
0002 11SEP2014 15SEP2014  28AUG2014 15OCT2014
0002 28DEC2014 03JAN2015  14DEC2014     .
0002 05JAN2015 12MAR2015      .     11APR2015
;

For example, for ID 0001 since there are only 2 days between 25JAN2017 and 27JAN2017 if I count 25JAN+30 or 27JAN -14 some overlap will occur. In this (and similar) case because of this overlap a missing should be inserted meaning that no considerations can be done. For ID= 0001 this happens for 3 sets of adm-disch (first 3 rows) but this is completely variable. In some cases there can be 5, 6 or 2 sets of dates that are hospitalizations that occur closely.

Can anyone help me please?

Re: Calculate new dates based on a number of days but only if there is not an overlap

Tom — Tue, 07 Jan 2025 18:16:01 GMT

Use LAG() to find the previous discharge.

Finding the next admission is harder (it is much easier to remember the past than to predict the future.) Here is a simple method using the FIRSTOBS= dataset option to re-read the dataset offset by one. Make sure to not misuse the PREV and NEXT values on the FIRST and LAST observation for a particular ID value.

data DB;
  input ID :$20. Admission :date. Discharge :date.; 
  format Admission Discharge date9.;
cards;
0001 13JAN2017 25JAN2017 
0001 27JAN2017 07MAR2017 
0001 11MAR2017 16MAY2017 
0001 30JAN2019 04MAR2019 
0002 11SEP2014 15SEP2014 
0002 28DEC2014 03JAN2015 
0002 05JAN2015 12MAR2015 
;

data want;
  set db ;
  by id admission ;
  set db(firstobs=2 keep=admission rename=(admission=next_admission)) db(obs=1 drop=_all_);
  prev_discharge=lag(discharge);
  limit1=admission-10;
  if not first.id and limit1<prev_discharge then limit1=.;
  limit2=discharge+30;
  if not last.id and limit2>next_admission then limit2=.;
  format limit1 limit2 prev_discharge next_admission date9.;
run;

proc print;
run;

Re: Calculate new dates based on a number of days but only if there is not an overlap

Tom — Tue, 07 Jan 2025 19:19:24 GMT

If you want to keep those NEXT/PREV variables then a simple change cleans them up.

  limit1=admission-10;
  if first.id then prev_discharge=.;
  else if limit1<prev_discharge then limit1=.;
  limit2=discharge+30;
  if last.id then next_admission=.;
  else if limit2>next_admission then limit2=.;