topic Re: Caculate actual work time. in SAS Programming

Caculate actual work time.

helloSAS — Wed, 10 Feb 2016 01:00:44 GMT

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

Re: Caculate actual work time.

Reeza — Wed, 10 Feb 2016 01:40:23 GMT

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

ie always these records in this order?

SHIFT>BREAK1>LUNCH>BREAK2

Re: Caculate actual work time.

helloSAS — Wed, 10 Feb 2016 01:48:34 GMT

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

Re: Caculate actual work time.

Reeza — Wed, 10 Feb 2016 01:49:34 GMT

Hmm...will the SHIFT always be first?

Re: Caculate actual work time.

Reeza — Wed, 10 Feb 2016 01:51:04 GMT

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.

Re: Caculate actual work time.

helloSAS — Wed, 10 Feb 2016 02:05:17 GMT

Yes. Shift is first.

Re: Caculate actual work time.

Reeza — Wed, 10 Feb 2016 16:56:21 GMT

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;

Re: Caculate actual work time.

helloSAS — Wed, 10 Feb 2016 17:31:22 GMT

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;