Hi guys,
suppose to have the following:
data DB;
input ID :$20. Admission :date09. Discharge :date09. Index Morbidity1 Morbidity2 Morbidity3 Morbidity4;
cards;
0001 13JAN2017 25JAN2017 1 1 0 1 0
0001 13JAN2017 25JAN2017 1 1 0 1 0
0001 22FEB2017 03MAR2017 0 0 1 0 0
0001 30JAN2019 04MAR2019 0 1 0 0 0
0002 01DEC2018 14DEC2018 0 1 0 1 0
0002 25DEC2018 02JAN2019 1 0 0 1 0
0002 25NOV2020 03DEC2020 0 1 1 1 1
0003 09JAN2016 25JAN2016 1 0 0 1 0
0003 29JAN2018 12FEB2018 0 0 0 1 1
...;
Is there a way to get the following?
data DB1;
input ID :$20. Admission :date09. Discharge :date09. Index Morbidity1 Morbidity2 Morbidity3 Morbidity4;
cards;
0001 13JAN2017 25JAN2017 1 1 0 1 0
0001 13JAN2017 25JAN2017 1 1 0 1 0
0001 22FEB2017 03MAR2017 0 0 0 0 0
0001 30JAN2019 04MAR2019 0 0 0 0 0
0002 01DEC2018 14DEC2018 0 1 0 1 0
0002 25DEC2018 02JAN2019 1 0 0 1 0
0002 25NOV2020 03DEC2020 0 0 0 0 0
0003 09JAN2016 25JAN2016 1 0 0 1 0
0003 29JAN2018 12FEB2018 0 0 0 0 0
...;
In other words if morbidity* occurs before or at Index = 1 then nothing happens, otherwise if Morbidity* happens after Index = 1 (meaning after the date where Index = 1) all values in Morbidity* columns should be set = 0. So, all "1s" will become 0.
Note that each patient has only one chance that Index = 1. In the reported example, pts 0001 has 2 records where Index = 1 but the date is the same. This could happen. Replicated rows are present because there are other variables not shown here. Finally, Morbidities* are 20 variables with specific name. Here Morbidity is reported for simplicity. I can customize column names in my data.
Thank you very much for your help.