Set a new date variable based on the comparison of other dates

[NewUsrStat]

Hi guys, 

suppose to have the following: 

data DB;
  input ID :$20. Date_Admission :date9. Date_Discharge :date9. Event :$20. Date_Event :$20. Index :$20. Final_Date :$20.;
  format Date_Admission :date9. Date_Discharge :date9.  Final_Date :$20.;
cards;
0001  18OCT2016 18NOV2016  Change_department MAR2017   1    0
0001  17FEB2018 28FEB2018          .            .      0    0
0002  11SEP2014 15SEP2014  Change_department NOV2014   1    0
0002  13DEC2015 25DEC2015          .            .      0    0 
0003  12MAR2022 15MAR2022  Change_department APR2022   1    0
0004  15APR2016 18APR2016  Change_department AUG2016   1    0
0004  01JAN2018 15FEB2018  Death             DEC2018   0    0
0005  15APR2017 18APR2017  Change_department AUG2017   1    0
0005  01JAN2019 15FEB2019  Death             FEB2019   0    0
0006  01APR2022 18APR2017  Change_department MAY2022   1    0
0006  13MAR2023 01MAY2023  Death             MAY2023   0    0 
0007  11NOV2014 13NOV2014  Change_department JAN2015   1    0
....
run;

I have to set a Final_Date variable based on dates comparisons according to the following rules: 

1) If Index = 1 and Date_Event > Date_Discharge + 90 days
and missing (Date_Event) for all other combinations of Date_Admission-Date_Event for the same ID
then Final_Date = 30SEP2023 (e.g., ID = 0001)

2) If Index = 1 and Date_Event < Date_Discharge + 90 days then delete the entire ID independently
if there are other admissions (e.g., ID = 0002).

3) If Index = 1 and and Date_Event < Date_Discharge + 90 days and there is only one combination Date_Admission-Date_Discharge for that ID, then
since Date_Event < Date_Discharge + 90 days then delete (e.g., ID = 0003).

4) If Index = 1 and Date_Event > Date_Discharge + 90 days but there are other admissions-discharges
and also an event death, then the Final_Date = the date corresponding to the death. Since there
is only the indication of Month and Year, if the death is not in the same month
of the last admission-discharge, then set the date to the first day of the month (e.g., for
ID=0004 the Final_Date should be 01DEC2018). Otherwise, if the death is in the same month
of the last admission-discharge (e.g., ID= 0005) then the final_date should be set as the first day after the
corresponding discharge to avoid the final_date falls into the range last admission-discharge.

5) If Index = 1 and Date_Event < Date_Discharge + 90 days (e.g., ID = 0006) then remove all records corresponding
to that ID (as point 2) independently from the presence of event Death.

6) If Index = 1 and Date_Event > Date_Discharge + 90 days and there is only one admission-discharge
(e.g., ID = 0007) then, if the Date_Event is in the same month of the combination admission-discharge,
then the final_date should be the day after the discharge (e.g., ID = 0007), otherwise if not in the same month
than set at the first day of the month (e.g., ID = 0008).

Desired output:

data DB1;
  input ID :$20. Date_Admission :date9. Date_Discharge :date9. Event :$20. Date_Event :$20. Index :$20. Final_Date :date9.;
  format Date_Admission :date9. Date_Discharge :date9.  Final_Date :date9.;
cards;
0001  18OCT2016 18NOV2016  Change_department MAR2017   1    30SEP2022
0001  17FEB2018 28FEB2018         .             .      0    30SEP2022
0004  15APR2016 18APR2016  Change_department AUG2016   1    01DEC2018
0004  01JAN2018 15FEB2018  Death             DEC2018   0    01DEC2018
0005  15APR2017 18APR2017  Change_department AUG2017   1    16FEB2019
0005  01JAN2019 15FEB2019  Death             FEB2019   0    16FEB2018 
0007  11NOV2014 13NOV2014  Change_department NOV2014   1    14NOV2014
0008  12DEC2016 15JAN2017  Change_department FEB2017   1    01FEB2017
....
run;

Can anyone help me please? 

Thank you in advance, 

Best

[PaigeMiller]

Is the problem you are having that you don't know how to code this logic ? Or is the problem that you don't know how to determine 90 days after a certain date? Or both? Or neither?

[NewUsrStat]

I have troubles writing a code where many rules must be verified based on a number of comparisons at a time.

[Tom]

You need to figure out how to test all of the predicates you have in your mapping rules.  Once you know how to calculate those then applying the logic should be straight forward (as long as you consider all possible combinations).

So let's start by trying to understand what you rules mean and how to test each of the parts.

1) If Index = 1 and Date_Event > Date_Discharge + 90 days
and missing (Date_Event) for all other combinations of Date_Admission-Date_Event for the same ID
then Final_Date = 30SEP2023 (e.g., ID = 0001)

The first two seem straight forward, assuming there are variables named INDEX, DATE_EVENT and DATE_DISCHARGE in your dataset.

But you need to explain what you mean by "missing (Date_Event) for all other combinations of Date_Admission-Date_Event for the same ID"  

[NewUsrStat]

So, it means that there is not another event and accordingly a date reported . For a patient multiple admissions-discharges may happen but only one "Event" in the history of that patient with a date of the event

[PaigeMiller]

PROC SUMMARY/PROC MEANS/PROC FREQ will tell you if there are missings in that field for each patient in the data set. Then you merge those results back into your original data and then the logic is simple.