Calculating total length of stay with concurrent encounters

[Devin Parker]

Hello,
I am trying to calculate a person's total hospital stay but there are multiple encounters (claims) that take place within the same date window.

claim person date1 date2
1 789 2012-02-05 2012-02-08
2 789 2012-02-06 2012-02-07
3 789 2012-02-27 2012-02-29

Person 789 has an encounter that takes place from 2.5.2012 to 2.8.2012 = 4 days. However there is an additional claim that takes place during that time period. I don't want to count Claim 2, as those dates are already captured in Claim 1.

Person 789 has a hospital stay during Claim 3, from 2.27.12 - 2.29.12 = 3 days.

My goal is to calculate a total number of days -- therefore I do not want to count Claim 2 (2 days).

Thanks for any advice.

[Carlo Lazzaro]

Devin:
welcome to this forum.
I would try something along the following lines:

. generate double date1_real = date( date1 , "YMD")

. generate double date2_real = date( date2 , "YMD")

. format date1_real date2_real %td

. bysort person: g flag=1 if date1_real[_n]> date1_real[_n-1] & date2_real[_n]< date2_real[_n-1]

. g hospital_stay= date2_real- date1_real

. bysort person: gen wanted=sum( hospital_stay) if flag!=1


. list

     +---------------------------------------------------------------------------------------------+
     | claim   person        date1        date2   date1_r~l   date2_r~l   flag   hospit~y   wanted |
     |---------------------------------------------------------------------------------------------|
  1. |     1      789   2012-02-05   2012-02-08   05feb2012   08feb2012      .          3        3 |
  2. |     2      789   2012-02-06   2012-02-07   06feb2012   07feb2012      1          1        . |
  3. |     3      789   2012-02-27   2012-02-29   27feb2012   29feb2012      .          2        5 |
     +---------------------------------------------------------------------------------------------+

.

[Øyvind Snilsberg]

consider a more complicated scenario,

* Example generated by -dataex-. For more info, type help dataex
clear
input float(claim person date1 date2)
1 789 19010 19011
2 789 19028 19034
3 789 19029 19031
4 789 19030 19033
5 789 19040 19050
end

. rangestat (max) date2, int(date1 . 0) by(person)

. gen days = date2 - date1 if date2 == date2_max
(2 missing values generated)

. egen total_days = total(days), by(person)

. display total_days
17

[Hemanshu Kumar]

Consider an even more complicated scenario. total_days1 comes from the same calculation as in #3, total_days2 is from my modification.

clear
input float(claim person date1 date2)
1 789 19010 19032
2 789 19028 19034
3 789 19033 19036
4 789 19033 19034
5 789 19035 19037
end
format %td date?
sort person date1

rangestat (max) date2, int(date1 . 0) by(person)
gen date1_eff = max(date1, date2_max[_n-1]) 
format %td date2_max date1_eff

gen days1 = date2 - date1 if date2 == date2_max
gen days2 = date2 - date1_eff if date2 == date2_max

egen total_days1 = total(days1), by(person)
egen total_days2 = total(days2), by(person)
drop date1_eff date2_max

. display total_days1
33
. display total_days2
27
. li claim-days2, noobs
  +--------------------------------------------------------+
  | claim   person       date1       date2   days1   days2 |
  |--------------------------------------------------------|
  |     1      789   18jan2012   09feb2012      22      22 |
  |     2      789   05feb2012   11feb2012       6       2 |
  |     3      789   10feb2012   13feb2012       3       2 |
  |     4      789   10feb2012   11feb2012       .       . |
  |     5      789   12feb2012   14feb2012       2       1 |
  +--------------------------------------------------------+

[Hemanshu Kumar]

I should also add that in #3 and #4, rangestat is a community-contributed command, available via SSC.

[Nick Cox]

Here is another approach. Spoiler alert: I too get 27 days for the example #4.

See the story at https://www.stata-journal.com/articl...article=dm0068

