BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7

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

7 REPLIES 7
Reeza
Super User

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

 

ie always these records in this order?

 

SHIFT>BREAK1>LUNCH>BREAK2

helloSAS
Obsidian | Level 7

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

Reeza
Super User

Hmm...will the SHIFT always be first?

Reeza
Super User

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. 

helloSAS
Obsidian | Level 7
Yes. Shift is first.
Reeza
Super User

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;


helloSAS
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1108 views
  • 0 likes
  • 2 in conversation