Identifying Hospitals that were included in the survey both BEFORE as well as AFTER an Intervention

[pavan pandey]

Hi Stata Lover,

I have data from a cross-sectional study that spans a total of 5 years- 2018 to 2022.

It contains data from patients (variable caseid) visiting around 260 different hospitals (variable hospital).
The dataset contains the date of the visit in the form of day, month, and year.
A policy intervention was introduced on the 1st of November 2019.
In some of these hospitals, patients were surveyed only during 2019, while in some hospitals, patients were surveyed both during 2019 & 2020, and in some hospitals patients were surveyed only during 2020 & 2021.

I want to identify the hospitals where patients were surveyed both before November 2019 (before the intervention) and after the intervention.

From this information, I want to create a variable that divides all the hospitals into two categories

gen selected = 1 for hospitals if patients visiting those hospitals were surveyed both before as well as after Nov 2019
replace selected = 0 if the hospital was only surveyed before Nov 2019.
replace selected = 0 if the hospital was only surveyed after Nov 2019.

below if the example of my dataset.



input str15 caseid byte(day month) int(year hospital)

" 0100101305 04" 11 8 2019 1
" 0100101305 05" 12 8 2019 1
" 0100101345 02" 12 8 2019 1
" 0100101383 02" 12 8 2019 1
" 0100101383 03" 11 9 2019 1
" 0100101383 05" 11 9 2019 1
" 0100101334 02" 13 9 2019 2
" 0100101356 04" 13 9 2019 1
" 0100101356 05" 13 9 2019 1
" 0100101301 04" 11 9 2019 1
" 0100101319 04" 11 9 2019 1
" 0100101399 02" 11 9 2019 1
" 0100101399 06" 11 9 2020 5
" 0100101375 02" 12 9 2019 1
" 0100101375 03" 12 9 2019 1
" 0100101375 05" 12 9 2019 1
" 0100101337 02" 13 9 2019 10
" 0100101335 02" 11 9 2019 1
" 0100101357 02" 11 9 2019 1
" 0100101314 04" 12 9 2019 1
" 0100101314 06" 12 9 2019 22
" 0100101395 02" 13 9 2019 1
" 0100101316 02" 11 9 2019 1
" 0100101316 04" 11 9 2019 1
" 0100101358 04" 11 9 2019 1
" 0100101322 02" 11 9 2019 1
" 0100101380 02" 12 9 2019 30
" 0100101362 02" 12 9 2019 1
" 0100101362 04" 12 9 2019 1
" 0100101321 02" 12 9 2019 1
" 0100101321 03" 12 9 2019 1
" 0100101393 02" 13 9 2019 1
" 0100103091 04" 1 12 2019 1
" 0100103016 03" 4 12 2019 1
" 0100103057 02" 1 12 2019 1
" 0100103057 04" 1 12 2019 1
" 0100103080 03" 2 12 2019 1
" 0100103080 04" 2 12 2019 1
" 0100103055 04" 2 12 2019 1
" 0100103022 04" 3 12 2019 1
" 0100103068 04" 1 12 2019 1
" 0100103063 02" 1 12 2019 1
" 0100103098 02" 1 12 2019 1
" 0100103026 02" 2 12 2019 1
" 0100103026 03" 2 12 2022 234
" 0100103079 02" 1 12 2019 1
" 0100103086 04" 2 12 2019 1
" 0100103051 03" 4 12 2019 1
" 0100103051 06" 4 12 2019 1
" 0100103033 02" 3 12 2019 1
" 0100103085 02" 3 12 2019 1
" 0100103085 03" 3 12 2019 1
" 0100103085 04" 3 12 2019 1
" 0100103089 02" 3 12 2019 1
" 0100103020 02" 2 12 2019 1
" 0100103047 02" 1 12 2019 1
" 0100103047 04" 1 12 2019 1
" 0100103075 03" 4 12 2019 1
" 0100100678 02" 14 9 2021 98
" 0100100678 03" 14 9 2019 1
" 0100100686 02" 14 9 2019 1
" 0100100686 03" 14 9 2019 1
" 0100100631 04" 14 9 2019 1
" 0100100627 04" 15 9 2019 1
" 0100100627 05" 15 9 2019 1
" 0100100627 06" 16 9 2019 1
" 0100100627 07" 16 9 2019 1
" 0100100654 04" 14 9 2019 1
" 0100100643 04" 16 9 2019 1
" 0100100643 05" 16 9 2019 1
" 0100100618 04" 14 9 2019 1
" 0100100624 05" 15 9 2019 1
" 0100100650 02" 15 9 2019 1
" 0100100661 02" 14 9 2019 1
" 0100100661 03" 14 9 2019 1
" 0100100616 04" 15 9 2019 1
" 0100100616 06" 15 9 2019 1
" 0100100602 02" 16 9 2019 1
" 0100100693 02" 15 9 2019 1
" 0100100667 02" 15 9 2019 1
" 0100100690 02" 16 9 2019 1
" 0100100698 02" 16 9 2019 1
" 0100100626 02" 15 9 2019 1
" 0100100695 02" 15 9 2019 1
" 0100100692 02" 15 9 2019 1
" 0100103165 02" 24 11 2019 1
" 0100103157 02" 24 11 2019 1
" 0100103157 03" 24 11 2019 1
" 0100103105 02" 24 11 2019 1
" 0100103173 04" 25 11 2019 1
" 0100103153 04" 24 11 2019 1
" 0100103167 03" 24 11 2019 1
" 0100103185 02" 25 11 2019 1
" 0100103138 02" 25 11 2019 1
" 0100103123 02" 24 10 2021 189
" 0100103107 04" 24 11 2019 1
" 0100103133 04" 25 11 2019 1
" 0100103148 02" 25 11 2019 1
" 0100103162 04" 24 11 2022 194
" 0100103129 02" 26 11 2019 1

[Clyde Schechter]

Try this:

gen mdate = ym(year, month)
assert !missing(mdate)
format mdate %tm

by hospital (mdate day caseid), sort: egen surveyed_before_nov2019 ///
    = max(mdate < tm(2019m11))
by hospital (mdate day caseid): egen surveyed_after_nov2019 ///
    = max(mdate > tm(2019m11))
gen byte selected = surveyed_before_nov2019 & surveyed_after_nov2019

What is not clear is the status of surveys carried out during the month of November 2019 itself. As this is neither before nor after, it doesn't fit into either category. The code above takes this literally, so surveys conducted during November 2019 simply "don't count" at all. You might want to count them one way or another, and that would inbvolve changing one of the < or > symbols to <= or >=, respectively. (As it happens, in the example data the only hospital that did have surveys in November 2019 also had them both before and after, so it makes no difference. But there may be some hospitals in the data where surveys in November 2019 might make the difference between selection and non-selection.)

[pavan pandey]

Thank you very much, Dr Clyde. The codes worked perfectly.