Date: Thu, 31 Jul 2008 12:44:33 -0500
Reply-To: sas 9 bi user <sas9bi@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: sas 9 bi user <sas9bi@GMAIL.COM>
Subject: Re: Help with Array for Healthcare Claims Analysis
In-Reply-To: <200807310439.m6V0JZWc021452@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1
Paul -
Wow this is great. Thanks so much for helping!
So if I wanted to change my study to measure 1allowable gap for a total of
45 days are allowed and the length of time one would need to be eligible
after the claim is 365 days then I would change the macro vars to:
%let g_max = 45 ; *max gap width ;
%let g_num = 1 ; *max num of gs ;
%let g_tot = 45 ; *max tot gap days ;
%let d_enr = 365 ; *max days cont enr aft clm ;
Is that correct? What is the difference between %let g_max and %let g_tot?
Are they always the same?
Thanks so much!
On 7/30/08, Paul Dorfman <sashole@bellsouth.net> wrote:
>
> SAS BI User,
>
> Instead of "fixing" the clever Howard's array solution (he is a much better
> candidate), I am offering a much dummier code below. Note that I have
> changed the variables' names to suit my parsimonious nature.
>
> data clms ;
> input id clm_dt mmddyy11. ;
> format clm_dt yymmdd10. ;
> cards ;
> 111 01/27/2008
> 112 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 elig ;
> input id eff_dt mmddyy11. trm_dt mmddyy11. ;
> format eff_dt trm_dt yymmdd10. ;
> cards ;
> 111 01/01/2008 01/31/2008
> 111 02/03/2008 12/31/2008
> 112 01/01/2008 01/31/2008
> 112 02/02/2008 02/08/2008
> 112 02/10/2008 12/31/2008
> 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 view clm_elig as
> select * from (select *, monotonic() as seq from clms) c, elig e
> where c.id = e.id
> order seq, c.id, e.eff_dt
> ;
> quit ;
>
> %let g_max = 2 ; *max gap width ;
> %let g_num = 1 ; *max num of gs ;
> %let g_tot = 2 ; *max tot gap days ;
> %let d_enr = 30 ; *max days cont enr aft clm ;
>
> data new (drop = _:) ;
> do until (last.id) ;
> set clm_elig ;
> by seq id ;
> _eff_dt = eff_dt max clm_dt min (clm_dt + &d_enr) ;
> _trm_dt = trm_dt max clm_dt min (clm_dt + &d_enr) ;
> if first.id then first_adj_eff_dt = _eff_dt ;
> else _g = sum (_eff_dt, -_trm_dt_p, -1) ;
> g_tot = sum (g_tot, _g, 0) ;
> g_num = sum (g_num, _g > 0) ;
> g_max = max (g_max, _g, 0) ;
> _trm_dt_p = _trm_dt ;
> end ;
> d_enr = sum (_trm_dt, -first_adj_eff_dt, -g_tot, 1) ;
> last_adj_trm_dt = _trm_dt ;
> if d_enr => &d_enr ;
> if g_max <= &g_max ;
> if g_num <= &g_num ;
> if g_tot <= &g_tot ;
> run ;
>
> You can control execution with the macro parameters above the step. The key
> to understanding the scheme is the lines where _eff_dt and _trm_dt are
> formed: both dates earlier than clm_dt are made equal to clm_dt, and both
> dates later than clm_dt are also made equal to clm_dt.
>
> Kind regards
> ------------
> Paul Dorfman
> Jax, FL
> ------------
>
> On Wed, 30 Jul 2008 09:53:51 -0500, sas 9 bi user <sas9bi@GMAIL.COM>
> wrote:
>
> >All - A while ago Howard assisted me re the below solution. I am trying
> to
> >add a new business rule to an array that he created and am not getting
> >anywhere. I work in healthcare analytics. I want to do a study where a
> >member needs to be eligible for 30 days or less after the claim. So below
> I
> >have a claim file and a eligibility file. A while ago I provided the
> claims
> >and eligibility datasets and Howard kindly came up with the data set
> called
> >'new', as created int he array below. It is a wonderful array that
> >calculates if a member has continuous coverage for 30 days after a claim.
> >If the member has 30 days continuous coverage, then the member would be in
> >my study.
> >
> >However, I have one new requirement and I wonder how I would code this new
> >requirement? The new requirement is - the member can have a break in
> their
> >eligibility of 2 days max, but only once, and the member must be eligible
> 30
> >days total (2 days allowed gap, though only once). So they should really
> >have 28 days real eligibility coverage and the +2 gap days.
> >
> >For eg, take a look at member number 111 below. This mbr has a claim on
> >1/27/08. The member has eligibility 1/1/08 thru 1/31/08, then new
> coverage
> >2/3/08 through 12/31/08. So this member has a gap of 2 days and was also
> >eligible for 30 days (given that a gap of 2 days is allowed).
> >
> >I can't figure out how to alter the nice array below to allow for a gap of
> 2
> >days max and find members who are eligible for 30 days? I want this
> member
> >111 to be in my study since the member's gap is 2 days or less and only
> >once. I cant figure out how to account for their gap of 2 days. Now
> member
> >112, this member has 2 gaps, and I dont want this member to be in the
> final
> >dataset because they do have 30 days of coverage if you count the gap of 2
> >days or less, but this member's had 2 days of gap coverage but they
> occurred
> >on two different points in time and I can only allow one gap - else the
> >member is no in the study.
> >
> >Any thoughts? I graciously thank anyone for assistance.
> >
> >data claims;
> >input
> >mbrid date_of_claim mmddyy11.;
> >format date_of_claim date9.;
> >datalines
> >;
> >111 01/27/2008
> >112 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/03/2008 12/31/2008
> >112 01/01/2008 01/31/2008
> >112 02/02/2008 02/08/2008
> >112 02/10/2008 12/31/2008
> >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;
> >
> >data new(keep = mbrid date_of_claim);
> > array ee(60000);
> > do until (last.mbrid);
> > set eligibility(in=in_eligibility)
> > claims (in=in_claims);
> > by mbrid;
> > if in_eligibility then do day = effective to term; ee(day) = 1; end;
> > if in_claims then do;
> > do day = date_of_claim to date_of_claim + 30;
> > if missing( ee(day) ) then not30 = 1;
> > end;
> > if not30 then continue;
> > output;
> > end;
> > end;
> > run;
> >
> >
> >
> >/*end*/
> >
> >
> >
> >
> >
> >
> >
> >
> >On 7/25/08, sas 9 bi user <sas9bi@gmail.com> wrote:
> >>
> >> /*
> >> Howard, a while ago you kindly helped me out re the below. To recap:
> >>
> >> I work in healthcare analytics. I want to do a study where a member
> needs
> >> to be eligible for 30 days or less after the claim. So below I have a
> claim
> >> file and a eligibility file. A while ago I provided the claims and
> >> eligibility datasets and you kindly came up with the data set called
> 'new',
> >> as created int he array below. It is a wonderful array that calculates
> if a
> >> member has continuous coverage for 30 days after a claim. If the member
> has
> >> 30 days continuous coverage, then the member would be in my study. And
> your
> >> array worked greatly.
> >>
> >> However, I have one new requirement and I wonder how I would code this
> new
> >> requirement? The new requirement is - the member can have a break in
> their
> >> eligibility of 2 days max, but only once, and the member must be
> eligible 30
> >> days total (2 days allowed gap, though only once). So they should
> really
> >> have 28 days real eligibility coverage and the +2 gap days.
> >>
> >> For eg, take a look at member number 111 below. This mbr has a claim on
> >> 1/27/08. The member has eligibility 1/1/08 thru 1/31/08, then new
> coverage
> >> 2/3/08 through 12/31/08. So this member has a gap of 2 days and was
> also
> >> eligible for 30 days (given that a gap of 2 days is allowed).
> >>
> >> I can't figure out how to alter your nice array to allow for a gap of 2
> >> days max and find members who are eligible for 30 days? I want this
> member
> >> 111 to be in my study since the member's gap is 2 days or less and only
> >> once. I cant figure out how to account for their gap of 2 days. Now
> member
> >> 112, this member has 2 gaps, and I dont want this member to be in the
> final
> >> dataset because they do have 30 days of coverage if you count the gap of
> 2
> >> days or less, but this member's had 2 days of gap coverage but they
> occurred
> >> on two different points in time and I can only allow one gap - else the
> >> member is no in the study.
> >>
> >> Any thoughts? I graciously thank you for looking at this!
> >>
> >> */
> >>
> >> data claims;
> >> input
> >> mbrid date_of_claim mmddyy11.;
> >> format date_of_claim date9.;
> >> datalines
> >> ;
> >> 111 01/27/2008
> >> 112 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/03/2008 12/31/2008
> >> 112 01/01/2008 01/31/2008
> >> 112 02/02/2008 02/08/2008
> >> 112 02/10/2008 12/31/2008
> >> 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;
> >>
> >> data new(keep = mbrid date_of_claim);
> >> array ee(60000);
> >> do until (last.mbrid);
> >> set eligibility(in=in_eligibility)
> >> claims (in=in_claims);
> >> by mbrid;
> >> if in_eligibility then do day = effective to term; ee(day) = 1;
> end;
> >> if in_claims then do;
> >> do day = date_of_claim to date_of_claim + 30;
> >> if missing( ee(day) ) then not30 = 1;
> >> end;
> >> if not30 then continue;
> >> output;
> >> end;
> >> end;
> >> run;
> >>
> >>
> >>
> >> /*end*/
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> On 4/15/08, Howard Schreier <hs AT dc-sug DOT org> <
> >> schreier.junk.mail@gmail.com> wrote:
> >>>
> >>> 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!
> >>> >>
> >>> >>
> >>> >>
> >>>
> >>
> >>
>
| 
