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
... View more
