Counting "active by date" from start and end dates

[Randy Absher]

Hello StataList,

In my dataset of hospital consultations, each observation represents one consultation and variables include the consultation start date and end date. Am seeking to either tabulate or just count the number of consultations active on each date.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id startdate enddate)
1 21124 21125
2 21124 21126
3 21124 21136
4 21125 21129
5 21125 21127
6 21125 21128
7 21126 21130
8 21126 21130
end
format %td startdate
format %td enddate

Had originally thought -egen- perhaps followed by -reshape- might be first steps to solve, but honestly haven't made any progress so far.

Any advice or suggestions would be most appreciated.

Thank you -
Randy Absher

[William Lisowski]

Welcome to Statalist.

Starting from the data you provide (thank you for using dataex, that made my task easy) the following code should help, if I correctly understand what you want.

expand enddate - startdate + 1
clonevar active = startdate
bysort id (startdate): replace active = active + (_n-1)
list if id<=2, sepby(id)
tab active

. list if id<=2, sepby(id)

     +----------------------------------------+
     | id   startdate     enddate      active |
     |----------------------------------------|
  1. |  1   01nov2017   02nov2017   01nov2017 |
  2. |  1   01nov2017   02nov2017   02nov2017 |
     |----------------------------------------|
  3. |  2   01nov2017   03nov2017   01nov2017 |
  4. |  2   01nov2017   03nov2017   02nov2017 |
  5. |  2   01nov2017   03nov2017   03nov2017 |
     +----------------------------------------+

. tab active

     active |      Freq.     Percent        Cum.
------------+-----------------------------------
  01nov2017 |          3        7.50        7.50
  02nov2017 |          6       15.00       22.50
  03nov2017 |          7       17.50       40.00
  04nov2017 |          6       15.00       55.00
  05nov2017 |          5       12.50       67.50
  06nov2017 |          4       10.00       77.50
  07nov2017 |          3        7.50       85.00
  08nov2017 |          1        2.50       87.50
  09nov2017 |          1        2.50       90.00
  10nov2017 |          1        2.50       92.50
  11nov2017 |          1        2.50       95.00
  12nov2017 |          1        2.50       97.50
  13nov2017 |          1        2.50      100.00
------------+-----------------------------------
      Total |         40      100.00

[Randy Absher]

Thank you William! Extremely helpful!

Randy Absher

[Nick Cox]

Here is another way to think about it.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id startdate enddate)
1 21124 21125
2 21124 21126
3 21124 21136
4 21125 21129
5 21125 21127
6 21125 21128
7 21126 21130
8 21126 21130
end
format %td startdate
format %td enddate

gen long obs = _n 
expand 2 
bysort obs : gen date = cond(_n == 1, start, end) 
by obs : gen event = (_n == 1) - (_n == 2) 
gsort date -event 
gen nactive = sum(event) 

list, sepby(date) 

     +------------------------------------------------------------+
     | id   startdate     enddate   obs    date   event   nactive |
     |------------------------------------------------------------|
  1. |  3   01nov2017   13nov2017     3   21124       1         1 |
  2. |  2   01nov2017   03nov2017     2   21124       1         2 |
  3. |  1   01nov2017   02nov2017     1   21124       1         3 |
     |------------------------------------------------------------|
  4. |  4   02nov2017   06nov2017     4   21125       1         4 |
  5. |  5   02nov2017   04nov2017     5   21125       1         5 |
  6. |  6   02nov2017   05nov2017     6   21125       1         6 |
  7. |  1   01nov2017   02nov2017     1   21125      -1         5 |
     |------------------------------------------------------------|
  8. |  7   03nov2017   07nov2017     7   21126       1         6 |
  9. |  8   03nov2017   07nov2017     8   21126       1         7 |
 10. |  2   01nov2017   03nov2017     2   21126      -1         6 |
     |------------------------------------------------------------|
 11. |  5   02nov2017   04nov2017     5   21127      -1         5 |
     |------------------------------------------------------------|
 12. |  6   02nov2017   05nov2017     6   21128      -1         4 |
     |------------------------------------------------------------|
 13. |  4   02nov2017   06nov2017     4   21129      -1         3 |
     |------------------------------------------------------------|
 14. |  7   03nov2017   07nov2017     7   21130      -1         2 |
 15. |  8   03nov2017   07nov2017     8   21130      -1         1 |
     |------------------------------------------------------------|
 16. |  3   01nov2017   13nov2017     3   21136      -1         0 |
     +------------------------------------------------------------+

A reference is

SJ-13-1 dm0068  . . . . . Stata tip 114: Expand paired dates to pairs of dates
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q1/13   SJ 13(1):217--219                                (no commands)
        tip on using expand to deal with paired dates

which is also accessible at http://www.stata-journal.com/sjpdf.h...iclenum=dm0068

The otherwise hard-to-guess search term dm0068 will find some related threads in this forum.

[Randy Absher]

Thank you Nick! I will explore those. Appreciate your help!

Randy Absher