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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.