How to calculate overlapping dates in stata

[PRITI SINGH]

Hello,
I am working on a patient dataset in which I need to calculate the time spent in each room based on a 24 hr range. We have information on room, in_time- the time checked in the room, out_time- time checkout form that room. We also have information on collection time for a sample collected for disease X. We want to know the amount of time spent in all rooms from 24hrs before collection time until collection time. The goal is to flag the room where a patient spent most time in the 24hrs before sample was collected. For this we need to calculate the times spent in all rooms the patient visited during this time frame. Appreciate your help.

Thank you

Room number	In_time	Out_time	Collection_time	24Hrs_prior_to_collection
Room A	07-august-2022, 04:30	08-august-2022 03:47	09_august-2022 18:02	08_august-2022 18:02
Room B	08-august_2022 03:48	08_august_2022 23:47	09_august-2022 18:02	08_august-2022 18:02
Room C	08_august_2022 23:48	10_august_2022 10:01	09_august-2022 18:02	08_august-2022 18:02

[Andrew Musau]

Assuming that these are time variables recognized by Stata (SIF values), then the time spent in hours is given by the first line of the code below.

gen duration= (Out_time - In_time)/60*60*1000
gsort patient_id -duration
by patient_id: gen wanted= Room_number[1]

For your future posts, use the dataex command to present data examples. Had you done so, we would have known whether your time variables were proper Stata time variables or whether they were strings and additionally, we would see your exact variable names. Review FAQ Advice #12 for details.

[PRITI SINGH]

Thank you Andrew for the help, but this is just calculating the duration spent in each room. I want to know what amount of time from 24hrs before sample collection to sample collection was spent in each of these rooms. Yes these variables are in SIF format. I will shortly post a the same problem using dataex.

[Andrew Musau]

I will shortly post a the same problem using dataex.

As you state that the dataset includes multiple patients, include at least 2 patient_ids.

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 room_number double(in_time out_time collection_time) long pat_id
"Room A" 1.9754658e+12 1975549620000 1975687320000 1
"Room B" 1975549680000 1975621620000 1975687320000 1
"Room C" 1975621680000 1975744860000 1975687320000 1
end
format %tc in_time
format %tc out_time
format %tc collection_time


gen double before_24h = collection_time - msofhours(24)
format before_24h %tc
gen double wanted = min(out_time, collection_time) ///
    - max(in_time, before_24h)
replace wanted = 0 if wanted < 0
format wanted %tcHH:MM:SS

by pat_id (wanted), sort: gen flag = (_n == _N)

The variable wanted will show the time spent in each room within that 24 hour window. The variable flag will be 1 for a room in which the greatest amount of time was spent during that window, 0 elsewhere. Note that if there are two or more rooms that are tied for the longest amount of time, you have not specified how you wish to break that tie. The code above breaks it at random and irreproducibly. Given your purpose, it might make more sense to flag all of the tied room. If that is what you wish to do, change the final line of code to -by pat_id (wanted), sort: gen flag = (wanted == wanted[_N])-.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[PRITI SINGH]

Thank you Dr.Clyde Schechter . That works!