Compare dates and check if an event occurs within "n" days

[NewUsrStat]

Hi guys, 

suppose to have the following dataset:

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Influenza_pathogen Death;
  format Admission date9. Discharge date9.;
cards;
0001  13JAN2017 25JAN2017  1   0
0001  22FEB2018 03MAR2018  0   0
0001  30JAN2019 04MAR2019  0   0
0002  01DEC2016 14DEC2016  0   0
0002  01DEC2016 14DEC2016  0   0
0002  25DEC2017 02JAN2018  1   0
0002  06JAN2018 09JAN2018  0   1
0003  09JAN2016 25JAN2016  1   1
0004  29JAN2018 12FEB2018  1   0
0004  29JAN2018 12FEB2018  0   0
0004  25FEB2018 26FEB2018  0   1
0004  25FEB2018 26FEB2018  0   0
;run;

Is there a way to get the following new dataset? 


data D1;
  input ID :$20. Admission :date09. Discharge :date09. Influenza_pathogen Death Index_death;
  format Admission date9. Discharge date9.;
cards;
0001  13JAN2017 25JAN2017  1   0   0
0001  22FEB2018 03MAR2018  0   0   0
0001  30JAN2019 04MAR2019  0   0   0
0002  01DEC2016 14DEC2016  0   0   0
0002  01DEC2016 14DEC2016  0   0   0
0002  25DEC2017 02JAN2018  1   0   0
0002  06JAN2018 09JAN2018  0   1   1
0003  09JAN2016 25JAN2016  1   1   1
0004  29JAN2018 12FEB2018  1   0   0
0004  29JAN2018 12FEB2018  0   0   0
0004  25FEB2018 26FEB2018  0   1   1
0004  25FEB2018 26FEB2018  0   0   0
;run;

In other words if death (death = 1) occurs within 30 days from the date (discharge!) where influenza_pathogen = 1 (meaning an admission for influenza occurred at that date) than Index_death = 1. Note that some dates are repeated but not influenza pathogen and death indexes (e.g., 0004 29JAN2018 12FEB2018 and  0004 25FEB2018 26FEB2018). This should be maintained also for Index_death.

Note: when comparing dates please consider always discharge date.

Thank you in advance

[yabwon · Accepted solution]

Double DOW-loop should do the job:

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Influenza_pathogen Death;
  format Admission date9. Discharge date9.;
cards;
0001  13JAN2017 25JAN2017  1   0
0001  22FEB2018 03MAR2018  0   0
0001  30JAN2019 04MAR2019  0   0
0002  01DEC2016 14DEC2016  0   0
0002  01DEC2016 14DEC2016  0   0
0002  25DEC2017 02JAN2018  1   0
0002  06JAN2018 09JAN2018  0   1
0003  09JAN2016 25JAN2016  1   1
0004  29JAN2018 12FEB2018  1   0
0004  29JAN2018 12FEB2018  0   0
0004  25FEB2018 26FEB2018  0   1
0004  25FEB2018 26FEB2018  0   0
;run;

proc print;
run;

data want;
  do until(last.ID);
    set DB;
    by ID;
    if Death then death_dt=Discharge;
  end;

  do until(last.ID);
    set DB;
    by ID;
    if death_dt then 
      do;
        if Influenza_pathogen = 1 then XXX+(abs(Discharge-death_dt)<30);
      end;
    Index_death = death AND XXX;
    output;
  end;
  call missing(of _all_);
  drop death_dt XXX; 
run;

proc print;
run;

[rudfaden]

You coulc use retain to retain the dischage date where Influenza_pathogen=1

data have;
  input ID :$20. Admission :date09. Discharge :date09. Influenza_pathogen Death;
  format Admission date9. Discharge date9.;
cards;
0001  13JAN2017 25JAN2017  1   0
0001  22FEB2018 03MAR2018  0   0
0001  30JAN2019 04MAR2019  0   0
0002  01DEC2016 14DEC2016  0   0
0002  01DEC2016 14DEC2016  0   0
0002  25DEC2017 02JAN2018  1   0
0002  06JAN2018 09JAN2018  0   1
0003  09JAN2016 25JAN2016  1   1
0004  29JAN2018 12FEB2018  1   0
0004  29JAN2018 12FEB2018  0   0
0004  25FEB2018 26FEB2018  0   1
0004  25FEB2018 26FEB2018  0   0
;run;


data want;
set have;
retain influenza_discharge;
if Influenza_pathogen then influenza_discharge=Discharge;
if death=1 and Discharge-influenza_discharge<=30 then Index_death=1;
else Index_death=0;
drop influenza_discharge;
run;