clear
input float(claim person date1 date2)
1 789 19010 19032
2 789 19028 19034
3 789 19033 19036
4 789 19033 19034
5 789 19035 19037
end

format %td date?

save original_data, replace 

gen duration = date2 - date1 + 1 

expand duration 

bysort person claim : gen newdate = date1[1] + _n - 1 

list 

keep person newdate 

sort person newdate 

duplicates drop person newdate , force 

contract person , freq(duration) 

merge 1:m person using original_data 

list

[Hemanshu Kumar]

Nick Cox I love this way of doing it.

But you get 28

Might I suggest tweaking that one line to just

gen duration = date2 - date1

[Nick Cox]

Sorry, yes: I do get 28 days, and I think that's a fair answer. Here is the code again with extra details.

. clear

. input float(claim person date1 date2)

         claim     person      date1      date2
  1. 1 789 19010 19032
  2. 2 789 19028 19034
  3. 3 789 19033 19036
  4. 4 789 19033 19034
  5. 5 789 19035 19037
  6. end

. 
. format %td date?

. 
. save original_data, replace 
(file original_data.dta not found)
file original_data.dta saved

. 
. gen duration = date2 - date1 + 1 

. 
. expand duration 
(34 observations created)

. 
. bysort person claim : gen newdate = date1[1] + _n - 1 

. 
. list 

     +-------------------------------------------------------------+
     | claim   person       date1       date2   duration   newdate |
     |-------------------------------------------------------------|
  1. |     1      789   18jan2012   09feb2012         23     19010 |
  2. |     1      789   18jan2012   09feb2012         23     19011 |
  3. |     1      789   18jan2012   09feb2012         23     19012 |
  4. |     1      789   18jan2012   09feb2012         23     19013 |
  5. |     1      789   18jan2012   09feb2012         23     19014 |
     |-------------------------------------------------------------|
  6. |     1      789   18jan2012   09feb2012         23     19015 |
  7. |     1      789   18jan2012   09feb2012         23     19016 |
  8. |     1      789   18jan2012   09feb2012         23     19017 |
  9. |     1      789   18jan2012   09feb2012         23     19018 |
 10. |     1      789   18jan2012   09feb2012         23     19019 |
     |-------------------------------------------------------------|
 11. |     1      789   18jan2012   09feb2012         23     19020 |
 12. |     1      789   18jan2012   09feb2012         23     19021 |
 13. |     1      789   18jan2012   09feb2012         23     19022 |
 14. |     1      789   18jan2012   09feb2012         23     19023 |
 15. |     1      789   18jan2012   09feb2012         23     19024 |
     |-------------------------------------------------------------|
 16. |     1      789   18jan2012   09feb2012         23     19025 |
 17. |     1      789   18jan2012   09feb2012         23     19026 |
 18. |     1      789   18jan2012   09feb2012         23     19027 |
 19. |     1      789   18jan2012   09feb2012         23     19028 |
 20. |     1      789   18jan2012   09feb2012         23     19029 |
     |-------------------------------------------------------------|
 21. |     1      789   18jan2012   09feb2012         23     19030 |
 22. |     1      789   18jan2012   09feb2012         23     19031 |
 23. |     1      789   18jan2012   09feb2012         23     19032 |
 24. |     2      789   05feb2012   11feb2012          7     19028 |
 25. |     2      789   05feb2012   11feb2012          7     19029 |
     |-------------------------------------------------------------|
 26. |     2      789   05feb2012   11feb2012          7     19030 |
 27. |     2      789   05feb2012   11feb2012          7     19031 |
 28. |     2      789   05feb2012   11feb2012          7     19032 |
 29. |     2      789   05feb2012   11feb2012          7     19033 |
 30. |     2      789   05feb2012   11feb2012          7     19034 |
     |-------------------------------------------------------------|
 31. |     3      789   10feb2012   13feb2012          4     19033 |
 32. |     3      789   10feb2012   13feb2012          4     19034 |
 33. |     3      789   10feb2012   13feb2012          4     19035 |
 34. |     3      789   10feb2012   13feb2012          4     19036 |
 35. |     4      789   10feb2012   11feb2012          2     19033 |
     |-------------------------------------------------------------|
 36. |     4      789   10feb2012   11feb2012          2     19034 |
 37. |     5      789   12feb2012   14feb2012          3     19035 |
 38. |     5      789   12feb2012   14feb2012          3     19036 |
 39. |     5      789   12feb2012   14feb2012          3     19037 |
     +-------------------------------------------------------------+

