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