Encounter level dataset to day level

[Mohammed SA]

Hi all,
I have data with repeated measures for the same individual admission. I am trying to create days of admission variable based on the admission and test result date variable. For the example if test result within the first day of admission the new variable will get a value of 1.
Here is an example of the new variable that I am trying to crate (Day_admt).

id	Admt_dt	Bg_result_dt	Bg_result	Day_admt
1	6/1/22 9:30	6/1/22 18:30	100	1
1	6/1/22 9:30	6/1/22 20:30	120	1
1	6/1/22 9:30	6/1/22 23: 30	200	1
1	6/1/22 9:30	6/2/22 11:30	70	2

Then I will need to collapse the bg_result per day of admission.

input str10 id float(bg_result bg_result_dt admt_dt)
"1" 85 1.765697e+12 1.7656944e+12
"1" 150 1.7657087e+12 1.7656944e+12
"1" 93 1.765778e+12 1.7656944e+12
"1" 79 1.7658167e+12 1.7656944e+12
"1" 66 1.7658354e+12 1.7656944e+12
"1" 167 1.765839e+12 1.7656944e+12
"1" 103 1.7658865e+12 1.7656944e+12
"1" 91 1.7659046e+12 1.7656944e+12
"1" 103 1.7659196e+12 1.7656944e+12
"1" 70 1.7661307e+12 1.7656944e+12
"1" 74 1.7661446e+12 1.7656944e+12
"2" 76 1.76596e+12 1.7659547e+12
"2" 102 1.7659816e+12 1.7659547e+12
"2" 109 1.7660046e+12 1.7659547e+12
"2" 84 1.7660277e+12 1.7659547e+12
"2" 101 1.766036e+12 1.7659547e+12
"2" 88 1.766057e+12 1.7659547e+12
"2" 90 1.766069e+12 1.7659547e+12
"2" 80 1.766082e+12 1.7659547e+12
"2" 92 1.766105e+12 1.7659547e+12
"2" 97 1.7661344e+12 1.7659547e+12
"2" 94 1.7661505e+12 1.7659547e+12
"2" 121 1.7661645e+12 1.7659547e+12
"2" 96 1.766178e+12 1.7659547e+12
"2" 91 1.7663007e+12 1.7659547e+12
"3" 158 1.764788e+12 1.7647606e+12
"4" 102 1.764662e+12 1.7646576e+12
"4" 100 1.7646773e+12 1.7646576e+12
"4" 99 1.7647174e+12 1.7646576e+12
"4" 96 1.7647483e+12 1.7646576e+12
"4" 114 1.764767e+12 1.7646576e+12
"4" 141 1.764784e+12 1.7646576e+12
"4" 142 1.7648353e+12 1.7646576e+12
"4" 127 1.7648528e+12 1.7646576e+12
"4" 158 1.7648665e+12 1.7646576e+12
end

[Clyde Schechter]

The solution depends on what you mean by "day." If it is a period of 24 hours:

// IF DAY MEANS 24 HOURS
local one_day = msofhours(24)
gen int hospital_day = ceil((bg_result_dt-admt_dt)/`one_day')

But if you mean calendar days, it's:

// IF DAY MEANS CALENDAR DAYS
gen int hospital_day = 1 + datediff(dofc(admt_dt), dofc(bg_result_dt), "day")

If the purposes of your study are primarily biological/pharmacological, then you probably would use the 24 hour period as your definition of "day." If your purposes are primarily health-services related, you are more likely to use the calendar day.

Added: By the way, both approaches assume that all bg_result_dt values are later than the corresponding admt_dt value. This is not always true in real world data sets as an admitted patient may have had blood glucose measured in the ED or outpatient setting shortly before being admitted, and those might show up in your data pull. If that does arise in your data and you know how you want to handle them but are unsure of how to modify the code, post back with your explanation.

[Mohammed SA]

Thank you so much, Clyde. Incredibly helpful. I am including the blood glucose measures that were drawn in the hospital ward in this analysis.