searching for start/end times across multiple observations

[jsmall]

Hi there, hope I can explain this well-

Below is some of my data that shows how patients are moving through a hospital, having one or several encounters with staff, each encounter documented in their record. I'd like to collapse the encounters that occur within 48 hours and call it one 'episode'. People can have up to 200 encounters and multiple episodes that span a 10 year window, so it's not efficient to transpose the data.

For now, I'm starting with the patient's very first encounter at the hospital, and want to identify when that episode is over.  So, patient 300 has 3 encounters, the first 2 occurring within 24 hours, the last one beginning at a much later date, so I don't want to count that one.  Patient 500 has 3 encounters within 24 hours. In each case I want to identify the encounter that is the end of their first episode at the hospital 

Any ideas?  I don't use SQL, so if you have any code for a data step, I'd really appreciate it!!!!!!

Thanks so much!

ID	encounter_num	eval start	eval end
100	1	08JUL10:15:00:00	08JUL10:17:43:08
100	2	08JUL10:17:15:00	09JUL10:18:00:00
100	3	23JUL10:16:30:00	23JUL10:16:46:53
100	4	23JUL10:17:31:53	26JUL10:16:30:00
200	1	18AUG05:04:01:53	18AUG05:04:30:00
300	1	25APR05:08:15:00	25APR05:08:31:53
300	2	25APR05:09:16:53	25APR05:10:46:53
300	3	03JAN11:13:46:53	03JAN11:13:58:08
400	1	23MAR07:19:13:08	24MAR07:12:16:53
400	2	24MAR07:15:00:00	24MAR07:16:30:00
400	3	27MAR07:10:30:00	27MAR07:10:46:53
400	4	13AUG04:09:00:00	13AUG04:13:13:08
500	1	05MAR12:19:30:00	05MAR12:19:46:53
500	2	05MAR12:20:15:00	05MAR12:22:58:08
500	3	06MAR12:00:00:00	06MAR12:04:13:08

[Reeza]

How are you counting 48 hours?

What if it was the following:

T1 Monday Night - first visit

T2 Tuesday Morning - second visit - 26 hours from T1

T3 Wednesday evening - third visit - 56 hours from T1, 30 hours from T2

Would T3 be included in that episode or would it be on its own?

This can be accomplished using PROC SQL - if you search on here with 30 day readmission problem it's the same concept except 48 hours instead of 30 days.

Spoiler

---

@jsmall wrote:

Hi there, hope I can explain this well-

Below is some of my data that shows how patients are moving through a hospital, having one or several encounters with staff, each encounter documented in their record. I'd like to collapse the encounters that occur within 48 hours and call it one 'episode'. People can have up to 200 encounters and multiple episodes that span a 10 year window, so it's not efficient to transpose the data.

For now, I'm starting with the patient's very first encounter at the hospital, and want to identify when that episode is over.  So, patient 300 has 3 encounters, the first 2 occurring within 24 hours, the last one beginning at a much later date, so I don't want to count that one.  Patient 500 has 3 encounters within 24 hours. In each case I want to identify the encounter that is the end of their first episode at the hospital 

Any ideas?  I don't use SQL, so if you have any code for a data step, I'd really appreciate it!!!!!!

Thanks so much!

ID	encounter_num	eval start	eval end
100	1	08JUL10:15:00:00	08JUL10:17:43:08
100	2	08JUL10:17:15:00	09JUL10:18:00:00
100	3	23JUL10:16:30:00	23JUL10:16:46:53
100	4	23JUL10:17:31:53	26JUL10:16:30:00
200	1	18AUG05:04:01:53	18AUG05:04:30:00
300	1	25APR05:08:15:00	25APR05:08:31:53
300	2	25APR05:09:16:53	25APR05:10:46:53
300	3	03JAN11:13:46:53	03JAN11:13:58:08
400	1	23MAR07:19:13:08	24MAR07:12:16:53
400	2	24MAR07:15:00:00	24MAR07:16:30:00
400	3	27MAR07:10:30:00	27MAR07:10:46:53
400	4	13AUG04:09:00:00	13AUG04:13:13:08
500	1	05MAR12:19:30:00	05MAR12:19:46:53
500	2	05MAR12:20:15:00	05MAR12:22:58:08
500	3	06MAR12:00:00:00	06MAR12:04:13:08

---

[jsmall]

Thanks, I'll search for that.

Right now, I'm just ballpark-ing 48 hours, and I'm only interested in time from T1. so evalend time- evalstart time= 48 hours

[novinosrin · Accepted solution]

Please correct my understanding wherever i am wrong to make further changes:

data have;
input ID 	encounter_num 	evalstart :datetime20.	evalend :datetime20.;
format evalstart datetime20.	evalend datetime20.;
datalines;
100	1	08JUL10:15:00:00	08JUL10:17:43:08
100	2	08JUL10:17:15:00	09JUL10:18:00:00
100	3	23JUL10:16:30:00	23JUL10:16:46:53
100	4	23JUL10:17:31:53	26JUL10:16:30:00
200	1	18AUG05:04:01:53	18AUG05:04:30:00
300	1	25APR05:08:15:00	25APR05:08:31:53
300	2	25APR05:09:16:53	25APR05:10:46:53
300	3	03JAN11:13:46:53	03JAN11:13:58:08
400	1	23MAR07:19:13:08	24MAR07:12:16:53
400	2	24MAR07:15:00:00	24MAR07:16:30:00
400	3	27MAR07:10:30:00	27MAR07:10:46:53
400	4	13AUG07:09:00:00	13AUG07:13:13:08
500	1	05MAR12:19:30:00	05MAR12:19:46:53
500	2	05MAR12:20:15:00	05MAR12:22:58:08
500	3	06MAR12:00:00:00	06MAR12:04:13:0
;

data want;
set have;
by id;
retain episode;
_k=lag(evalend);
if first.id then do; _cumhr =0;episode=1;end;
if not first.id then do;_hours=intck('hour',_k,evalend);_cumhr+_hours;end;
if _cumhr>=48 then do;episode+1;_cumhr =0;end;
drop _:;
run;

[jsmall]

Thank you! this works perfectly!! 

[novinosrin]

Lol I didn't even read your details completely before i started coding and that worries me. Well do test as well as you can and feel free to come back to us for any further edit/changes. have a good one!