DATA Step, Macro, Functions and more

Caculate actual work time.

Reply
Frequent Contributor
Posts: 87

Caculate actual work time.

[ Edited ]

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

Super User
Posts: 19,817

Re: Caculate actual work time.

Do you have a fixed data structure in terms of records?

 

ie always these records in this order?

 

SHIFT>BREAK1>LUNCH>BREAK2

Frequent Contributor
Posts: 87

Re: Caculate actual work time.

No. This is just an example. There can be more breaks in different order. 

Super User
Posts: 19,817

Re: Caculate actual work time.

Hmm...will the SHIFT always be first?

Super User
Posts: 19,817

Re: Caculate actual work time.

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. 

Frequent Contributor
Posts: 87

Re: Caculate actual work time.

Yes. Shift is first.
Super User
Posts: 19,817

Re: Caculate actual work time.

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;


Frequent Contributor
Posts: 87

Re: Caculate actual work time.

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;

Ask a Question
Discussion stats
  • 7 replies
  • 307 views
  • 0 likes
  • 2 in conversation