Hi All,
I have below dataset and expected output.
Input data details:
“Id” is the randomly-generated patient identifier.
“Date” and “Time” is the time stamp of the event
“Assigned_pat_loc” is the current patient location in the hospital, formatted as “unit^room^bed”. EIAB is the internal code for the emergency department, with most of the admissions processed through the emergency department.
“Prior_pat_loc” is the location where the patient was at immediately prior to current location
“Activity” is the description of the event. It includes entries like “Admission”, “Transfer to” “Transfer from” “Discharge”, and “Death”. You will notice a lot of duplicate records, where the same transfer is recorded in both the departing and the receiving
Goal is to use the information on dates, times, and locations, to create a file that lists, for each patient, all of the episodes of sharing a hospital room with another patient.
any programming language can be used, the final output should be in the form of a Stata file with accompanying do file.
id | Date | Time | assigned_pat_loc | prior_pat_loc | Activity |
1 | May/31/11 | 8:00 | EIAB^EIAB^6 | | Admission |
1 | May/31/11 | 9:00 | 8w^201 | EIAB^EIAB^6 | Transfer to 8w |
1 | Jun/8/11 | 15:00 | 8w^201 | | Discharge |
2 | May/31/11 | 5:00 | EIAB^EIAB^4 | | Admission |
2 | May/31/11 | 7:00 | 10E^45 | EIAB^EIAB^4 | Transfer to 10E |
2 | Jun/1/11 | 1:00 | 8w^201 | 10E^45 | Transfer to 8w |
2 | Jun/1/11 | 8:00 | 8w^201 | | Discharge |
3 | May/31/11 | 9:00 | EIAB^EIAB^2 | | Admission |
3 | Jun/1/11 | 9:00 | 8w^201 | EIAB^EIAB^2 | Transfer to 8w |
3 | Jun/5/11 | 9:00 | 8w^201 | | Discharge |
4 | May/31/11 | 9:00 | EIAB^EIAB^9 | | Admission |
4 | May/31/11 | 7:00 | 10E^45 | EIAB^EIAB^9 | Transfer to 10E |
4 | Jun/1/11 | 8:00 | 10E^45 | | Death |
The hospital layout is such that most of the rooms are private, but there are some two-patient, three-patient, and four-patient rooms. So, at any point in time, a patient can have a maximum of 3 other patients in the same room with him/her, but most will have 1 or none. If a patient does not have any room-sharing spells, you can omit them from the final data set you produce.
output data details:
- the id of the other patient, “r_id”
- date and time the episode started, “start_date” and “start_time”
- date and time the episode ended, “end_date” and “end_time”
- length of the episode, “length”
- location of the episode, “location”
id | r_id | start_date | start_time | end_date | end_time | length | location |
1 | 2 | Jun/1/11 | 1:00 | Jun/1/11 | 8:00 | 7 | 8w^201 |
1 | 3 | Jun/1/11 | 9:00 | Jun/5/11 | 9:00 | 96 | 8w^201 |
2 | 4 | May/31/11 | 7:00 | Jun/1/11 | 1:00 | 18 | 10E^45 |
2 | 1 | Jun/1/11 | 1:00 | Jun/1/11 | 8:00 | 7 | 8w^201 |
3 | 1 | Jun/1/11 | 9:00 | Jun/5/11 | 9:00 | 96 | 8w^201 |
4 | 2 | May/31/11 | 7:00 | Jun/1/11 | 1:00 | 18 | 10E^45 |
- r_id is the name of the variable you will generate for the id of the other patient.
- patient 1 had two room-sharing episodes, both in 8w^201 (room 201 of unit 8w); he shared the room with patient 2 for 7 hours (1 am to 8 am on June 1) and with patient 3 for 96 hours (9 am on June 1 to 9 am on June 5).
- Patient 2 also had two room sharing episodes. The first one was with patient 4 in in 10E^45 (room 45 of unit 10E) and lasted 18 hours (7 am May 31 to 1 am June 1); the second one is the 7-hour episode with patient 1 in 8w^201.
- Patient 3 had only one room-sharing episode with patient 1 in room 8w^201, lasting 96 hours.
- Patient 4, also, had only one room-sharing episode, with patient 2 in room 10E^45, lasting 18 hours.
- *Note that the room-sharing episodes are listed twice, once for each patient.*
Thanks in advance.
