Calculate "active" observations between dates

[Scott Rick]

Hi. I have medical appointment data along with doctor details from January 2022 to July 2024. The doctor details include the date of the doctors joining the hospital and the date of them leaving. For each day between Jan 2022 - July 2024 (each day in my data), I am trying to find the number of "active" doctors. That is, the total number of working doctors, which includes those who've joined and subtracts those who've left. This would include:

1. the total number of working doctors on Jan 1, 2022 = number of who joined before Jan 1, 2022
2. the total number of working doctors on June 30, 2022 = the total number of working doctors on Jan 1, 2022 + any doctors who've joined before June 2022 - any doctors who've left before June 2022.

Any help in doing this will be much appreciated!

clear
input float appt_date str37 consultant int(doc_entryDate doc_RelievingDate)
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "B Maneesha Mohan"       22128 22863
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Mudit Tyagi"            19267     .
22646 "Sujata Das"             17198     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Anasua Kapoor"          20301     .
22646 "Mudit Tyagi"            19267     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Sujata Das"             17198     .
22646 "Sujata Das"             17198     .
22646 "Sujata Das"             17198     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Sujata Das"             17198     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "B Maneesha Mohan"       22128 22863
22646 "B Maneesha Mohan"       22128 22863
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Sujata Das"             17198     .
22646 "Tarjani Dave"           19769     .
22646 "Anasua Kapoor"          20301     .
22646 "Sujata Das"             17198     .
22646 "Sujata Das"             17198     .
22646 "Sujata Das"             17198     .
22646 "Mudit Tyagi"            19267     .
22646 "Anasua Kapoor"          20301     .
22646 "Anasua Kapoor"          20301     .
22646 "Sunita Chaurasia"       17624     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Sunita Chaurasia"       17624     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Niroj Kumar Sahoo"      22128     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Mudit Tyagi"            19267     .
22646 "Sujata Das"             17198     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Mudit Tyagi"            19267     .
22646 "Sujata Das"             17198     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Anasua Kapoor"          20301     .
22646 "Anasua Kapoor"          20301     .
22646 "Sujata Das"             17198     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "Mudit Tyagi"            19267     .
22646 "Anasua Kapoor"          20301     .
22646 "Anasua Kapoor"          20301     .
22646 "Mudit Tyagi"            19267     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Anasua Kapoor"          20301     .
22646 "Tarjani Dave"           19769     .
22648 "Mukesh Jain"            21705 23010
22648 "Debasmita Majhi"        20835     .
22648 "Debasmita Majhi"        20835     .
22648 "Sameera Nayak"          19632     .
22648 "Vivek Pravin Dave"      19769     .
22648 "Sameera Nayak"          19632     .
22648 "Debasmita Majhi"        20835     .
22648 "Sameera Nayak"          19632     .
22648 "Soumyava Basu"          16603     .
22648 "Anamika Patel"          22097 23130
22648 "Sameera Nayak"          19632     .
22648 "Pragnya Rao Donthineni" 21381     .
22648 "Muraleedhara Ramappa"   17272     .
22648 "Sameera Nayak"          19632     .
22648 "Vivek Pravin Dave"      19769     .
22648 "Sameera Nayak"          19632     .
end
format %td appt_date
format %tdnn/dd/CCYY doc_entryDate
format %tdnn/dd/CCYY doc_RelievingDate

[Nick Cox]

Here is some technique. See

S

J-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

for the main idea.

