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

[martyvd]

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! 

[PeterClemmensen]

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;

[martyvd]

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.

[PeterClemmensen]

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;

[Tom]

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;

[martyvd]

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

[ballardw · Accepted solution]

---

@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.

[martyvd]

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

[novinosrin]

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;