topic Re: Update values in a set of variables if they appear before or after an index date in New SAS User

Update values in a set of variables if they appear before or after an index date

NewUsrStat — Sat, 01 Jun 2024 14:15:42 GMT

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.

Re: Update values in a set of variables if they appear before or after an index date

Ksharp — Sun, 02 Jun 2024 01:13:04 GMT

data DB;
  input ID :$20. Admission :date9. Discharge :date9. Index  Morbidity1  Morbidity2  Morbidity3  Morbidity4;
  format Admission  Discharge date9.;
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
;
data want;
do i=1 by 1 until(last.id);
 set DB;
 by ID;
 if Index=1 then _i=i;
end;
do i=1 by 1 until(last.id);
 set DB;
 by ID;
 if _i<i and not missing(_i) then do;Morbidity1=0;  Morbidity2=0; Morbidity3=0;  Morbidity4=0;  end;
 output;
end;
drop i _i;
run;

Re: Update values in a set of variables if they appear before or after an index date

Kurt_Bremser — Sun, 02 Jun 2024 06:55:02 GMT

data want;
set db;
by id admission; * admission to guarantee order by date;
array mo {*} morb:;
retain _index;
if first.id then _index = 0;
if index then _index = 1;
if _index
then do _i = 1 to dim(mo);
  mo{_i} = 0;
end;
drop _i _index;
run;