×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Process episode data with dates when a by variable change

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,

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



 

RE: Process episode data with dates when a by variable change

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.

RE: Process episode data with dates when a by variable change

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close