Date:         Sun, 26 Jan 1997 13:09:24 -0500
Reply-To:     julierog@ix.netcom.com
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Roger L. Lustig" <julierog@IX.NETCOM.COM>
Organization: Netcom
Subject:      Re: Conditional collapsing of observations
Content-Type: text/plain; charset=us-ascii

Kamal Hijjazi wrote:
>
> Hi friends:
> I have some sort of a complicated programming problem. I have a file
> with multiple observations per case; in which each observation represent
> a Medicare claim that is submitted for payment (usually covering a
> period of a month). In For each observation, there is:
> 1. Unique id number (baseid) for the case that the claim presents.
> 2. Begin: first date of claim coverage.
> 3. End: last date of claim coverage.
> 4. Vis: number of visits for that claim. (part of file is below)
>
> I want to collapse the file into episodes of care. An episode of care is
> defined here as a service period without interruption for over (30) days
> (for the same baseid: lead(begin)-end <30 days). If 30 days lapse
> between the end date and the begin date on the line, we have a new case.
> So the collapsing is based on the variable (baseid) IF there was no
> break in service for over 30 days.
>
> For observations 1 and 2, they will be collapsed into 1 episode with
> begin = 25NOV92 , end = 03DEC92 , and vis=6.
>
> For obs=17-19, there are two episodes, the first starts on 10JUN92 and
> ends on 06JUL92 with 7 visits. The second episode is limited to the
> claim on obs=19.
>
> For obs=24, there is only one claim.
>
> Each case can have up to 12 claims.
>
> OBS     BASEID     VIS     BEGIN        END
>  1    00000185     2     25NOV92    27NOV92
>  2    00000185     4     03DEC92    03DEC92
> 17    00002901     5     10JUN92    26JUN92
> 18    00002901     2     06JUL92    06JUL92
> 19    00002901     6     14SEP92    14SEP92
> 24    00003358     6     29JUN92    30JUN92
>
> The goal is to get the file in this format:
>
> OBS     BASEID     VIS     BEGIN        END
>
>  1    00000185     2     25NOV92    03DEC92
>  2    00002901     5     10JUN92    06JUL92
>  3    00002901     6     14SEP92    14SEP92
>  4    00003358     6     29JUN92    30JUN92
>
> Your assistance is greatly appreciated.
>
> Regards
>
> Kamal Hijjazi           Simmons College
> Boston, MA 02115
> (617)521-2121           Fax (617)521-3199
 
data claims;
input   baseid $ vis @23 begin date7. @34 end date7.;
cards;
 
   00000185     2     25NOV92    27NOV92
   00000185     4     03DEC92    03DEC92
   00002901     5     10JUN92    26JUN92
   00002901     2     06JUL92    06JUL92
   00002901     6     14SEP92    14SEP92
   00003358     6     29JUN92    30JUN92
;
run;
 
data epibase;
set claims;
by baseid;
 
if first.baseid then episode = 1;
 
else if intck('day',lag(end),begin) > 30 then episode + 1;
 
run;
 
proc summary data=epibase;
by baseid episode;
var vis begin end;
output out=episodes
  sum(vis)=ep_vis
 min(begin)=ep_begin
 max(end)=ep_end
 ;
run;
proc print data=epibase;
 
format begin end date7.;
run;
proc print data=episodes;
format ep_begin ep_end date7.;
 
run;
 
 
Here's the output:
 
 
OBS     BASEID     VIS      BEGIN        END    EPISODE
 
 1     00000185     2     25NOV92    27NOV92       1
 2     00000185     4     03DEC92    03DEC92       1
 3     00002901     5     10JUN92    26JUN92       1
 4     00002901     2     06JUL92    06JUL92       1
 5     00002901     6     14SEP92    14SEP92       2
 6     00003358     6     29JUN92    30JUN92       1
 
OBS     BASEID     EPISODE    _TYPE_    _FREQ_    EP_VIS    EP_BEGIN
EP_END
 
 1     00000185       1          0         2         6      25NOV92
03DEC92
 2     00002901       1          0         2         7      10JUN92
06JUL92
 3     00002901       2          0         1         6      14SEP92
14SEP92
 4     00003358       1          0         1         6      29JUN92
30JUN92