Counting observations in range

[Wei Hai Deng]

Dear Statalists,

I want to count total number of patients at a hospital while a patient is waiting for treatment, after Evaluation, for each patient/observation.
The date variables are Start, Evaluation, and Treatment. I want to generate a variable that counts how many patients have a daterange from Start to Treatment that includes the current patient's Evaluation to End interval.

ie.

ID	Start	Evaluation	End	Count
1	1 Jan	5 Jan	7 Jan	2
2	4 Jan	8 Jan	20 Jan	3
3	13 Jan	15 Jan	18 Jan	2
4	19 Jan	20 Jan	30Jan	0

I have tried rangestat from SSC but it doesn't check a range. It counts number of observations with e.g. Start within the current observation's Evaluation to End range.

Thank you for your help!

Here's the dataex:

* Example generated by -dataex-. For more info, type help dataex
clear
input float ID int(Start Evaluation Treatment)
  1 23644 23676 23694
  2 23533 23562 23578
  3 23407 23423 23442
  4 23800 23821 23829
  5 23875 23889     .
  6 23887     .     .
  7 23611 23648 23649
  8 23670 23696     .
  9 23695 23765 23767
 10 23709 23751 23765
 11 23954     .     .
 12 23695 23759 23801
 13 23538 23547     .
 14 23643 23654     .
 15 23778 23784 23796
 16 23686 23715 23732
 17 23918 23927 23935
 18 23507 23519     .
 19 23603 23637     .
 20 23815 23865 23898
 21 23428 23456 23478
 22 23952 23962 23975
 23 23505 23710     .
 24 23757 23767     .
 25 23757 23780 23794
 26 23440 23458     .
 27 23692     .     .
 28 23763 23780 23788
 29 23412 23442 23445
 30 23842 23875 23891
 31 23944 23946     .
 32 23862 23914 23927
 33 23482 23487     .
 34 23555 23575 23599
 35 23569 23584 23612
 36 23429 23464 23478
 37 23786 23802 23815
 38 23685 23725 23759
 39 23792 23802     .
 40 23625 23655 23656
 41 23607 23619     .
 42 23462 23486     .
 43 23497 23519     .
 44 23645 23651     .
 45 23645 23658 23722
 46 23650 23659     .
 47 23469 23484     .
 48 23582 23654 23660
 49 23867 23882 23894
 50 23931 23966 23974
 51 23568 23577 23582
 52 23602 23612     .
 53 23388 23422 23423
 54 23629 23633     .
 55 23905 23918 23928
 56 23864 23900 23914
 57 23482 23511 23520
 58 23798 23816 23838
 59 23526 23528 23547
 60 23651 23857 23913
 61 23602 23645 23660
 62 23533 23618 23612
 63 23834 23858     .
 64 23472 23483     .
 65 23548 23574 23619
 66 23883 23919     .
 67 23668 23725 23779
 68 23877 23891 23947
 69 23597 23646     .
 70 23660 23683     .
 71 23659 23672 23696
 72 23955 23967     .
 73 23856 23871     .
 74 23734 23749     .
 75 23854 23862     .
 76 23561 23574 23597
 77 23820 23913 23935
 78 23491     .     .
 79 23799 23800 23816
 80 23661 23669 23674
 81 23580 23591 23617
 82 23637 23674 23694
 83 23695 23696 23722
 84 23752 23876     .
 85 23749 23757     .
 86 23899 23910 23928
 87 23512 23534     .
 88 23422 23442 23458
 89 23877 23896     .
 90 23720 23780 23806
 91 23751 23764 23771
 92 23603 23623     .
 93 23680 23716 23731
 94 23750 23779 23800
 95 23756 23815 23816
 96 23387 23396 23403
 97 23573 23602 23614
 98 23752 23808 23809
 99 23807 23820 23837
100 23829 23861 23891
end
format %td Start
format %td Evaluation
format %td Treatment


rangestat (count) currently_in_treatment = ID if !missing(Start, Evaluation, Treatment), interval(Start Evaluation Treatment)
*

[Clyde Schechter]

I can't reconcile your example data with your stated problem. From your stated problem, it seems you need four variables: start, end, evaluation, and treatment dates. But you don't show a treatment date.

