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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.