Process episode data with dates when a by variable change
Process episode data with dates when a by variable change
(OP)
Hi everyone,
I need some help to roll up the following dataset by ID and FAC (facility). As you can see in the output dataset below, I want to keep the first record, then roll-up as to when facility switches. Then stamp the appropriate times to the record, and that is START_TM (start time) of the 2nd record would be equal to the STOP_TM (stop time) of the previous one.
I have been playing with the FIRST.ID and LAST.ID but have a hard time processing it because every time I sorted it, it messed up the order of the time.
Any help would be greatly appreciated! Thanks,
Here is the output I am trying to accomplish:
I need some help to roll up the following dataset by ID and FAC (facility). As you can see in the output dataset below, I want to keep the first record, then roll-up as to when facility switches. Then stamp the appropriate times to the record, and that is START_TM (start time) of the 2nd record would be equal to the STOP_TM (stop time) of the previous one.
I have been playing with the FIRST.ID and LAST.ID but have a hard time processing it because every time I sorted it, it messed up the order of the time.
Any help would be greatly appreciated! Thanks,
CODE
data a;
input id $ fac $ start_dt:mmddyy10. start_tm:time8. stop_tm:time8. ;
format start_dt mmddyy10. start_tm time8. stop_tm time8. ;
cards;
001 mod 08/19/2009 14:42:00 20:19:00
001 man 08/19/2009 20:19:00 23:59:59
001 man 08/20/2009 00:00:00 00:00:00
001 man 08/21/2009 00:00:00 00:00:00
002 fre 07/25/2009 19:19:00 22:18:00
002 fre 07/25/2009 22:18:00 23:59:59
002 fre 07/26/2009 00:00:00 07:18:00
002 hay 07/26/2009 07:18:00 10:26:00
002 fre 07/21/2009 10:26:00 13:30:00
;
input id $ fac $ start_dt:mmddyy10. start_tm:time8. stop_tm:time8. ;
format start_dt mmddyy10. start_tm time8. stop_tm time8. ;
cards;
001 mod 08/19/2009 14:42:00 20:19:00
001 man 08/19/2009 20:19:00 23:59:59
001 man 08/20/2009 00:00:00 00:00:00
001 man 08/21/2009 00:00:00 00:00:00
002 fre 07/25/2009 19:19:00 22:18:00
002 fre 07/25/2009 22:18:00 23:59:59
002 fre 07/26/2009 00:00:00 07:18:00
002 hay 07/26/2009 07:18:00 10:26:00
002 fre 07/21/2009 10:26:00 13:30:00
;
CODE
001 mod 08/19/2009 14:42:00 20:19:00
001 man 08/21/2009 20:19:00 00:00:00
002 fre 07/25/2009 19:19:00 22:18:00
002 hay 07/26/2009 22:18:00 10:26:00
001 man 08/21/2009 20:19:00 00:00:00
002 fre 07/25/2009 19:19:00 22:18:00
002 hay 07/26/2009 22:18:00 10:26:00
Talk To Other Members
RE: Process episode data with dates when a by variable change
CODE
by id fac start_dt start_tm stop_tm;
run;
data b(drop=start_tm start_dt);
set a;
by id fac;
retain start_date start_time;
if first.fac then
do;
start_date = start_dt;
start_time = start_tm;
end;
if last.fac then
do;
stop_date = start_dt;
output;
end;
run;
I retained the start date there as well because in your example output records, the second records start time actually refers to a date 3 days earlier than the date listed.
I hope that this helps.
Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.
RE: Process episode data with dates when a by variable change