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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: