topic Updating dataset based on values of another dataset in SAS Procedures

Updating dataset based on values of another dataset

ksbarnz — Tue, 05 May 2015 20:49:33 GMT

Hi,

I am new to SAS, so please bear with me, and I am mostly using it for data manipulation. The problem I am trying to solve is updating a variable in a dataset based upon value from another dataset. My datastructure is below:

Dataset1 - Agent_Shift

Variables: Report_Date, Employee_ID, Shift_Code, Avail_Category, Start_moment (timestamp), End_Moment (timestamp)

This data set has the timespans for an agent's Shift, Overtime, and Makeup time. Below, for example Agent1234 is scheduled to work from 01MAR2015:07:00:00 to 01MAR2015:16:30:00 is the schedule span / shift and the same employee has Overtime from 16:30 to 18:00.

Agent_Shift dataset: This dataset would update the Avail_category of Agent_Schedule_Details below

Report_Date	Agent_ID	Shift_Code	Start_Moment	End_Moment	Avail_Category
1-Mar-15	1234	SHIFT	01MAR2015:07:00:00.000000	01MAR2015:16:30:00.000000	Schedule Span
1-Mar-15	1234	OVPH	01MAR2015:16:30:00.000000	01MAR2015:18:00:00.000000	Gross OT

Dataset2 - Agent_Schedule_Details

Variables: Report_Date, Employee_ID, Shift_Code, Avail_Category (currently blank), Start_moment (timestamp), End_Moment (timestamp)

This dataset contains the transactional timestamps of an agents workday. For example agent 1234 was scheduled for production work at 5 different times throughout the day (PRD), had 2 breaks at specific time stamps as well as a lunch.

Agent_Schedule_Details dataset (Avail_Category will need to be updated from Avail Category of Agent_Shift)

Report_Date	Agent_ID	Shift_Code	Start_Moment	End_Moment	Avail_Category
1-Mar-15	1234	CKEY	01MAR2015:07:00:00.000000	01MAR2015:07:08:00.000000	Should be Schedule Span
1-Mar-15	1234	PRD	01MAR2015:07:08:00.000000	01MAR2015:10:30:00.000000	Should be Schedule Span
1-Mar-15	1234	BRK1	01MAR2015:10:30:00.000000	01MAR2015:10:45:00.000000	Should be Schedule Span
1-Mar-15	1234	PRD	01MAR2015:10:45:00.000000	01MAR2015:14:00:00.000000	Should be Schedule Span
1-Mar-15	1234	LUNCH	01MAR2015:14:00:00.000000	01MAR2015:14:30:00.000000	Should be Schedule Span
1-Mar-15	1234	PRD	01MAR2015:14:30:00.000000	01MAR2015:15:37:00.000000	Should be Schedule Span
1-Mar-15	1234	BRKL	01MAR2015:15:37:00.000000	01MAR2015:15:52:00.000000	Should be Schedule Span
1-Mar-15	1234	PRD	01MAR2015:15:52:00.000000	01MAR2015:16:30:00.000000	Should be Schedule Span
1-Mar-15	1234	BRKO	01MAR2015:16:45:00.000000	01MAR2015:17:00:00.000000	Should be Gross OT
1-Mar-15	1234	PRD	01MAR2015:17:00:00.000000	01MAR2015:18:00:00.000000	Should be Gross OT

The issue I am having is how do I update the Avail category based on the Avail_Category and timestamp of the Agent_Shift dataset? I know there should be logic to see if the start and stop timestamps of each observation within Agent_Schedule_Details falls with the time stamp of the observations for the Agents_Shift. I am just struggling with how to do this update.

Re: Updating dataset based on values of another dataset

gergely_batho — Tue, 05 May 2015 21:25:02 GMT

proc sql;

create table want as

select l.*, r.Avail_Category

from Agent_Schedule_Details as l left join Agent_Shift as r on (

l.Agent_ID=r.Agent_ID and l.Start_Moment between r.Start_Moment and r.End_Moment

)

;

quit;

The tricky part is in bold. You should precisely define how to match observations.

What if there is an activity for agent 1234 from 16:25 to 16:35?

Re: Updating dataset based on values of another dataset

PGStats — Wed, 06 May 2015 03:19:17 GMT

If the Detail periods never span more than two consecutive shift categories, then you can fix the transactional data by splitting the spanning observations in two:

option linesize=120;

data agent_shift;

input Report_Date :anydtdte. Agent_ID Shift_Code $