clear
input float appt_date str37 consultant int(doc_entryDate doc_RelievingDate)
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "B Maneesha Mohan"       22128 22863
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Mudit Tyagi"            19267     .
22646 "Sujata Das"             17198     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Anasua Kapoor"          20301     .
22646 "Mudit Tyagi"            19267     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Sujata Das"             17198     .
22646 "Sujata Das"             17198     .
22646 "Sujata Das"             17198     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Sujata Das"             17198     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "B Maneesha Mohan"       22128 22863
22646 "B Maneesha Mohan"       22128 22863
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Sujata Das"             17198     .
22646 "Tarjani Dave"           19769     .
22646 "Anasua Kapoor"          20301     .
22646 "Sujata Das"             17198     .
22646 "Sujata Das"             17198     .
22646 "Sujata Das"             17198     .
22646 "Mudit Tyagi"            19267     .
22646 "Anasua Kapoor"          20301     .
22646 "Anasua Kapoor"          20301     .
22646 "Sunita Chaurasia"       17624     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Sunita Chaurasia"       17624     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Niroj Kumar Sahoo"      22128     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Mudit Tyagi"            19267     .
22646 "Sujata Das"             17198     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Mudit Tyagi"            19267     .
22646 "Sujata Das"             17198     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "Anasua Kapoor"          20301     .
22646 "Anasua Kapoor"          20301     .
22646 "Sujata Das"             17198     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "Jenil Nilesh Sheth"     21581 22795
22646 "Anasua Kapoor"          20301     .
22646 "Mudit Tyagi"            19267     .
22646 "Anasua Kapoor"          20301     .
22646 "Anasua Kapoor"          20301     .
22646 "Mudit Tyagi"            19267     .
22646 "Niroj Kumar Sahoo"      22128     .
22646 "B Maneesha Mohan"       22128 22863
22646 "Anasua Kapoor"          20301     .
22646 "Tarjani Dave"           19769     .
22648 "Mukesh Jain"            21705 23010
22648 "Debasmita Majhi"        20835     .
22648 "Debasmita Majhi"        20835     .
22648 "Sameera Nayak"          19632     .
22648 "Vivek Pravin Dave"      19769     .
22648 "Sameera Nayak"          19632     .
22648 "Debasmita Majhi"        20835     .
22648 "Sameera Nayak"          19632     .
22648 "Soumyava Basu"          16603     .
22648 "Anamika Patel"          22097 23130
22648 "Sameera Nayak"          19632     .
22648 "Pragnya Rao Donthineni" 21381     .
22648 "Muraleedhara Ramappa"   17272     .
22648 "Sameera Nayak"          19632     .
22648 "Vivek Pravin Dave"      19769     .
22648 "Sameera Nayak"          19632     .
end
format %td appt_date
format %tdnn/dd/CCYY doc_entryDate
format %tdnn/dd/CCYY doc_RelievingDate

keep consultant *Date 
duplicates drop 
expand 2 
bysort consultant : gen event = _n 
by consultant : gen Date = cond(_n == 1, doc_entry, doc_Relieving) 

sort Date event 
gen wanted = sum(event == 1) - sum(event == 2 & doc_Relieving < .)
bysort Date : replace wanted = wanted[_N]

