Date: Tue, 15 Apr 2008 10:06:24 -0400
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>"
<schreier.junk.mail@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>"
<schreier.junk.mail@GMAIL.COM>
Subject: Re: Proc SQL and maybe a correlated subquery- the BEST SO FAR!!
On Mon, 14 Apr 2008 15:01:34 -0500, sas 9 bi user <sas9bi@GMAIL.COM> wrote:
>Mary - Wow thanks so much for this. I have enjoyed seeing your revisions.
>Being a SAS newbie, being able to see how you took something complex and
>then kept revising it till the current solution was nice. I learned more by
>seeing your re-work. Thanks!
However, Cherish is correct in pointing out that the real problem is to
consolidate the eligibility data so that that there is one observation for
each uninterrupted interval of coverage. The data set (call it
"eligibility_consolidated") looks like this:
111 01/01/2008 12/31/2099
123 01/01/2008 01/31/2008
123 04/01/2008 12/31/2099
456 01/01/2008 03/31/2008
789 01/01/2008 02/29/2008
888 03/01/2007 01/31/2008
888 03/01/2008 12/31/2008
999 01/01/2008 01/31/2008
See
http://www.sascommunity.org/wiki/Processing_Data_with_Beginning_and_Ending_Dates
Then the solution is:
create table new as select *
from claims as outer
where exists ( select 1
from eligibility_consolidated
where mbrid = outer.mbrid
and effective LE discharge
and term GE discharge+30);
There is also a DATA step solution which requires no pre-processing:
data new(keep = mbrid discharge);
array ee(60000);
do until (last.mbrid);
set eligibility(in=ine)
claims (in=inc);
by mbrid;
if ine then do day = effective to term; ee(day) = 1; end;
if inc then do;
do day = discharge to discharge + 30;
if missing( ee(day) ) then not30 = 1;
end;
if not30 then continue;
output;
end;
end;
run;
The array covers dates from 1960 into the 22nd century. For each ID, flags
are set for each day of eligibility. Then, for each discharge date, the
flags are checked.
>
>On 4/14/08, Mary <mlhoward@avalon.net> wrote:
>>
>> And here, (drum-roll please, I'm feeling a bit of Hubris in this!!!) is
>> the one pass solution
>>
>> -Mary
>>
>> *
>>
>> data
>> *claims;
>>
>> input
>> mbrid discharge mmddyy11.;
>>
>> format
>> discharge date9.;
>>
>> datalines
>> ;
>>
>> 111 01/27/2008
>>
>> 123 01/05/2008
>>
>> 456 01/03/2008
>>
>> 789 01/06/2008
>>
>> 789 01/31/2008
>>
>> 888 01/04/2008
>>
>> 999 01/20/2008
>>
>> ;
>> *
>>
>> run
>> *;*
>>
>> data
>> *eligibility;
>>
>> input
>> mbrid effective mmddyy11. term mmddyy11.;
>>
>> format
>> effective term date9.;
>>
>> datalines
>> ;
>>
>> 111 01/01/2008 01/31/2008
>>
>> 111 02/01/2008 02/29/2008
>>
>> 111 03/01/2008 12/31/2008
>>
>> 111 01/01/2008 12/31/2099
>>
>> 123 01/01/2008 01/31/2008
>>
>> 123 04/01/2008 12/31/2099
>>
>> 456 01/01/2008 02/29/2008
>>
>> 456 03/01/2008 03/31/2008
>>
>> 789 01/01/2008 01/31/2008
>>
>> 789 02/01/2008 02/29/2008
>>
>> 888 03/01/2007 01/31/2008
>>
>> 888 03/01/2008 12/31/2008
>>
>> 999 01/01/2008 01/31/2008
>>
>> ;
>> *
>>
>> run
>> *;
>>
>>
>> *
>>
>> proc
>> **sql*;
>>
>> create
>> table new as
>>
>> select
>> mbrid, discharge
>>
>> from
>> claims a
>>
>> where
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *30
>> *) and (b.term >= a.discharge + *30*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *29
>> *) and (b.term >= a.discharge + *29*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *28
>> *) and (b.term >= a.discharge + *28*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *27
>> *) and (b.term >= a.discharge + *27*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *26
>> *) and (b.term >= a.discharge + *26*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *25
>> *) and (b.term >= a.discharge + *25*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *24
>> *) and (b.term >= a.discharge + *24*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *23
>> *) and (b.term >= a.discharge + *23*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *22
>> *) and (b.term >= a.discharge + *22*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *21
>> *) and (b.term >= a.discharge + *21*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *20
>> *) and (b.term >= a.discharge + *20*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *19
>> *) and (b.term >= a.discharge + *19*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *18
>> *) and (b.term >= a.discharge + *18*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *17
>> *) and (b.term >= a.discharge + *17*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *16
>> *) and (b.term >= a.discharge + *16*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *15
>> *) and (b.term >= a.discharge + *15*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *14
>> *) and (b.term >= a.discharge + *14*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *13
>> *) and (b.term >= a.discharge + *13*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *12
>> *) and (b.term >= a.discharge + *12*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *11
>> *) and (b.term >= a.discharge + *11*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *10
>> *) and (b.term >= a.discharge + *10*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *09
>> *) and (b.term >= a.discharge + *09*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *08
>> *) and (b.term >= a.discharge + *08*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *07
>> *) and (b.term >= a.discharge + *07*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *06
>> *) and (b.term >= a.discharge + *06*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *05
>> *) and (b.term >= a.discharge + *05*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *04
>> *) and (b.term >= a.discharge + *04*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *03
>> *) and (b.term >= a.discharge + *03*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *02
>> *) and (b.term >= a.discharge + *02*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *01
>> *) and (b.term >= a.discharge + *01*)) and
>>
>> mbrid
>> in (select mbrid from eligibility b where(b.effective <= a.discharge + *00
>> *) and (b.term >= a.discharge + *00*));*
>>
>> quit
>> *;*
>>
>> run
>> *;
>>
>>
>>
>> ----- Original Message -----
>> *From:* sas 9 bi user <sas9bi@GMAIL.COM>
>> *To:* SAS-L@LISTSERV.UGA.EDU
>> *Sent:* Monday, April 14, 2008 12:09 PM
>> *Subject:* Re: Proc SQL and maybe a correlated subquery
>>
>>
>> Mary you are so kind to have worked thru this. I like your logic and it
>> does the trick.
>>
>> Thanks so much!
>>
>>
>>
