Process episode data with dates when a by variable change

[khfor2007]

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,

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
;

Here is the output I am trying to accomplish:

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

 

[ChrisW75]

try this.

proc sort data=a;
  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.

 

[khfor2007]

Thank you so much, Chris. As it turned out, the data I had was not quite that way, and I had to use NOTSORTED to resolve it. As always,your help is greatly appreciated!