(Start_Moment End_Moment) (:datetime.) Avail_Category & :$20.;

format Report_Date date9. Start_Moment End_Moment datetime15.;

datalines;

1-Mar-15 1234 SHIFT 01MAR2015:07:00:00.000000 01MAR2015:16:30:00.000000 Schedule Span

1-Mar-15 1234 OVPH 01MAR2015:16:30:00.000000 01MAR2015:18:00:00.000000 Gross OT

;

data Agent_Schedule_Details;

input Report_Date :anydtdte. Agent_ID Shift_Code $

(Start_Moment End_Moment) (:datetime.);

format Report_Date date9. Start_Moment End_Moment datetime15.;

datalines;

1-Mar-15 1234 CKEY 01MAR2015:07:00:00.000000 01MAR2015:07:08:00.000000

1-Mar-15 1234 PRD 01MAR2015:07:08:00.000000 01MAR2015:10:30:00.000000

1-Mar-15 1234 BRK1 01MAR2015:10:30:00.000000 01MAR2015:10:45:00.000000

1-Mar-15 1234 PRD 01MAR2015:10:45:00.000000 01MAR2015:14:00:00.000000

1-Mar-15 1234 LUNCH 01MAR2015:14:00:00.000000 01MAR2015:14:30:00.000000

1-Mar-15 1234 PRD 01MAR2015:14:30:00.000000 01MAR2015:15:37:00.000000

1-Mar-15 1234 BRKL 01MAR2015:15:37:00.000000 01MAR2015:15:52:00.000000

1-Mar-15 1234 PRD 01MAR2015:15:52:00.000000 01MAR2015:16:40:00.000000

1-Mar-15 1234 BRKO 01MAR2015:16:45:00.000000 01MAR2015:17:00:00.000000

1-Mar-15 1234 PRD 01MAR2015:17:00:00.000000 01MAR2015:18:00:00.000000

;

proc sql;

create table want as

select

l.Report_Date,

l.Agent_ID,

l.Shift_Code,

l.Start_Moment,

min(l.End_Moment, r.End_Moment) as End_Moment format=datetime15.,

r.Avail_Category

from

Agent_Schedule_Details as l left join

Agent_Shift as r on

l.Agent_ID = r.Agent_ID and

l.Start_Moment >= r.Start_Moment and

l.Start_moment < r.End_Moment

union

select

l.Report_Date,

l.Agent_ID,

l.Shift_Code,

max(l.Start_Moment, r.Start_Moment) as Start_Moment format=datetime15.,

l.End_Moment,

r.Avail_Category

from

Agent_Schedule_Details as l left join

Agent_Shift as r on

l.Agent_ID = r.Agent_ID and

l.End_Moment > r.Start_Moment and

l.End_moment <= r.End_Moment

order by Agent_ID, Start_Moment

;

select * from want;

quit;

Report_Date Agent_ID Shift_Code Start_Moment End_Moment Avail_Category

-----------------------------------------------------------------------------------

01MAR2015 1234 CKEY 01MAR15:07:00 01MAR15:07:08 Schedule Span

01MAR2015 1234 PRD 01MAR15:07:08 01MAR15:10:30 Schedule Span

01MAR2015 1234 BRK1 01MAR15:10:30 01MAR15:10:45 Schedule Span

01MAR2015 1234 PRD 01MAR15:10:45 01MAR15:14:00 Schedule Span

01MAR2015 1234 LUNCH 01MAR15:14:00 01MAR15:14:30 Schedule Span

01MAR2015 1234 PRD 01MAR15:14:30 01MAR15:15:37 Schedule Span

01MAR2015 1234 BRKL 01MAR15:15:37 01MAR15:15:52 Schedule Span

01MAR2015 1234 PRD 01MAR15:15:52 01MAR15:16:30 Schedule Span

01MAR2015 1234 PRD 01MAR15:16:30 01MAR15:16:40 Gross OT

01MAR2015 1234 BRKO 01MAR15:16:45 01MAR15:17:00 Gross OT

01MAR2015 1234 PRD 01MAR15:17:00 01MAR15:18:00 Gross OT

Note: You can't use between as your join condtion because consecutine End and Start shift times are exactly the same (16:30) and thus could both match your Detail Start_Moment.

Re: Updating dataset based on values of another dataset

ksbarnz — Wed, 06 May 2015 15:36:47 GMT

Thanks, PGStats. That is exactly what I was looking for