Hi, I provided below "Have" and "Want" data. "Have" is what I have and "Want" is what I want as the end result.
Basically I have data for each ID on a date have shift times and different types of breaks. For example: If an agent works 8:00AM to 5:00PM and took lunch break from 12:00 to 1:00, my result data should show Actual time he worked, like, 8:00 to 11:59 and 1:01 to 5:00 PM.
Have
Date ID CODE Start_time Stop_time
15-Feb 2151 Shift 15FEB2016:08:00:00 15FEB2016:17:00:00
15-Feb 2151 Break1 15FEB2016:10:30:00 15FEB2016:10:45:00
15-Feb 2151 Lunch 15FEB2016:12:00:00 15FEB2016:13:00:00
15-Feb 2151 Break2 15FEB2016:15:15:00 15FEB2016:15:30:00
15-Feb 3000 Shift 15FEB2016:09:00:00 15FEB2016:18:00:00
15-Feb 3000 Break1 15FEB2016:10:30:00 15FEB2016:10:45:00
15-Feb 3000 Lunch 15FEB2016:12:00:00 15FEB2016:13:00:00
15-Feb 3000 Break2 15FEB2016:15:15:00 15FEB2016:15:30:00
Want
Date ID CODE Start_time Stop_time Shift_Begin Shift_End
15-Feb 2151 Shift 15FEB2016:08:00:00 15FEB2016:10:29:29 15FEB2016:08:00:00 15FEB2016:17:00:00
15-Feb 2151 Shift 15FEB2016:10:45:00 15FEB2016:11:59:59 15FEB2016:08:00:00 15FEB2016:17:00:00
15-Feb 2151 Shift 15FEB2016:13:00:00 15FEB2016:15:14:59 15FEB2016:08:00:00 15FEB2016:17:00:00
15-Feb 2151 Shift 15FEB2016:15:30:00 15FEB2016:17:00:00 15FEB2016:08:00:00 15FEB2016:17:00:00
15-Feb 3000 Shift 15FEB2016:09:00:00 15FEB2016:10:29:29 15FEB2016:09:00:00 15FEB2016:18:00:00
15-Feb 3000 Shift 15FEB2016:10:45:00 15FEB2016:11:59:59 15FEB2016:09:00:00 15FEB2016:18:00:00
15-Feb 3000 Shift 15FEB2016:13:00:00 15FEB2016:15:14:59 15FEB2016:09:00:00 15FEB2016:18:00:00
15-Feb 3000 Shift 15FEB2016:15:30:00 15FEB2016:18:00:00 15FEB2016:09:00:00 15FEB2016:18:00:00
Do you have a fixed data structure in terms of records?
ie always these records in this order?
SHIFT>BREAK1>LUNCH>BREAK2
No. This is just an example. There can be more breaks in different order.
Hmm...will the SHIFT always be first?
Also, what are you trying to do overall...there may be other/different ways. I'd do a SQL self join with the records after identifying SHIFT groups.
Assuming you have the events ordered this should help you get started. Since you said your data doesn't always follow this format you'll likely have some modifications to account for things that this may not.
It's not sleek or efficient, more like brute force....
data have;
informat date yymmn6. start_time stop_time datetime21.;
format date yymon6. start_time stop_time datetime21.;
input Date ID CODE $ Start_time Stop_time ;
cards;
201502 2151 Shift 15FEB2016:08:00:00 15FEB2016:17:00:00
201502 2151 Break1 15FEB2016:10:30:00 15FEB2016:10:45:00
201502 2151 Lunch 15FEB2016:12:00:00 15FEB2016:13:00:00
201502 2151 Break2 15FEB2016:15:15:00 15FEB2016:15:30:00
201502 3000 Shift 15FEB2016:09:00:00 15FEB2016:18:00:00
201502 3000 Break1 15FEB2016:10:30:00 15FEB2016:10:45:00
201502 3000 Lunch 15FEB2016:12:00:00 15FEB2016:13:00:00
201502 3000 Break2 15FEB2016:15:15:00 15FEB2016:15:30:00
;
run;
data want;
set have;
by id;
length Event $8.;
retain shift_start shift_end Shift 0;
if first.id or Code='Shift' then do;
shift_start=start_Time;
shift_end=stop_time;
Shift+1;
output;
end;
else do;
Event="Stop";
Time=start_Time;;
output;
Event="Start";
Time=stop_Time;
output;
end;
format shift_start shift_end time datetime21.;
drop start_Time stop_time;
run;
data want;
set want;
by id shift;
lag_T = lag1(time);
if lag_T = . then lag_T=shift_start;
if event="Stop" then do;
work_start=lag_t;
work_end=time-1;
output;
end;
if last.shift then do;
work_start=time;
work_end=shift_end;
output;
end;
format work_start work_end lag_T datetime21.;
run;
proc print;
var date id event time shift_start shift_end lag_T shift work_start work_End;
run;
Thank you for sharing the logic!
I did the below ang got the results I wanted
data shift1; set shift;
format start_time1 stop_time1 time8.;
start_time1=timepart(start_time);
stop_time1=timepart(stop_time);
drop start_time stop_time;
run;
proc sort data=shift1; by date id; run;
data shift2;
set shift1;
by date id;
if first.id then output shift2;
run;
proc sql;
create table shift3 as
select a.*,b.start_time1 as shift_begin, b.stop_time1 as shift_end
from shift1 a left join shift2 b
on a.id=b.id and a.date=b.date;
quit;
proc sort data=shift3; by date id start_time1 stop_time1; run;
data shift4;
recno=_n_+1;
set shift3 end=last;
by id;
if not last.id
then set shift3 (keep=start_time1 rename=(start_time1=next_stop_time1)) point=recno;
else next_stop_time1=shift_end;
run;
data shift5(drop=stop_time1 start_time1 rename=(next_start_time1=start_time next_stop_time1=stop_time));
set shift4;
format next_start_time1 time8.;
by id;
if first.id
then next_start_time1=shift_begin;
else next_start_time1=stop_time1;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.