Hi SAS community...
Long time lurker but new user.
So, I have a question that I am stumped with.
Here is code I have but it's not removing dates that have 0 claim.
proc sql;
create table output as select * from input t
where exists (select * from input t where t.person=t.person and t.date=t.date and t.readmission_claim=1);
quit;
So I made up some quick-and-dirty test data, and ran my SQL against it. See the result.
data input;
input person date :yymmdd8. readmission_claim;
format date yymmddd10.;
cards;
1 20180305 0
1 20180305 1
1 20180306 0
1 20180306 0
2 20180305 0
2 20180305 0
2 20180306 0
2 20180306 0
;
run;
proc sql;
create table output as
select *
from input t
group by t.person, t.date
having max(t.readmission_claim) = 1
;
quit;
proc print data=output noobs;
run;
readmission_ person date claim 1 2018-03-05 1 1 2018-03-05 0
Please supply some example data in a data step so we can test code against it.
Try a having clause:
proc sql;
create table output as
select *
from input t
group by t.person, t.date
having max(t.readmission_claim) = 1
;
quit;
The problem seems to be that you are using the same alias inside and outside your EXISTS subquery. Try removing the t alias from the inner query table:
proc sql;
create table output as select * from input t
where exists (select * from input where person=t.person and date=t.date and readmission_claim=1);
quit;
In your original query, all rows will be included if there is a single row in the whole table having readmission_claim=1, otherwise no rows will be selected.
Thank you for noticing the error. This fixed it.
So I made up some quick-and-dirty test data, and ran my SQL against it. See the result.
data input;
input person date :yymmdd8. readmission_claim;
format date yymmddd10.;
cards;
1 20180305 0
1 20180305 1
1 20180306 0
1 20180306 0
2 20180305 0
2 20180305 0
2 20180306 0
2 20180306 0
;
run;
proc sql;
create table output as
select *
from input t
group by t.person, t.date
having max(t.readmission_claim) = 1
;
quit;
proc print data=output noobs;
run;
readmission_ person date claim 1 2018-03-05 1 1 2018-03-05 0
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.