. 
. keep person newdate 

. 
. sort person newdate 

. 
. duplicates drop person newdate , force 

Duplicates in terms of person newdate

(11 observations deleted)

. 
. contract person , freq(duration) 

. 
. merge 1:m person using original_data 

    Result                      Number of obs
    -----------------------------------------
    Not matched                             0
    Matched                                 5  (_merge==3)
    -----------------------------------------

. 
. list

     +-----------------------------------------------------------------+
     | person   duration   claim       date1       date2        _merge |
     |-----------------------------------------------------------------|
  1. |    789         28       1   18jan2012   09feb2012   Matched (3) |
  2. |    789         28       2   05feb2012   11feb2012   Matched (3) |
  3. |    789         28       3   10feb2012   13feb2012   Matched (3) |
  4. |    789         28       4   10feb2012   11feb2012   Matched (3) |
  5. |    789         28       5   12feb2012   14feb2012   Matched (3) |
     +-----------------------------------------------------------------+

.

. di mdy(2, 9, 2012) - mdy(1, 18, 2012) + 1
23

. * + 2 for claim 2 as extra days, 10 11 Feb

. * + 2 for claim 3 as extra days, 12 13 Feb

. * + 0 for claim 4 as no extra days

. * + 1 for claim 5 as extra day, 14 Feb

.

I yield to the OP who should know exactly what is needed here. But adding 1 is a defensible rule. If someone arrives and leaves on the same day is it right to say that they spent 0 days in hospital? I say that's one day in hospital.

If anyone wants a different rule, do change the rule accordingly. For example, is it really nights in hospital that count?

[Hemanshu Kumar]

Ah yes, I see the OP is calculating days this way. My bad. And in that case my version of the code would need to become:

sort person date1

rangestat (max) date2, int(date1 . 0) by(person)
gen date1_eff = max(date1, date2_max[_n-1] + 1)
gen days = cond(date2 == date2_max, date2 - date1_eff + 1, 0)

egen total_days = total(days), by(person)
drop date1_eff date2_max

[Clyde Schechter]

Here's yet another way to do this. (I'm counting the duration of an episode with the "add 1" rule.)

clear
input float(claim person date1 date2)
1 789 19010 19032
2 789 19028 19034
3 789 19033 19036
4 789 19033 19034
5 789 19035 19037
6 789 19010 19011
7 789 19028 19034
8 789 19029 19031
9 789 19030 19033
10 789 19040 19050
end

format %td date?

assert date1 <= date2
reshape long date, i(claim) j(in_out)
by person (date in_out), sort: gen depth = sum((in_out == 1) - (in_out == 2))
by person (date): gen episode = sum(depth[_n-1] == 0) + 1
by person episode (date), sort: gen episode_duration = date[_N]-date[1]+1 if _n == _N
by person (episode date): egen total_duration = total(episode_duration)

Note: Using the +1 rule, there is a certain situation that makes the problem ambiguous. If a patient is admitted on October 1 and discharged the morning of October 5, but then is readmitted the evening of October 5 and is discharged on October 9, we can either view this as a single stay of 9 days, or as a stay of 5 days followed by another stay of 5 days, for a total of 10 days. This ambiguity does not arise if we drop the +1 rule. (By the way, in the code I show here this ambiguity is resolved by treating it as a single stay of 10 days.)

[Devin Parker]

Thank you all for your guidance! I agree with the +1 rule, as I would still want to count single-day encounters. I appreciate the feedback and help.