Seeking help with coding the following issue

[Tariku Getaneh]

Dear all,

I would like to assign to every patient at a particular consultation date, the most frequently visited DrID (the largest count) in 1 year preceding that particular consultation date. e.g., for a patient X11 in hospital A1Z at 01-Jan-2011, the most frequently seen doctor would be A00. likewise for a patient with ID B01 in the same hospital would be also A00.

Next, I would like to create a dummy variable that =1 if a DrID for a particular consultation date and patient is the same as the most frequently seen DrID (in the previous one year window preceding this consultation).
Next, I would like to create the percentage of dummies with a value of 1 for each hospital.

Could you please assist? I am happy to clarify any related questions.

Best regards,
T

clear
input str3(hospitalID patientID) int consdate str3 DrID byte month int year
"A1Z" "B40" 18332 "A00" 3 2010
"A1Z" "B40" 18394 "A00" 5 2010
"A1Z" "B40" 18448 "A00" 7 2010
"A1Z" "B40" 18449 "A00" 7 2010
"A1Z" "B40" 18514 "A00" 10 2010
"A1Z" "B40" 18514 "A00" 10 2010
"A1Z" "B40" 18606 "B01" 12 2010
"A1Z" "B56" 18332 "A00" 3 2010
"A1Z" "B56" 18394 "A00" 5 2010
"A1Z" "B56" 18448 "A00" 7 2010
"A1Z" "B56" 18514 "A00" 10 2010
"A1Z" "B56" 18606 "B01" 12 2010
"A1Z" "C21" 18332 "A00" 3 2010
"A1Z" "C21" 18558 "B01" 10 2010
"A1Z" "C21" 18610 "A00" 12 2010
"A1Z" "C21" 18628 "A00" 1 2011
"A1Z" "X11" 18332 "A00" 3 2010
"A1Z" "X11" 18558 "B01" 10 2010
"A1Z" "X11" 18610 "A00" 12 2010
"A1Z" "X11" 18628 "A00" 1 2011
"A1Z" "Z32" 18628 "A00" 1 2011
"A1Z" "Z33" 18514 "B01" 9 2010
"A1Z" "Z34" 18525 "A00" 9 2010
"A1Z" "Z35" 18537 "B01" 10 2010
"A1Z" "Z35" 18567 "B01" 11 2010
"A1Z" "Z35" 18606 "B01" 12 2010
"A1Z" "Z35" 18628 "A00" 1 2011
"A1Z" "Z90" 18537 "B01" 10 2010
"A1Z" "Z90" 18567 "B01" 11 2010
"A1Z" "Z90" 18606 "B01" 12 2010
"A1Z" "Z90" 18628 "A00" 1 2011
"A1Z" "Z91" 18606 "B01" 12 2010
"A2" "B41" 18514 "A00" 10 2010
"A2" "B43" 18332 "A00" 3 2010
"A2" "B43" 18394 "A00" 5 2010
"A2" "B43" 18448 "A00" 7 2010
"A2" "B43" 18448 "A00" 7 2010
"A2" "B43" 18514 "A00" 10 2010
"A2" "B43" 18514 "A00" 10 2010
"A2" "B43" 18606 "B01" 12 2010
"A2" "Z91" 18606 "B01" 12 2010
"A2" "Z92" 18606 "B01" 12 2010
end

[Clyde Schechter]

Your question is unclear in a couple of respects.

1. The same patient may see the same doctor at more than one hospital. I cannot discern whether you want to count only visits with the doctor at the same hospital as the one mentioned for that visit, or if you want to count all visits to the doctor in deciding which doctor is seen most frequently.
2. Regardless of the answer to my first question, it is possible that two or more doctors will tie for most frequently seen. How do you want to break those ties?

[Tariku Getaneh]

Thanks Clyde,
Ans 1) I am interested in "count only visits with the doctors at the same hospital".
Ans 2) That is true. then the dummy variable should be equal to 1 if the current visit is just with either of the two doctors who were most frequently seen during the last one year preceding the current consultation.

I am available to answer further questions

[Clyde Schechter]

I think the following does what you ask:

preserve
keep hospitalID patientID consdate DrID
tempfile copy
save `copy'

restore
gen `c(obs_t)' obs_no = _n
rangejoin consdate -365 -1 using `copy', by(patientID hospitalID)
by obs_no DrID, sort: gen n_consultations = _N
by obs_no (n_consultations), sort: keep if n_consultations == n_consultations[_N]
by obs_no: egen byte wanted = max(DrID == DrID_U)
by obs_no: keep if _n == 1
drop consdate_U

Note: When the patient has no other consultations in that hospital in the preceding year, there is no doctor with the most consultations, and I have set the desired indicator ("dummy") variable to 0 in this case.

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

[Tariku Getaneh]

Thank you again Clyde,
I think we are very close, but I need the dummy to take a value (0 or 1) for every patient and consultation row. e.g., I can see that for a patient id B40 at hospital A1Z with consultation date 09sep2010
the dummy is missing, but it has to take a value =1. I think the data needs to be first sorted as sort hospitalID patientID consdate .

Kind regards,
T

[Clyde Schechter]

I think you have made some modification to the code, as when I run the code in #3 with your example data, there are no missing values for the variable wanted:

. preserve

. keep hospitalID patientID consdate DrID

. tempfile copy

. save `copy'
file C:\Users\clyde\AppData\Local\Temp\ST_3b24_000002.tmp saved as .dta format

