Help using Base SAS procedures

Building Episodes

Reply
Contributor
Posts: 67

Building Episodes

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.

Super User
Posts: 11,343

Building Episodes

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

Contributor
Posts: 67

Building Episodes

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.

Respected Advisor
Posts: 4,935

Re: Building Episodes

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

PG
Contributor
Posts: 67

Building Episodes

Thanks PG for the information, this is exactly what we want.

Ask a Question
Discussion stats
  • 4 replies
  • 192 views
  • 0 likes
  • 3 in conversation