Process episode data with dates when a by variable change

khfor2007 · Feb 5, 2010

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,

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
;

Here is the output I am trying to accomplish:

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

ChrisW75 · Feb 9, 2010

try this.

Code:

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 · Feb 10, 2010

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!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Process episode data with dates when a by variable change

khfor2007

IS-IT--Management

ChrisW75

Programmer

khfor2007

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor