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