Hi Members,
I need to build episodes for the following situations.
1. Same patient (id) has bills start at the same service center (service id) on March 31th and then end on May 11th, there are multiple costs happen, this considers as one episode. (see sample data below)
ID | Service ID | Start_DT | End_DT | Amount | ESP |
123 | 300 | 3/31/2011 | 5/11/2011 | 1382.07 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33153.85 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33153.85 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 | 1 |
2. Same patient (id) has bills start at the same service center (service id) on Oct. 16th and end on Nov. 3rd, and then bills start again on Nov. 4th, end on Nov. 26th. This also considers as one episode. (see sample data below)
ID | Service ID | Start_DT | End_DT | Amount | ESP |
456 | 700 | 10/16/2010 | 11/3/2010 | 60050.79 | 11 |
456 | 700 | 10/16/2010 | 11/3/2010 | 10226.93 | 11 |
456 | 700 | 11/4/2010 | 11/26/2010 | 2356.21 | 11 |
3. Same patient (id) has bill starts at the same service center (service id) on Oct. 8th and ends on Oct. 15; however during this period of the time, there is another bill starts on Oct. 12th and ends on Oct. 20th. This considers as one episode. (see sample data below)
ID | Service ID | Start_DT | End_DT | Amount | ESP |
456 | 600 | 10/8/2010 | 10/15/2010 | 15228 | 10 |
456 | 600 | 10/12/2010 | 10/20/2010 | 5869.3 | 10 |
4. Same patient (id) has bill starts at the same service center (service id) on June 11 and ends on June 14th, also this person has another bill that happens on June 30th and ends on July 2rd. This considers as two separate episodes. (see sample data below)
ID | Service ID | Start_DT | End_DT | Amount | ESP |
456 | 500 | 6/11/2010 | 6/14/2010 | 7481.56 | 4 |
456 | 500 | 6/30/2010 | 7/2/2010 | 8868.46 | 5 |
5. Same patient (id) has bill starts at the ONE service center (service id) on Dec. 1st ends on Dec. 3rd, and then bill starts again on a DIFFERENT service center (service id) on Dec. 14th and then ends on Dec. 10th. This is consider as two separate episodes. (see sample data below)
ID | Service ID | Start_DT | End_DT | Amount | ESP |
456 | 723 | 12/1/2010 | 12/3/2010 | 2576.42 | 12 |
456 | 760 | 12/4/2010 | 12/10/2010 | 6589.5 | 13 |
In ESP column, I manually filled them out, is there anyway that SAS can do this work for us.
Sorry it does not allow me to attach file, I have to post the sample data here.
ID | Service ID | Start_DT | End_DT | Amount |
123 | 300 | 3/31/2011 | 5/11/2011 | 1382.07 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33153.85 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33153.85 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 |
123 | 300 | 3/31/2011 | 5/11/2011 | 25141.5 |
123 | 300 | 3/31/2011 | 5/11/2011 | 27960.46 |
123 | 300 | 3/31/2011 | 5/11/2011 | 27960.46 |
123 | 300 | 3/31/2011 | 5/11/2011 | 27960.46 |
123 | 300 | 3/31/2011 | 5/11/2011 | 27960.46 |
123 | 300 | 3/31/2011 | 5/11/2011 | 26823.95 |
123 | 300 | 3/31/2011 | 5/11/2011 | 21022.18 |
123 | 300 | 3/31/2011 | 5/11/2011 | 23477.92 |
123 | 300 | 3/31/2011 | 5/11/2011 | 32490.53 |
123 | 300 | 3/31/2011 | 5/11/2011 | 32490.35 |
123 | 300 | 3/31/2011 | 5/11/2011 | 32490.35 |
123 | 300 | 3/31/2011 | 5/11/2011 | 32490.35 |
123 | 300 | 3/31/2011 | 5/11/2011 | 30526.24 |
123 | 300 | 3/31/2011 | 5/11/2011 | 17711.57 |
123 | 300 | 3/31/2011 | 5/11/2011 | 23780.86 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29814.05 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29814.05 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29814.05 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29814.05 |
123 | 300 | 3/31/2011 | 5/11/2011 | 11338.58 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33979.3 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33978.95 |
123 | 300 | 3/31/2011 | 5/11/2011 | 36246.81 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33326.88 |
123 | 300 | 3/31/2011 | 5/11/2011 | 26893.07 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35127.65 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35127.65 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29006.91 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29006.91 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29006.91 |
123 | 300 | 3/31/2011 | 5/11/2011 | 30810.77 |
456 | 400 | 1/24/2010 | 1/27/2010 | 8333.5 |
456 | 400 | 4/8/2010 | 4/9/2010 | 15465.76 |
456 | 500 | 6/11/2010 | 6/14/2010 | 7481.56 |
456 | 500 | 6/30/2010 | 7/2/2010 | 8868.46 |
456 | 500 | 8/18/2010 | 8/22/2010 | 14159.72 |
456 | 500 | 9/1/2010 | 9/2/2010 | 6713.97 |
456 | 500 | 9/18/2010 | 9/20/2010 | 7651.7 |
456 | 500 | 9/29/2010 | 10/8/2010 | 67422.93 |
456 | 500 | 9/29/2010 | 10/8/2010 | 60481.26 |
456 | 500 | 9/29/2010 | 10/8/2010 | 44218.5 |
456 | 500 | 9/29/2010 | 10/8/2010 | 56635.25 |
456 | 500 | 9/29/2010 | 10/8/2010 | 50804.47 |
456 | 500 | 9/29/2010 | 10/8/2010 | 37143.54 |
456 | 600 | 10/8/2010 | 10/15/2010 | 15228 |
456 | 600 | 10/12/2010 | 10/20/2010 | 5869.3 |
456 | 700 | 10/16/2010 | 11/3/2010 | 60050.79 |
456 | 700 | 10/16/2010 | 11/3/2010 | 10226.93 |
456 | 700 | 11/4/2010 | 11/26/2010 | 2356.21 |
456 | 723 | 12/1/2010 | 12/3/2010 | 2576.42 |
456 | 760 | 12/4/2010 | 12/10/2010 | 6589.5 |
786 | 800 | 1/29/2011 | 2/21/2011 | 45181.5 |
786 | 800 | 1/29/2011 | 2/21/2011 | 45281.5 |
786 | 800 | 1/29/2011 | 2/21/2011 | 31140.23 |
786 | 800 | 1/29/2011 | 2/21/2011 | 10927.44 |
786 | 800 | 1/29/2011 | 2/21/2011 | 40143.2 |
786 | 800 | 12/13/2011 | 12/23/2011 | 18061.98 |
786 | 800 | 12/13/2011 | 12/23/2011 | 44607.41 |
786 | 800 | 12/13/2011 | 12/23/2011 | 5771.82 |
786 | 800 | 12/28/2011 | 12/31/2011 | 59186.25 |
786 | 800 | 12/28/2011 | 12/31/2011 | 1031.2 |
786 | 800 | 1/1/2012 | 1/5/2012 | 26177.01 |
The result that we want to get is
ID | Service ID | Start_DT | End_DT | Amount | ESP |
123 | 300 | 3/31/2011 | 5/11/2011 | 1382.07 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33153.85 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33153.85 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35992.12 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 25141.5 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 27960.46 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 27960.46 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 27960.46 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 27960.46 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 26823.95 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 21022.18 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 23477.92 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 32490.53 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 32490.35 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 32490.35 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 32490.35 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 30526.24 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 17711.57 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 23780.86 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29814.05 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29814.05 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29814.05 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29814.05 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 11338.58 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33979.3 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33978.95 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 36246.81 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 33326.88 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 26893.07 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35127.65 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 35127.65 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29006.91 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29006.91 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 29006.91 | 1 |
123 | 300 | 3/31/2011 | 5/11/2011 | 30810.77 | 1 |
456 | 400 | 1/24/2010 | 1/27/2010 | 8333.5 | 2 |
456 | 400 | 4/8/2010 | 4/9/2010 | 15465.76 | 3 |
456 | 500 | 6/11/2010 | 6/14/2010 | 7481.56 | 4 |
456 | 500 | 6/30/2010 | 7/2/2010 | 8868.46 | 5 |
456 | 500 | 8/18/2010 | 8/22/2010 | 14159.72 | 6 |
456 | 500 | 9/1/2010 | 9/2/2010 | 6713.97 | 7 |
456 | 500 | 9/18/2010 | 9/20/2010 | 7651.7 | 8 |
456 | 500 | 9/29/2010 | 10/8/2010 | 67422.93 | 9 |
456 | 500 | 9/29/2010 | 10/8/2010 | 60481.26 | 9 |
456 | 500 | 9/29/2010 | 10/8/2010 | 44218.5 | 9 |
456 | 500 | 9/29/2010 | 10/8/2010 | 56635.25 | 9 |
456 | 500 | 9/29/2010 | 10/8/2010 | 50804.47 | 9 |
456 | 500 | 9/29/2010 | 10/8/2010 | 37143.54 | 9 |
456 | 600 | 10/8/2010 | 10/15/2010 | 15228 | 10 |
456 | 600 | 10/12/2010 | 10/20/2010 | 5869.3 | 10 |
456 | 700 | 10/16/2010 | 11/3/2010 | 60050.79 | 11 |
456 | 700 | 10/16/2010 | 11/3/2010 | 10226.93 | 11 |
456 | 700 | 11/4/2010 | 11/26/2010 | 2356.21 | 11 |
456 | 723 | 12/1/2010 | 12/3/2010 | 2576.42 | 12 |
456 | 760 | 12/4/2010 | 12/10/2010 | 6589.5 | 13 |
786 | 800 | 1/29/2011 | 2/21/2011 | 45181.5 | 14 |
786 | 800 | 1/29/2011 | 2/21/2011 | 45281.5 | 14 |
786 | 800 | 1/29/2011 | 2/21/2011 | 31140.23 | 14 |
786 | 800 | 1/29/2011 | 2/21/2011 | 10927.44 | 14 |
786 | 800 | 1/29/2011 | 2/21/2011 | 40143.2 | 14 |
786 | 800 | 12/13/2011 | 12/23/2011 | 18061.98 | 15 |
786 | 800 | 12/13/2011 | 12/23/2011 | 44607.41 | 15 |
786 | 800 | 12/13/2011 | 12/23/2011 | 5771.82 | 15 |
786 | 800 | 12/28/2011 | 12/31/2011 | 59186.25 | 16 |
786 | 800 | 12/28/2011 | 12/31/2011 | 1031.2 | 16 |
786 | 800 | 1/1/2012 | 1/5/2012 | 26177.01 | 16 |
Finally we want to group the episodes and then get the total amount in each episode, such as
ID | Service ID | Start_DT | End_DT | Amount | ESP |
123 | 300 | 3/31/2011 | 5/11/2011 | 103681.89 | 1 |
456 | 500 | 6/11/2010 | 6/14/2010 | 7481.56 | 4 |
456 | 500 | 6/30/2010 | 7/2/2010 | 8868.46 | 5 |
456 | 600 | 10/8/2010 | 10/20/2010 | 21097.30 | 10 |
456 | 700 | 10/16/2010 | 11/26/2010 | 72633.93 | 11 |
456 | 723 | 12/1/2010 | 12/3/2010 | 2576.42 | 12 |
456 | 760 | 12/4/2010 | 12/10/2010 | 6589.5 | 13 |
Thanks in advance.
First is to describe the logic you use to DEFINE episode. Examples are nice to see how the definitions are applied but the base definition is important. Such as what makes your example 4 two different episodes when example 2 isn't ?
Are your Start_dt and End_DT SAS date variables? If not it's a good idea to make them so.
I would start with something like this:
Proc sort data=<start dataset name>; by id serviceid start_dt;run;
data second;
set <start dataset name>;
by id serviceid;
retain episode 0;
if first.serviceid then episode=episode+1;
/* is suspect you want a piece here examining the current start date
with the previous end date but don't know the rule. maybe something like*/
if intck('DAY',start_dt, lag(end_dt)) > 10 then episode=episode+1;
run;
The order of output woud differ but:
proc tabulate data=second;
class id serviceid episode;
var start_dt end_dt amount;
table id*serviceid*episode,
start_dt*min=''*f=mmddyy10. end_dt*max=''*f=mmddyy10. amound*sum=''*f=f10.2;
run;
might be start
Hi ballardw,
Thank you for your reply.
The difference between rule 2 and rule 4 is because there is no gap between these two cases for rule 2, the bill ends on 11/3/2010 at the first case, and the start day on second case is 11/4/2010 while there is a gap between two cases for rule 4. The rule of building the episode relys on ID, Service ID, Start_DT, End_DT. Futhermore, Start_DT and End_DT are SAS date variables, I format them when I output this table.
Thanks again.
Assuming that stays tha are consecutive must be fused into the same episode, the solution could be:
data test;
input ID serviceID startD :mmddyy. endD :mmddyy. amount;
format startD endD yymmdd10.;
datalines;
123 300 3/31/2011 5/11/2011 1382.07
123 300 3/31/2011 5/11/2011 33153.85
123 300 3/31/2011 5/11/2011 33153.85
123 300 3/31/2011 5/11/2011 35992.12
123 300 3/31/2011 5/11/2011 35992.12
123 300 3/31/2011 5/11/2011 35992.12
123 300 3/31/2011 5/11/2011 35992.12
123 300 3/31/2011 5/11/2011 35992.12
123 300 3/31/2011 5/11/2011 25141.5
123 300 3/31/2011 5/11/2011 27960.46
123 300 3/31/2011 5/11/2011 27960.46
123 300 3/31/2011 5/11/2011 27960.46
123 300 3/31/2011 5/11/2011 27960.46
123 300 3/31/2011 5/11/2011 26823.95
123 300 3/31/2011 5/11/2011 21022.18
123 300 3/31/2011 5/11/2011 23477.92
123 300 3/31/2011 5/11/2011 32490.53
123 300 3/31/2011 5/11/2011 32490.35
123 300 3/31/2011 5/11/2011 32490.35
123 300 3/31/2011 5/11/2011 32490.35
123 300 3/31/2011 5/11/2011 30526.24
123 300 3/31/2011 5/11/2011 17711.57
123 300 3/31/2011 5/11/2011 23780.86
123 300 3/31/2011 5/11/2011 29814.05
123 300 3/31/2011 5/11/2011 29814.05
123 300 3/31/2011 5/11/2011 29814.05
123 300 3/31/2011 5/11/2011 29814.05
123 300 3/31/2011 5/11/2011 11338.58
123 300 3/31/2011 5/11/2011 33979.3
123 300 3/31/2011 5/11/2011 33978.95
123 300 3/31/2011 5/11/2011 36246.81
123 300 3/31/2011 5/11/2011 33326.88
123 300 3/31/2011 5/11/2011 26893.07
123 300 3/31/2011 5/11/2011 35127.65
123 300 3/31/2011 5/11/2011 35127.65
123 300 3/31/2011 5/11/2011 29006.91
123 300 3/31/2011 5/11/2011 29006.91
123 300 3/31/2011 5/11/2011 29006.91
123 300 3/31/2011 5/11/2011 30810.77
456 400 1/24/2010 1/27/2010 8333.5
456 400 4/8/2010 4/9/2010 15465.76
456 500 6/11/2010 6/14/2010 7481.56
456 500 6/30/2010 7/2/2010 8868.46
456 500 8/18/2010 8/22/2010 14159.72
456 500 9/1/2010 9/2/2010 6713.97
456 500 9/18/2010 9/20/2010 7651.7
456 500 9/29/2010 10/8/2010 67422.93
456 500 9/29/2010 10/8/2010 60481.26
456 500 9/29/2010 10/8/2010 44218.5
456 500 9/29/2010 10/8/2010 56635.25
456 500 9/29/2010 10/8/2010 50804.47
456 500 9/29/2010 10/8/2010 37143.54
456 600 10/8/2010 10/15/2010 15228
456 600 10/12/2010 10/20/2010 5869.3
456 700 10/16/2010 11/3/2010 60050.79
456 700 10/16/2010 11/3/2010 10226.93
456 700 11/4/2010 11/26/2010 2356.21
456 723 12/1/2010 12/3/2010 2576.42
456 760 12/4/2010 12/10/2010 6589.5
786 800 1/29/2011 2/21/2011 45181.5
786 800 1/29/2011 2/21/2011 45281.5
786 800 1/29/2011 2/21/2011 31140.23
786 800 1/29/2011 2/21/2011 10927.44
786 800 1/29/2011 2/21/2011 40143.2
786 800 12/13/2011 12/23/2011 18061.98
786 800 12/13/2011 12/23/2011 44607.41
786 800 12/13/2011 12/23/2011 5771.82
786 800 12/28/2011 12/31/2011 59186.25
786 800 12/28/2011 12/31/2011 1031.2
786 800 1/1/2012 1/5/2012 26177.01
;
proc sql;
create table stays as
select unique ID, serviceID, startD, endD
from test
order by ID, serviceID, startD;
/* Fuse stays into episodes */
data episodes(drop=episodeEnd);
set stays;
by ID serviceID;
retain ESP 0 episodeEnd 0;
if first.serviceID or startD > (episodeEnd + 1) then do;
ESP + 1;
episodeEnd = endD;
end;
else episodeEnd = max(episodeEnd, endD);
run;
proc sql;
create table testEpisodes as
select T.*, E.ESP from test as T natural join episodes as E;
select ID, serviceID, ESP, sum(amount) format=10.2 from testEpisodes
group by ID, serviceID, ESP;
quit;
PG
Subtle bug corrected by PG
Thanks PG for the information, this is exactly what we want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.