Calculate number of patients hospitalised by day based on date of admission and of exit

[Marie Degail]

Hello Stata community,
I am working on a study looking at the impact of an antibiotic stewardship program on antibiotic consumption in a healthcare facility, using an interrupted time series analysis. I will need to compute indicators by patient-month (in order to take into account the activity of the facility).

Therefore, I would like to calculate the number of patients present each day or month in a healthcare facility based on his admission date and his exit date (discharge, death).
The results would be presented as a table with 2 columns: dates and the number of patients hospitalised on that date.
The date can be a day or a month (patient-day or patient-month). A month would actually be more useful.

I guess it is easier to start by counting the number of patients hospitalised each day (new admissions + people currently hospitalised) and then sum them for each month.
My problem is how to calculate the number of patients hospitalised on each day.

Thank you for your help.
Best
Marie-Amelie

[Marcos Almeida]

To entice a really helpful reply, you could share an scheme of the data. You may use CODE delimiters or - dataex - for that matter.

[Marie Degail]

Dear Marcos,
Thank you for your advice.
Here is an example of my dataset.
bs_d_hosp is the date of admission; bs_d_sortie is the exit date.

Thanks.

Best
Marie

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(bs_pat_id bs_d_hosp bs_d_sortie)
 1 20367 20376
 2 20367 20386
 3 20368 20375
 4 20368 20376
 5 20369 20400
 6 20369 20385
 7 20369 20390
 8 20370 20422
 9 20370 20370
10 20371 20425
11 20372 20378
12 20372 20412
13 20373 20384
14 20373 20415
15 20373 20415
16 20374 20390
18 20377 20398
19 20377 20391
20 20377 20398
21 20377 20395
end
format %dM_d,_CY bs_d_hosp
format %dM_d,_CY bs_d_sortie

[Nick Cox]

Often asked here. dm0068 is an otherwise unpredictable search term that throws up about 20 posts since 2014 in this forum.

[Marie Degail]

Hello,
Thank you Nick, these links are really useful.

However, and I'm sorry if I seem a little thick, How do I get the value of inout daily (over a period of almost two years).
I would like to end up with a table showing days and inout value for each of these days ?

Thank you again for your help.

Best

Marie-Amelie

[Marie Degail]

Hi,
So a colleague of mine managed to develop the code for what I was needing. I'll just post it here for others.

*Exclude patients with missing date of exit (bs_d_sortie)
drop if bs_d_sortie==.
 
*calculate length of stay and create
*one row for every day spent

gen dl=bs_d_sortie-bs_d_hosp
expand dl+1
bys bs_pat_id  : gen dt=bs_d_hosp+_n-1
format dt %d
 
*Count number of patients hospitalised per day
collapse (count) bs_pat_id, by(dt)
list

* To get the count of patient days by month-year
gen mdt=year(dt)*100+month(dt)
collapse (count) bs_pat_id, by(mdt)

I hope this helps future users.

Best

Marie

[Marcos Almeida]

Thank you for sharing the code that was helpful to you.

Maybe we ahould avoid dropping observations on account of missing data for just a pair of variables (who knows, the data may be further utilities).

You could create the variable this way:

gen dl=bs_d_sortie-bs_d_hosp if !missing( bs_d_hosp, bs_d_sortie )​​​​​​