Create a new variable based on the combination of dates and two other variables

[NewUsrStat]

Hi guys, 

suppose to have the following:  

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Index  Risk;
cards;
0001  13JAN2017 25JAN2017   0     1    
0001  13JAN2017 25JAN2017   0     1    
0001  22FEB2017 03MAR2017   1     0    
0001  30JAN2019 04MAR2019   0     0    
0002  01DEC2018 14DEC2018   0     1    
0002  25DEC2018 02JAN2019   1     0    
0002  25NOV2020 03DEC2020   0     1    
0003  09JAN2016 25JAN2016   1     0    
0003  29JAN2018 12FEB2018   0     1    
0004  12APR2015 15APR2015   1     1
0004  02JUN2018 04JUN2018   0     0
0004  29JAN2021 12FEB2021   0     0
;
run;

In other words a column indicates an Index date while another column if a patient is at risk because of a comorbidity. Is there a way to get the following? 

data DB1;
  input ID :$20. Admission :date09. Discharge :date09. Index  Risk New_Risk;
cards;
0001  13JAN2017 25JAN2017   0     1    1
0001  13JAN2017 25JAN2017   0     1    1
0001  22FEB2017 03MAR2017   1     0    1
0001  30JAN2019 04MAR2019   0     0    0
0002  01DEC2018 14DEC2018   0     1    1
0002  25DEC2018 02JAN2019   1     0    1
0002  25NOV2020 03DEC2020   0     1    0
0003  09JAN2016 25JAN2016   1     0    0
0003  29JAN2018 12FEB2018   0     0    0
0004  12APR2015 15APR2015   1     1    1
0004  02JUN2018 04JUN2018   0     0    0
0004  29JAN2021 12FEB2021   0     0    0
;
run;

In other words: 

1) if Risk = 1  at dates before Index = 1 then New_risk = 1 at dates before and at Index = 1 (although Risk at Index = 1 is 0). This refers to patient 0001 and patient 0002. For this last patient at date 25NOV2020 03DEC2020, Risk = 1 but since it is = 1 after Index = 1 New_Risk should be = 0;

2) if Risk = 0 always, then no rule will be applied. All will stay the same. This refers to patient 0003

3) if Risk = 1 only at Index = 1 New_risk will be =  1 only at Index = 1 and never after. This refers to patient 0004 that has no dates before Index = 1

Can anyone help me please? 

Thank you in advance!

[Tom · Accepted solution]

Just remember if you have seen any risk or index.

data DB;
  input ID :$20. Admission :date. Discharge :date. Index Risk Expect;
  format admission discharge date9.;
cards;
0001  13JAN2017 25JAN2017   0     1    1
0001  13JAN2017 25JAN2017   0     1    1
0001  22FEB2017 03MAR2017   1     0    1
0001  30JAN2019 04MAR2019   0     0    0
0002  01DEC2018 14DEC2018   0     1    1
0002  25DEC2018 02JAN2019   1     0    1
0002  25NOV2020 03DEC2020   0     1    0
0003  09JAN2016 25JAN2016   1     0    0
0003  29JAN2018 12FEB2018   0     0    0
0004  12APR2015 15APR2015   1     1    1
0004  02JUN2018 04JUN2018   0     0    0
0004  29JAN2021 12FEB2021   0     0    0
;

data db1;
  set db ;
  by id ;
  retain any_index any_risk;
  if first.id then call missing(of any_:);
  new_risk = (risk and not any_index)
          or (index and any_risk)
  ;
  output;
  any_index=index or any_index;
  any_risk=risk or any_risk;
run;

proc print;
run; 

proc compare data=db1 ;
  var new_risk;
  with expect;
run;

Result

[Ksharp]

data DB;
  input ID :$20. Admission :date09. Discharge :date09. Index  Risk;
  format Admission Discharge date9.;
cards;
0001  13JAN2017 25JAN2017   0     1    
0001  13JAN2017 25JAN2017   0     1    
0001  22FEB2017 03MAR2017   1     0    
0001  30JAN2019 04MAR2019   0     0    
0002  01DEC2018 14DEC2018   0     1    
0002  25DEC2018 02JAN2019   1     0    
0002  25NOV2020 03DEC2020   0     1    
0003  09JAN2016 25JAN2016   1     0    
0003  29JAN2018 12FEB2018   0     1    
0004  12APR2015 15APR2015   1     1
0004  02JUN2018 04JUN2018   0     0
0004  29JAN2021 12FEB2021   0     0
;
run;

data want;
do i=1 by 1 until(last.id);
  set DB;
  by id;
  if Index then idx_Index=i;
  if Risk and not found then do;idx_Risk=i;found=1;end;
end;
do j=1 by 1 until(last.id);
  set DB;
  by id;
  New_risk =0;
  if not missing(idx_Risk) and idx_Risk<idx_Index and j<=idx_Index then New_risk=1;
  if not missing(idx_Risk) and idx_Risk=idx_Index and j=idx_Index then New_risk=1;
  output;
end;
drop idx_: i j found;
run;