Date:         Tue, 15 Apr 2008 09:30:48 -0500
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: Proc SQL and maybe a correlated subquery- the BEST SO FAR!!
Comments: To: "Howard Schreier <hs AT dc-sug DOT org>"
          <schreier.junk.mail@GMAIL.COM>
Content-Type: text/plain; charset="iso-8859-1"

Yes, I see that this might work- but isn't it more expensive computing-wise than a correlated sub-query? 

In an actual insurance situation the discharge table the user had would have far fewer id's in the discharge records than in the eligibility table. 

In Howard's solution it does seem that he's checking for all 30 days, though, in the do-day= loop in the data step solution.  

So the data step solution seems to be a workable one, in that it is aggregating only the records that are to be used,  whereas the SQL solution requires aggregating the entire eligibility table (unless subsetted first, which would also require a pass), which would be more expensive computing-wise.

-Mary
  ----- Original Message ----- 
  From: Howard Schreier <hs AT dc-sug DOT org> 
  To: SAS-L@LISTSERV.UGA.EDU 
  Sent: Tuesday, April 15, 2008 9:06 AM
  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!
  >>
  >>
  >>