Also, when you say "daterange from Start to Treatment that includes the current patient's Evaluation to End interval." do you mean that the evaluation to end interval is completely contained between the start and treatment dates? Or do you mean that the evaluation to end interval overlaps the interval from start to treatment?

You are correct that -rangestat- is not the solution to your problem.

Let me assume that you have variables named start_date end_date (you should not name a variable "end" in Stata--you might get away with it, but it could cause problems because end is already a Stata reserved word), evaluation_date and treatment_date.

If you are looking to identify the situations where the evaluation to end interval is completely contained between the start and treatment dates, then this will give it to you:

gen byte is_contained = (start_date >= evaluation_date & end_date <= treatment_date)

If you are looking to identify the situations where the evaluation to end interval overlaps the start to treatment interval, then it would be this:

gen byte overlaps = max(start_date, evaluation_date) <= min(treatment_date, end_date)

[Wei Hai Deng]

Hi Clyde,

Thank you for your reply and sorry for the confusion in variable names, there's no End variable, this is a misstype on my part...

To restate my question, assume three date variables, in chronological order: admission_date, evaluation_date, treatment_date.
Say for patient ID == 1, I want to count total number of patients (in ID == 1 to 100) with an "admission_date" to "treatment_date" that includes patient ID == 1's "evaluation_date" to "treatment_date". Not necessarily completely contained. I want to count any with an overlapping range.

I have done the below for now and think it works? It is however laborious and extremely inefficient (n=100 000+) since it calculates a single ID at a time. Suggestions for improvements/alternatives?

clear all
input float ID int(admission_date evaluation_date treatment_date)
  1 23644 23676 23694
  2 23533 23562 23578
  3 23407 23423 23442
  4 23800 23821 23829
  5 23875 23889     .
  6 23887     .     .
  7 23611 23648 23649
  8 23670 23696     .
  9 23695 23765 23767
 10 23709 23751 23765
 11 23954     .     .
 12 23695 23759 23801
 13 23538 23547     .
 14 23643 23654     .
 15 23778 23784 23796
 16 23686 23715 23732
 17 23918 23927 23935
 18 23507 23519     .
 19 23603 23637     .
 20 23815 23865 23898
 21 23428 23456 23478
 22 23952 23962 23975
 23 23505 23710     .
 24 23757 23767     .
 25 23757 23780 23794
 26 23440 23458     .
 27 23692     .     .
 28 23763 23780 23788
 29 23412 23442 23445
 30 23842 23875 23891
 31 23944 23946     .
 32 23862 23914 23927
 33 23482 23487     .
 34 23555 23575 23599
 35 23569 23584 23612
 36 23429 23464 23478
 37 23786 23802 23815
 38 23685 23725 23759
 39 23792 23802     .
 40 23625 23655 23656
 41 23607 23619     .
 42 23462 23486     .
 43 23497 23519     .
 44 23645 23651     .
 45 23645 23658 23722
 46 23650 23659     .
 47 23469 23484     .
 48 23582 23654 23660
 49 23867 23882 23894
 50 23931 23966 23974
 51 23568 23577 23582
 52 23602 23612     .
 53 23388 23422 23423
 54 23629 23633     .
 55 23905 23918 23928
 56 23864 23900 23914
 57 23482 23511 23520
 58 23798 23816 23838
 59 23526 23528 23547
 60 23651 23857 23913
 61 23602 23645 23660
 62 23533 23618 23612
 63 23834 23858     .
 64 23472 23483     .
 65 23548 23574 23619
 66 23883 23919     .
 67 23668 23725 23779
 68 23877 23891 23947
 69 23597 23646     .
 70 23660 23683     .
 71 23659 23672 23696
 72 23955 23967     .
 73 23856 23871     .
 74 23734 23749     .
 75 23854 23862     .
 76 23561 23574 23597
 77 23820 23913 23935
 78 23491     .     .
 79 23799 23800 23816
 80 23661 23669 23674
 81 23580 23591 23617
 82 23637 23674 23694
 83 23695 23696 23722
 84 23752 23876     .
 85 23749 23757     .
 86 23899 23910 23928
 87 23512 23534     .
 88 23422 23442 23458
 89 23877 23896     .
 90 23720 23780 23806
 91 23751 23764 23771
 92 23603 23623     .
 93 23680 23716 23731
 94 23750 23779 23800
 95 23756 23815 23816
 96 23387 23396 23403
 97 23573 23602 23614
 98 23752 23808 23809
 99 23807 23820 23837
