topic Re: How to select groups by condition occurring within a date range? in SAS Programming

How to select groups by condition occurring within a date range?

martyvd — Mon, 15 Mar 2021 18:25:52 GMT

Hello, I have data structured like this:

ID	test_date	test_result
1	27Mar1992	N
1	08Dec1999	P
1	29Jan2005	N
2	13Jan2015	N
2	09Mar2017	P
2	05Jun2018	P
3	15Oct1996	N
3	05Sep1997	N
3	28Jun1998	N

I need to keep all records for each ID if they had a test_result=P that occurred within 2017-2018. For this example only records from ID 2 would be kept.

Thank you!

Re: How to select groups by condition occurring within a date range?

PeterClemmensen — Mon, 15 Mar 2021 18:30:51 GMT

Try this

data have;
input ID test_date :date9. test_resultb $;
format test_date date9.;
datalines;
1 27Mar1992 N
1 08Dec1999 P
1 29Jan2005 N
2 13Jan2015 N
2 09Mar2017 P
2 05Jun2018 P
3 15Oct1996 N
3 05Sep1997 N
3 28Jun1998 N
;

data want;
   set have;
   where year(test_date) in (2017:2018);
run;

Re: How to select groups by condition occurring within a date range?

martyvd — Mon, 15 Mar 2021 18:36:23 GMT

Peter, maybe I should have clarified. I need to subset records for only IDs that had a positive test result (test_result=P) in 2017-2018. If an ID had a positive test in this time period, I need to keep all other records for that ID as well.

Re: How to select groups by condition occurring within a date range?

Tom — Mon, 15 Mar 2021 18:38:14 GMT

I need to keep all records for each ID if they had a test_result=P that occurred within 2017-2018. For this example only records from ID 2 would be kept.

proc sql;
create table want as 
select * from have
where ID in (select id from have where test_result='P' and year(date) in (2017 2018) )
;
quit;

Re: How to select groups by condition occurring within a date range?

martyvd — Mon, 15 Mar 2021 19:10:48 GMT

This code does not give me any errors in the log but it produces a dataset with no rows. Not sure why.

Re: How to select groups by condition occurring within a date range?

PeterClemmensen — Mon, 15 Mar 2021 19:14:07 GMT

Ah ok. There you go.

data have;
input ID test_date :date9. test_resultb $;
format test_date date9.;
datalines;
1 27Mar1992 N
1 08Dec1999 P
1 29Jan2005 N
2 13Jan2015 N
2 09Mar2017 P
2 05Jun2018 P
3 15Oct1996 N
3 05Sep1997 N
3 28Jun1998 N
;

data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : "have(where=(year(test_date) in (2017:2018)))");
      h.definekey("ID");
      h.definedone();
   end;

   set have;
   
   if h.check() = 0;
run;

Re: How to select groups by condition occurring within a date range?

ballardw — Mon, 15 Mar 2021 19:38:19 GMT

@martyvd wrote:
This code does not give me any errors in the log but it produces a dataset with no rows. Not sure why.

Did you make sure to change the data set names to match yours, and the date variable name?

When I run:

data have;
input ID	test_date :date9.	test_result $;
format test_date date9.;
datalines;
1	27Mar1992	N
1	08Dec1999	P
1	29Jan2005	N
2	13Jan2015	N
2	09Mar2017	P
2	05Jun2018	P
3	15Oct1996	N
3	05Sep1997	N
3	28Jun1998	N
;

proc sql;
create table want as 
select * from have
where ID in (select id from have where test_result='P' and year(test_date) in (2017 2018) )
;
quit;

I get the 3 records with ID=2.

Post your log from running the code.

Re: How to select groups by condition occurring within a date range?

novinosrin — Mon, 15 Mar 2021 19:50:07 GMT

Hi @martyvd


data have;
input ID test_date :date9. test_resultb $;
format test_date date9.;
datalines;
1 27Mar1992 N
1 08Dec1999 P
1 29Jan2005 N
2 13Jan2015 N
2 09Mar2017 P
2 05Jun2018 P
3 15Oct1996 N
3 05Sep1997 N
3 28Jun1998 N
;

proc sql;
 create table want as
 select *
 from have 
 group by id
 having sum((test_resultb='P') and year(test_date) in (2017,2018));
quit;

Re: How to select groups by condition occurring within a date range?

martyvd — Tue, 16 Mar 2021 14:04:16 GMT

There was a problem with my data that has been resolved. Now the code seems to be working, thanks.