DATA Step, Macro, Functions and more

Proc sql help

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Proc sql help

Hi SAS community...

Long time lurker but new user.

 

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

  1. 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...
  2. 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. 
  3. I created a separate variable 'readmission_claim=1' if the claim was readmission. The other claims are all 0. 
  4. 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;

Accepted Solutions
Solution
‎03-07-2018 03:20 AM
Super User
Posts: 9,932

Re: Proc sql help

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     
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,932

Re: Proc sql help

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 254

Re: Proc sql help

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.

New Contributor
Posts: 3

Re: Proc sql help

Thank you for noticing the error. This fixed it. 

Solution
‎03-07-2018 03:20 AM
Super User
Posts: 9,932

Re: Proc sql help

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     
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Proc sql help

Posted in reply to KurtBremser
That worked! Thanks for helping a SAS noob
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 110 views
  • 1 like
  • 3 in conversation