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