list, sepby(Date)


     +--------------------------------------------------------------------------+
     |             consultant   doc_ent~e   doc_Reli~e   event    Date   wanted |
     |--------------------------------------------------------------------------|
  1. |          Soumyava Basu   6/16/2005            .       1   16603        1 |
     |--------------------------------------------------------------------------|
  2. |             Sujata Das    2/1/2007            .       1   17198        2 |
     |--------------------------------------------------------------------------|
  3. |   Muraleedhara Ramappa   4/16/2007            .       1   17272        3 |
     |--------------------------------------------------------------------------|
  4. |       Sunita Chaurasia    4/2/2008            .       1   17624        4 |
     |--------------------------------------------------------------------------|
  5. |            Mudit Tyagi   10/1/2012            .       1   19267        5 |
     |--------------------------------------------------------------------------|
  6. |          Sameera Nayak   10/1/2013            .       1   19632        6 |
     |--------------------------------------------------------------------------|
  7. |           Tarjani Dave   2/15/2014            .       1   19769        8 |
  8. |      Vivek Pravin Dave   2/15/2014            .       1   19769        8 |
     |--------------------------------------------------------------------------|
  9. |          Anasua Kapoor    8/1/2015            .       1   20301        9 |
     |--------------------------------------------------------------------------|
 10. |        Debasmita Majhi   1/16/2017            .       1   20835       10 |
     |--------------------------------------------------------------------------|
 11. | Pragnya Rao Donthineni   7/16/2018            .       1   21381       11 |
     |--------------------------------------------------------------------------|
 12. |     Jenil Nilesh Sheth    2/1/2019    5/30/2022       1   21581       12 |
     |--------------------------------------------------------------------------|
 13. |            Mukesh Jain    6/5/2019   12/31/2022       1   21705       13 |
     |--------------------------------------------------------------------------|
 14. |          Anamika Patel    7/1/2020    4/30/2023       1   22097       14 |
     |--------------------------------------------------------------------------|
 15. |      Niroj Kumar Sahoo    8/1/2020            .       1   22128       16 |
 16. |       B Maneesha Mohan    8/1/2020     8/6/2022       1   22128       16 |
     |--------------------------------------------------------------------------|
 17. |     Jenil Nilesh Sheth    2/1/2019    5/30/2022       2   22795       15 |
     |--------------------------------------------------------------------------|
 18. |       B Maneesha Mohan    8/1/2020     8/6/2022       2   22863       14 |
     |--------------------------------------------------------------------------|
 19. |            Mukesh Jain    6/5/2019   12/31/2022       2   23010       13 |
     |--------------------------------------------------------------------------|
 20. |          Anamika Patel    7/1/2020    4/30/2023       2   23130       12 |
     |--------------------------------------------------------------------------|
 21. |      Niroj Kumar Sahoo    8/1/2020            .       2       .       12 |
 22. |       Sunita Chaurasia    4/2/2008            .       2       .       12 |
 23. |          Soumyava Basu   6/16/2005            .       2       .       12 |
 24. |            Mudit Tyagi   10/1/2012            .       2       .       12 |
 25. |          Sameera Nayak   10/1/2013            .       2       .       12 |
 26. |   Muraleedhara Ramappa   4/16/2007            .       2       .       12 |
 27. |             Sujata Das    2/1/2007            .       2       .       12 |
 28. |      Vivek Pravin Dave   2/15/2014            .       2       .       12 |
 29. | Pragnya Rao Donthineni   7/16/2018            .       2       .       12 |
 30. |        Debasmita Majhi   1/16/2017            .       2       .       12 |
 31. |          Anasua Kapoor    8/1/2015            .       2       .       12 |
 32. |           Tarjani Dave   2/15/2014            .       2       .       12 |
     +--------------------------------------------------------------------------+

[Scott Rick]

Hi Nick Cox. Thanks a lot for this and the technique documentation. Really helps understand it.

A requirement in this case complicates the application of the technique above - I need the number of active doctors on each of the appointment dates in the dataset, i.e. on all appointment dates between January 2022 to July 2024. In the application above, we delete all duplicate entries at the start and with it the appointment date variable. Merging the final data above with the appointment data is also not feasible since it would be a m:m merge. I'm at a loss then as to how

Is there any other way to do this? The only solution I've been able to come up with is a manual calculation, which is an incredibly shoddy way to tackle this problem!

[Clyde Schechter]

I'm not 100% sure I understand what you want to do, but I think it's this:

tempfile original
save `original'
keep consultant *Date
duplicates drop
expand 2
bysort consultant : gen event = _n
by consultant : gen Date = cond(_n == 1, doc_entry, doc_Relieving)

sort Date event
gen wanted = sum(event == 1) - sum(event == 2 & doc_Relieving < .)
bysort Date : keep if _n == _N
keep Date wanted
gen Date2 = Date[_n+1]-1
format Date* %td
drop if missing(Date)
list, noobs clean

rangejoin appt_date Date Date2 using `original'
drop if missing(appt_date)

The modifications to Nick's code are shown in boldface.

-rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

If this is not what you wanted, when posting back, please handwork a short example of what your desired Results would look like and post that.

[Scott Rick]

Clyde Schechter Thanks a lot! That worked perfectly and did exactly what I needed