hospital room sharing problem

[sasuser_221]

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.

[japelin]

What does the current ID represent?
What does the output id represent? And how is the r_id numbered?
If you don't know the key variables, you won't know the start and end.

[andreas_lds]

In addition to the questions/comments by @japelin: please post data in usable form and for at least one id describe the processing.