Here is another way to do it. Not sure if it is the most efficient.

data have ; length id from to 8; informat from to mmddyy10.; format from to date9.; input id from to ; cards; 1 1/1/2007 1/20/2007 2 1/1/2008 3/1/2008 2 6/30/2008 7/2/2008 2 7/3/2008 7/5/2008 3 9/9/2009 9/14/2009 3 9/10/2009 9/16/2009 run;

data want (keep=id episode nrecs start end los) detail ; set have ; by id from ; retain episode nrecs start end; lagto=lag(to); if first.id then do ; episode=1; nrecs=0; lagto=.; diff=.; start=from; end=to; end; else do; diff = from - lagto; end; nrecs+1; los = end - start + 1; if diff > 1 then do; output want; output detail; episode+1; nrecs=0; start=from; end=to; end; else do; end=max(end,to); los = end - start + 1; output detail; if last.id then output want; end;

format start end lagto date9.; run;

Obs id from to episode nrecs start end lagto diff los

1 1 01JAN2007 20JAN2007 1 1 01JAN2007 20JAN2007 . . 20 2 2 01JAN2008 01MAR2008 1 1 01JAN2008 01MAR2008 . . 61 3 2 30JUN2008 02JUL2008 1 2 01JAN2008 01MAR2008 01MAR2008 121 61 4 2 03JUL2008 05JUL2008 2 1 30JUN2008 05JUL2008 02JUL2008 1 6 5 3 09SEP2009 14SEP2009 1 1 09SEP2009 14SEP2009 . . 6 6 3 10SEP2009 16SEP2009 1 2 09SEP2009 16SEP2009 14SEP2009 -4

On Fri, 18 Nov 2011 12:32:29 -0500, Simon, Lorna <Lorna.Simon@UMASSMED.EDU> wrote:

>I am defining an episode as claims which have no more than 1 day between them, i.e. if from_date2-to_date1 is no more than 1 day. Does that make sense? > >I'm working on a solution now that combines your sql code with my original code. I'll keep you posted on how that works. Thanks for all your help. I really appreciate it. > >From: toby dunn [mailto:tobydunn@hotmail.com] >Sent: Friday, November 18, 2011 12:28 PM >To: Simon, Lorna; sas-l@listserv.uga.edu >Subject: RE: aggregating claims into inpatient treatment episodes > >Lorna, > >I mentioned that is what it did in the post. The reason is you have failed to give the rules by which you want to define as a Episode. > >For instance here on one project we define an episode as admissions that are within ten days from the previous discharge date. > > >So inorder to get what you need you need ot define how you are defining a episode. > >Toby Dunn > > >If you get thrown from a horse, you have to get up and get back on, unless you landed on a cactus; then you have to roll around and scream in pain. > >"Any idiot can face a crisis-it's day to day living that wears you out" >~ Anton Chekhov > > >________________________________ >From: Lorna.Simon@umassmed.edu >To: tobydunn@hotmail.com; SAS-L@LISTSERV.UGA.EDU >Date: Fri, 18 Nov 2011 12:22:04 -0500 >Subject: RE: aggregating claims into inpatient treatment episodes >Unfortunately, Toby your elegant solution does not do what I want it to. One person can have many hospitalizations, so I can't just take the min and max of the dates - that only gives me one observation per person, with their 1st admission date and their last discharge date. Any other ideas anyone? > >From: toby dunn [mailto:tobydunn@hotmail.com] >Sent: Friday, November 18, 2011 10:09 AM >To: Simon, Lorna; sas-l@listserv.uga.edu >Subject: RE: aggregating claims into inpatient treatment episodes > >Since I didnt see anything about the number of days between date ranges what it looks like you want is simply the min and max dates. > >Proc SQL ; >Create Table Need As > Select Distinct Project_ID , Min( From_Date ) As Adm_Date , Max( To_Date ) As Dis_Date > From Have > Group By Project_Id ; >Quit ; > > > >Toby Dunn > > >If you get thrown from a horse, you have to get up and get back on, unless you landed on a cactus; then you have to roll around and scream in pain. > >"Any idiot can face a crisis-it's day to day living that wears you out" >~ Anton Chekhov > > >> Date: Fri, 18 Nov 2011 10:03:24 -0500 >> From: Lorna.Simon@UMASSMED.EDU >> Subject: aggregating claims into inpatient treatment episodes >> To: SAS-L@LISTSERV.UGA.EDU >> >> I have a claims dataset that I need to condense into 1 treatment episode - there can be many claims for each treatment episode (1 hospitalization). I found code in the archives which does this for most of the claims. However, for some hospitalizations, the claims are not consecutive - some of the hospitalizations may have claims which overlap. >> >> Here are the data I have: >> Obsnum project_id from_date to_date >> 1 1 1/1/2007 1/20/2007 >> 2 2 6/30/2008 7/2/2008 >> 3 2 7/3/2008 7/5/2008 >> 4 3 9/9/2009 9/14/2009 >> 5 3 9/10/2009 9/16/2009 >> >> Data I want: >> project_id adm_date dis_date >> 1 1/1/2007 1/20/2007 >> 2 6/30/2008 7/5/2008 >> 3 9/9/2009 9/16/2009 >> >> Here is my code: >> >> proc sort data=mhub92inpatientclaims; by project_id from_date to_date; run; >> data claims; >> set MHUB92inpatientclaims;; >> by project_id from_date; >> lagto = lag(to_date); >> lagfrom=lag(from_date); >> if first.project_id then do; >> lagto = .; >> lagfrom=.; >> stay = 1; >> end; >> if from_date-lagto ge 2 then stay + 1; >> lagstay=lag(stay); >> if from_date ge lagfrom and to_date le lagto then stay=lagstay; >> format lagto lagfrom from_date to_date mmddyy10.; >> run; >> data dig.mhub92inpdischargeclaims11_18_11; >> do until(last.stay); >> set claims; >> by project_id stay; >> if first.stay then adm_date = from_date; >> end; >> >> the code works for project_ids 1 and 2, but not for project_id 3. I am still getting 2 lines for project_id, one with a treatment episode from 9/9/2009-9/9/14 and one with a treatment episode from 9/10/2009-9/16/2009 . the highlighted code is what I added to the code I found on the web in the hope of solving this problem, but obviously it is not working. >> >> Any suggestions would be appreciated. Thanks.