.
. restore

. gen `c(obs_t)' obs_no = _n

. rangejoin consdate -365 -1 using `copy', by(patientID hospitalID)
  (using rangestat version 1.1.1)

. by obs_no DrID, sort: gen n_consultations = _N

. by obs_no (n_consultations), sort: keep if n_consultations == n_consultations[_N]
(0 observations deleted)

. by obs_no: egen byte wanted = max(DrID == DrID_U)

. by obs_no: keep if _n == 1
(45 observations deleted)

. drop consdate_U

.
. list, noobs clean

    hospit~D   patien~D   consdate   DrID   month   year   obs_no   DrID_U   n_cons~s   wanted  
         A1Z        B40      18332    A00       3   2010        1                   1        0  
         A1Z        B40      18394    A00       5   2010        2      A00          1        1  
         A1Z        B40      18448    A00       7   2010        3      A00          2        1  
         A1Z        B40      18449    A00       7   2010        4      A00          3        1  
         A1Z        B40      18514    A00      10   2010        5      A00          4        1  
         A1Z        B40      18514    A00      10   2010        6      A00          4        1  
         A1Z        B40      18606    B01      12   2010        7      A00          6        0  
         A1Z        B56      18332    A00       3   2010        8                   1        0  
         A1Z        B56      18394    A00       5   2010        9      A00          1        1  
         A1Z        B56      18448    A00       7   2010       10      A00          2        1  
         A1Z        B56      18514    A00      10   2010       11      A00          3        1  
         A1Z        B56      18606    B01      12   2010       12      A00          4        0  
         A1Z        C21      18332    A00       3   2010       13                   1        0  
         A1Z        C21      18558    B01      10   2010       14      A00          1        0  
         A1Z        C21      18610    A00      12   2010       15      A00          2        1  
         A1Z        C21      18628    A00       1   2011       16      A00          3        1  
         A1Z        X11      18332    A00       3   2010       17                   1        0  
         A1Z        X11      18558    B01      10   2010       18      A00          1        0  
         A1Z        X11      18610    A00      12   2010       19      A00          2        1  
         A1Z        X11      18628    A00       1   2011       20      A00          3        1  
         A1Z        Z32      18628    A00       1   2011       21                   1        0  
         A1Z        Z33      18514    B01       9   2010       22                   1        0  
         A1Z        Z34      18525    A00       9   2010       23                   1        0  
         A1Z        Z35      18537    B01      10   2010       24                   1        0  
         A1Z        Z35      18567    B01      11   2010       25      B01          1        1  
         A1Z        Z35      18606    B01      12   2010       26      B01          2        1  
         A1Z        Z35      18628    A00       1   2011       27      B01          3        0  
         A1Z        Z90      18537    B01      10   2010       28                   1        0  
         A1Z        Z90      18567    B01      11   2010       29      B01          1        1  
         A1Z        Z90      18606    B01      12   2010       30      B01          2        1  
         A1Z        Z90      18628    A00       1   2011       31      B01          3        0  
         A1Z        Z91      18606    B01      12   2010       32                   1        0  
          A2        B41      18514    A00      10   2010       33                   1        0  
          A2        B43      18332    A00       3   2010       34                   1        0  
          A2        B43      18394    A00       5   2010       35      A00          1        1  
          A2        B43      18448    A00       7   2010       36      A00          2        1  
          A2        B43      18448    A00       7   2010       37      A00          2        1  
          A2        B43      18514    A00      10   2010       38      A00          4        1  
          A2        B43      18514    A00      10   2010       39      A00          4        1  
          A2        B43      18606    B01      12   2010       40      A00          6        0  
          A2        Z91      18606    B01      12   2010       41                   1        0  
          A2        Z92      18606    B01      12   2010       42                   1        0  

.

Moreover, for the particular observation you cite as a problem, the value of wanted is, indeed, 1, as you say it should be.

Even if there is some error in my code,

I think the data needs to be first sorted as sort hospitalID patientID consdate .

is not part of the problem. That's because regardless of the original sort order of the data, =preserve-, -keep-, -tempfile-, -save-, -restore- and -gen- all maintain it unchanged. -rangejoin- then does its own sorting of the data, as does -by obs_no DrID, sort:...-, so that the sort order of the data are now independent of the original starting order.

[Tariku Getaneh]

Apologies Clyde,

I was just looking at the variable dummy. That perfectly works, thank you. and then I can simply compute the proportion of values with wanted equal 1 for each hospital.
If I may ask one more question related to that; suppose I want to exclude cases where the number of consultations is less than 3 in the previous one year preceding a particular consultation. Is it feasible to integrate this to the code and how?

I cannot thank you enough for kind assistance.

Best regards,
T

[Clyde Schechter]

suppose I want to exclude cases where the number of consultations is less than 3 in the previous one year preceding a particular consultation.

replace wanted = 0 if n_consultations < 3