100 23829 23861 23891
end
format %td admission_date
format %td evaluation_date
format %td treatment_date


gen concurrent_patients = .
gen countvar = .

qui levelsof ID, local(id_list)

foreach var of local id_list {
    di "`var'"
    
    * Get evaluation and treatment dates of one patient
    quietly sum evaluation_date    if ID==`var', meanonly
        local eval  = r(mean)
    quietly sum treatment_date     if ID==`var', meanonly
        local treat = r(mean)

    if (`eval' != . & `treat' != .) {
        * Check who else has overlapping admission to treatment date
        replace countvar = 1    if inrange(`eval', admission_date, treatment_date)
        replace countvar = 1    if inrange(`treat', admission_date, treatment_date)
        replace countvar = 1    if `eval' < min(admission_date, treatment_date) & `treat' > max(admission_date,treatment_date) // if others' daterange is completely contained within
    
        * Count and store
        count if countvar == 1
            replace concurrent_patients = r(N) if ID == `var'
            replace countvar = . // reset
    }
}
drop countvar

[Clyde Schechter]

assume three date variables, in chronological order: admission_date, evaluation_date, treatment_date

I have two problems with assuming this. The first is that, in your example data, it isn't true. Look at ID 62 whose admission, evaluation, and treatment dates are 6jun2024, 30aug2024, and 24aug2024, respectively. Here the evaluation date comes after the treatment date.

The second problem is that if it were true, the problem would be entirely trivial. If admission_date <= evaluation_date <= treatment_date, then the admission to treatment date interval always contains the evaluation to treatment date interval. So the answer to your problem is then: all patients satisfy this property.

Examining your code, it is difficult to try to improve it when I still do not understand what exactly you are trying to calculate. That said, for now, I will only point out that it contains numerous constructions that would be inefficient for solving any problem in Stata. Moreover, the code you show seems designed to handle a data set where each ID has more than one record in the data, but your example data, to the contrary, has only one observation per ID.

As I don't think that is what you mean, I am left with asking you to try to explain more clearly what you have and what you want. Perhaps in addition to explaining in words, you could provide a new data example containing some observations where the condition is satisfied and some where it is not where you have worked out the solution and show that. Once I get a clear picture of the kind of data you have and a clear understanding of what you want to do, I will try to help.

[Wei Hai Deng]

Thank you for your patience with me as I try to figure out how to explain my problem.

I want to know total number of patients that are being processed (admission_date -> treatment_date) at the same time as a patient is being evaluated for treatment (evaluation_date -> treatment_date).
The background is that a high number of patients at the clinic, over a certain threshold, usually leads to much longer wait-times. I want to visualize this relationship.

For a simplified example; letters A-E are individual patients, X's are the days they're at the hospital:

Days

1

2

3

4

5

6

7

8

9

10

11

12

A

X

X

X

X

B

X

X

X

C

X

X

X

D

X

X

X

E

X

X

F

X

X

The counts here would be:
A 4
B 2
C 2
D 3
E 1
F 2

I am not checking which rows have admission_date <= evaluation_date <= treatment_date, that would be this?:

gen date_order = admission_date <= evaluation_date & evaluation_date <= treatment_date if !missing(admission_date, evaluation_date, treatment_date)
    label define date_order_ 0 "Non-chronological" 1 "Chronological"
    label values date_order date_order_

Here's an updated example. The three dates are indeed not always chronological, but these are coding errors that are normally fixed through a separate algorithm with checks to other databases. I've implemented a simple fix for the example below.
I'm aware the code is very poorly constructed... but with a lack of ideas I've resorted to brute-forcing for the sake of the example.

clear all
input float ID int(admission_date evaluation_date treatment_date)
  1 23644 23676 23694
  2 23533 23562 23578
  3 23407 23423 23442
  4 23800 23821 23829
  5 23875 23889     .
  6 23887     .     .
  7 23611 23648 23649
  8 23670 23696     .
  9 23695 23765 23767
 10 23709 23751 23765
 11 23954     .     .
 12 23695 23759 23801
 13 23538 23547     .
 14 23643 23654     .
 15 23778 23784 23796
 16 23686 23715 23732
 17 23918 23927 23935
 18 23507 23519     .
 19 23603 23637     .
 20 23815 23865 23898
 21 23428 23478 23456 
 22 23952 23962 23975
 23 23505 23710     .
 24 23757 23767     .
 25 23757 23780 23794
 26 23440 23458     .
 27 23692     .     .
 28 23763 23780 23788
 29 23412 23442 23445
 30 23842 23875 23891
 31 23944 23946     .
 32 23862 23914 23927
 33 23482 23487     .
 34 23555 23575 23599
 35 23584 23569 23612
 36 23429 23464 23478
 37 23786 23802 23815
 38 23685 23725 23759
 39 23792 23802     .
 40 23625 23655 23656
 41 23607 23619     .
 42 23462 23486     .
 43 23497 23519     .
 44 23645 23651     .
 45 23645 23658 23722
 46 23650 23659     .
 47 23469 23484     .
 48 23582 23654 23660
 49 23867 23882 23894
 50 23931 23966 23974
 51 23568 23577 23582
 52 23602 23612     .
 53 23388 23422 23423
 54 23629 23633     .
 55 23905 23918 23928
 56 23864 23900 23914
 57 23482 23511 23520
 58 23798 23816 23838
 59 23526 23528 23547
 60 23651 23857 23913
 61 23602 23645 23660
 62 23533 23618 23612
 63 23834 23858     .
 64 23472 23483     .
 65 23548 23574 23619
 66 23883 23919     .
 67 23668 23725 23779
 68 23877 23891 23947
 69 23597 23646     .
 70 23660 23683     .
 71 23659 23672 23696
 72 23955 23967     .
 73 23856 23871     .
 74 23734 23749     .
 75 23854 23862     .
 76 23561 23574 23597
 77 23913 23820 23935
 78 23491     .     .
 79 23799 23800 23816
 80 23661 23669 23674
 81 23580 23591 23617
 82 23637 23674 23694
 83 23695 23696 23722
 84 23752 23876     .
 85 23749 23757     .
 86 23899 23910 23928
 87 23512 23534     .
 88 23422 23442 23458
 89 23877 23896     .
 90 23720 23780 23806
 91 23751 23764 23771
 92 23603 23623     .
 93 23680 23716 23731
 94 23750 23779 23800
 95 23756 23815 23816
 96 23387 23403 23396 
 97 23573 23602 23614
 98 23752 23808 23809
 99 23807 23820 23837
100 23829 23861 23891
end
format %td admission_date
format %td evaluation_date
format %td treatment_date

gen date_order = admission_date <= evaluation_date & evaluation_date <= treatment_date if !missing(admission_date, evaluation_date, treatment_date)
    label define date_order_ 0 "Non-chronological" 1 "Chronological"
    label values date_order date_order_

gen concurrent_patients = .
gen countvar = .

forval num = 1/100 {
    di "`var'"
    
    * Get evaluation and treatment dates of one patient
    quietly sum evaluation_date    if ID==`num', meanonly
        local eval  = r(mean)
    quietly sum treatment_date     if ID==`num', meanonly
        local treat = r(mean)

    
    * Check who else has overlapping admission to treatment date 
    replace countvar = 1    if inrange(`eval', admission_date, treatment_date)
    replace countvar = 1    if inrange(`treat', admission_date, treatment_date)
    replace countvar = 1    if `eval' < min(admission_date, treatment_date) & `treat' > max(admission_date,treatment_date) // if others' daterange is completely contained within

    * Count and store
    count if countvar == 1
        replace concurrent_patients = r(N) if ID == `num' & date_order == 1
        replace countvar = . // reset

}
drop countvar

[Clyde Schechter]

I'm sorry, but I'm still confused. In your tableau with the X's, and the subsequent counts you provide, I do not see a consistent pattern. For some of the patients in the tableau (A, D, and F) the count shown equals the number of X's. But for the others, it is the number of X's less one. How do you decide when to reduce the count by 1 and when to leave it?

The following code will identify the patients for whom the admission to treatment interval and the evaluation to treatment interval overlap, and give you an accounting of how many such there are:

gen byte overlap = (max(admission_date, evaluation_date) < treatment_date) ///
    if !missing(admission_date, evaluation_date, treatment_date)
tab overlap, miss

Now, notice that I have restricted the calculation to those observations where none of the three dates is missing. Without information on all three dates, it is, in principle, not possible to determine whether the intervals overlap.

[Wei Hai Deng]

The counts are not per patient, but how many total patients who share the same dates. Looking down the columns.

So e.g. patient A was at the hospital on days 4 - 7, we have four patients sharing the same days, ie.:
(Patient A him/herself) + (patient B who shares day 7) + (patient D who shares day 4) + (patient F who shares day 5 and 6) = 4 people who were at the hospital when patient A was there on days 4-7.

[Clyde Schechter]

Ah, now I get it! And, looking back at #1, you did say

I want to know total number of patients that are being processed (admission_date -> treatment_date) at the same time as a patient is being evaluated for treatment (evaluation_date -> treatment_date).

but, for whatever reason, I misunderstood it. I'm sorry about that.

//  VERIFY EACH ID HAS ONLY ONE OBSERVATION
isid ID // DO NOT PROCEED IF THIS COMMAND GIVES AN ERROR MESSAGE

preserve
keep ID admission_date treatment_date
rename * =_U
tempfile treatment_intervals
save `treatment_intervals'

restore
cross using `treatment_intervals'
drop if ID == ID_U
gen byte overlap = max(evaluation_date, admission_date_U) ///
    <= min(treatment_date, treatment_date_U)
by ID (overlap), sort: replace overlap = sum(overlap)
by ID: keep if _n == _N
drop *_U

Note: If your data set is very large, this code will be slow, and if it is extremely large, it will break through your system's memory limits. To conserve memory as much as possible, you should -compress- the data set before running this code, and you should also -drop- any variables other than ID, admission_date, and evaluation_date. (To recover those other variables, you can always -merge- the results of this code with the original data sets.) If even after doing that you still run out of memory when running this code, post back, and I will show you another way to do it that is a bit more complicated, and slower, but avoids this problem.

[Wei Hai Deng]

WOW! I'm left speechless at your code Clyde Schechter. This is so clever/elegant, and functions I was unaware of.

• I didn't know you could append suffixes to all variables like that.
• cross is also new to me. The mental gymnastics required to figure out your use of it here is simply impressive.
  • Additionally, [by ID: keep if _n == _N] is also very clever for cleaning up while keeping the last running sum(). Thanks for showing this.

drop if ID == ID_U

I'll be skipping this since I am counting all patients, including the patient themselves.

by ID (overlap), sort: replace overlap = sum(overlap)

Is sorting by overlap necessary? [by ID: keep if _n == _N] should anyway be the sum of all overlap?

Note: If your data set is very large, this code will be slow, and if it is extremely large, it will break through your system's memory limits.

>100 000 rows, I can reduce to half by restricting to those !missing(admission_date, evaluation_date, treatment_date), at which point I can split my data in two with some time overlap since they're over a 10 year period. That way I won't hit the ~2,000,000,000 row limit.

I'll let you know how it goes, but out of curiosity for the alternative method, would it be okay to ask about the logic, if not the code itself?

[Clyde Schechter]

Is sorting by overlap necessary? [by ID: keep if _n == _N] should anyway be the sum of all overlap?

You are correct. The secondary sorting by variable overlap is not necessary.

I'll let you know how it goes, but out of curiosity for the alternative method, would it be okay to ask about the logic, if not the code itself?

Since what I posted yesterday, I realized there is an even simpler way than what I had in mind then. It will be fairly slow in a large data set, but it requires very little memory beyond the original data set itself.

isid ID //  DO NOT PROCEED IF THIS GENERATES AN ERROR MESSAGE

drop if missing(admission_date, evaluation_date, treatment_date)

gen `c(obs_t)' result = .
forvalues focal = 1/`=_N' {
    gen byte overlap = max(evaluation_date[`focal'], admission_date) ///
        <= min(treatment_date[`focal'], treatment_date)
    summ overlap, meanonly
    replace result = `r(sum)' in `focal'
    drop overlap
}

The above code follows your practice set out in #9 in including the focal patient in the count.

[Clyde Schechter]

Actually, here's a better way. It requires a bit less than twice as much memory as the original data set. But it will produce more accurate results. Here's the key reasoning behind it: removing all observations with missing values on any of admission_date, evaluation_date, and treatment_date is actually excessive. For an observation to serve in the focal role, the admission date is not relevant: it only needs evaluation_date and treatment_date. Similarly to serve (and be counted) in the non-focal role, it only needs admission_date and treatment_date: the evaluation_date is not relevant. The code below allows every observation to be used in whichever role(s) it is capable of, so undercounting due to missing values that are not needed anyway is eliminated.

isid ID

frame put admission_date treatment_date ///
    if !missing(admission_date, treatment_date), into(admissions)

gen `c(obs_t)' result = .
forvalues focal = 1/`=_N' {
    if !missing(evaluation_date[`focal'], treatment_date[`focal']) {
        local eval = evaluation_date[`focal']
        local treat = treatment_date[`focal']
        frame admissions {
            gen byte overlap =  max(`eval', admission_date) ///
                <= min(`treat', treatment_date)
            summ overlap, meanonly
        }
        replace result = `r(sum)' in `focal'
        frame admissions: drop overlap
    }
}

Observations that are missing their admission or treatment dates will have missing values for the result variable.

Note: This code uses frames, so it requires version 16 or later. This is one of the circumstances where, for practical purposes, a -tempfile- could not be used instead of a frame: that would entail excessive disk thrashing and the execution time would be unacceptable.

[Wei Hai Deng]

This is immensely helpful, and so much faster?
Could you explain why this is faster than my own "brute-force" method? You at least shorten the operations by limiting which rows with an if { } statement for the whole operation.
I too did however a single row at a time. But you instead refer to its location directly instead of using an "if" condition?
Also, you create the temporary "overlap" variable inside the frame?

Also, thank you for making me aware of, and figuring out a way, to also include those who had partial missings.

[Clyde Schechter]

Could you explain why this is faster than my own "brute-force" method? You at least shorten the operations by limiting which rows with an if { } statement for the whole operation.
I too did however a single row at a time. But you instead refer to its location directly instead of using an "if" condition? [emphasis added]

Referring to the location directly, or with an -in- condition instead of an -if- condition accounts for most of the speedup. Other than repetitive disk operations, there is nothing that slows a Stata program down more than -if- conditions inside of loops. When you use an -if- condition, Stata has to check every observation in the entire data set and evaluate the condition to determine whether the observation is to be included in the calculations. Inside a loop, this means that every observation in the entire data set gets rechecked every time through the loop. If the number of observations is large and the loop iterates many times this really adds up! When it is possible to replace those -if-s by direct subscript reference to the observation, or identifying the observation(s) with an -in- condition, Stata can just go directly to the relevant observation(s) and ignore the rest of the data set. As here, the time-savings can be enormous. So avoiding -if- inside loops is an important trick for speeding up code slow-running code.

I should add that processing one observation at a time is usually slow in Stata. Whenever possible, is better to do repetitive calculations using -by:-. But that isn't possible for this particular calculation, so we loop over individual observations.

Also, you create the temporary "overlap" variable inside the frame?

Yes, because all of the information that overlap is calculated from is either in local macros (which don't belong to any frame but to the do-file itself) or in variables inside the frame. It is possible to create the variable outside of frame admissions, but that would require using the -frval()- function to access the data inside the frame, which would be slow things down.

[Wei Hai Deng]

Thank you again for teaching me about efficient Stata code. I appreciate it!

I hadn't thought about the issue of looping over -if- conditions.
I assume -frval()- also slows down processing time when having to refer to an "external" dataset.
The processing time was cut down from 3+hours with my -if- condition based method, to 20min with -cross- to 3min with position references. Massive improvements!