topic Re: Proc sql help in SAS Programming

Proc sql help

A_Swoosh — Wed, 07 Mar 2018 06:54:53 GMT

Hi SAS community...

Long time lurker but new user.

So, I have a question that I am stumped with.

I have 1 data set with all my data. There are multiple rows with each user and each user may have multiple dates. For example, there can be multiple rows for the same person on the same date and then additional rows for more dates and so on...
Now, I want to delete dates where the claim is 0 and keep those dates where the claim is 1. So, all claims on that date where the claim exists will be kept and all dates where claim is 0 will be removed. For example, the claim is readmission. So, for example, for one date, the person may have 5 claims but one claim=1 so I want to keep all those claims on that date.
I created a separate variable 'readmission_claim=1' if the claim was readmission. The other claims are all 0.
t is my data set

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;

Re: Proc sql help

Kurt_Bremser — Wed, 07 Mar 2018 07:01:59 GMT

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;

Re: Proc sql help

s_lassen — Wed, 07 Mar 2018 07:21:39 GMT

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.

Re: Proc sql help

Kurt_Bremser — Wed, 07 Mar 2018 07:25:42 GMT

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

Re: Proc sql help

A_Swoosh — Wed, 07 Mar 2018 08:21:02 GMT

That worked! Thanks for helping a SAS noob

Re: Proc sql help

A_Swoosh — Wed, 07 Mar 2018 08:21:39 GMT

Thank you for noticing the error. This fixed it.