Dataset containing only rows where DATE_DOSE2 is after DATE_DOSE1 by ID

[ANKH1]

I have this table. We need to make sure to keep only dates for variable DATE_DOSE2 that occur after DATE_DOSE1 by ID. This is a sample table. The resulting dataset should delete row 4 for ID=A since DATE_DOSE2 happens before DATE_DOSE1. What code do you recommend?

data dsin;
input ID $ SESSION DATE_DOSE2 : mmddyy10. DATE_DOSE1 : mmddyy10.;
format DATE_DOSE2 DATE_DOSE1 mmddyy10.;
datalines;
A 2 02/03/2021 .
A 2 02/04/2021 .
A 2 02/05/2021 .
A 2 01/06/2020 .
A 1 . 02/20/2020
B 2 04/16/2021 .
B 2 04/17/2021 .
B 2 04/18/2021 .
B 1 . 02/21/2020
;
run;

[mkeintz · Accepted solution]

If your data are sorted by ID, and you only have one non-missing DATE_DOSE1 value per ID, then a simple merge of the non-missing DATE_DOSE2 subset with the non-missing DATE_DOSE1 subset will allow you to use a subsetting IF statement:

data dsin;
  input ID $ SESSION DATE_DOSE2 : mmddyy10. DATE_DOSE1 : mmddyy10.;
  format DATE_DOSE2 DATE_DOSE1 mmddyy10.;
datalines;
A 2 02/03/2021 .
A 2 02/04/2021 .
A 2 02/05/2021 .
A 2 01/06/2020 .
A 1 .          02/20/2020
B 2 04/16/2021 .
B 2 04/17/2021 .
B 2 04/18/2021 .
B 1 .          02/21/2020
run;
data want;
  merge dsin (where=(date_dose2^=.) drop=date_dose1)
        dsin (where=(date_dose1^=.) keep=id date_dose1);
  by id;
  if date_dose2>date_dose1;
run;

[ANKH1]

Thanks!

[Ksharp]

data dsin;
  input ID $ SESSION DATE_DOSE2 : mmddyy10. DATE_DOSE1 : mmddyy10.;
  format DATE_DOSE2 DATE_DOSE1 mmddyy10.;
datalines;
A 2 02/03/2021 .
A 2 02/04/2021 .
A 2 02/05/2021 .
A 2 01/06/2020 .
A 1 .          02/20/2020
B 2 04/16/2021 .
B 2 04/17/2021 .
B 2 04/18/2021 .
B 1 .          02/21/2020
;

data want;
do until(last.id);
 set dsin;
 by id;
end;
_DATE_DOSE1=DATE_DOSE1;
do until(last.id);
 set dsin;
 by id;
 if  SESSION=1 or DATE_DOSE2>_DATE_DOSE1 then output;
end;
drop _DATE_DOSE1;
run;