BookmarkSubscribeRSS Feed
invilink
Calcite | Level 5

Hi community!

 

I'm trying to count the difference between two datetime values. But only the part, which are included in worktime (from 10:00 to 19:00) and workdays (a have a table with days marked as workdays and holidays)

 

Examples

a1 = '10aug2018:15:00:00'dt

b1 = '10aug2018:18:00:00'dt

the result must be 3 hours. Because 10aug2018 is workday and the range [15:00;17:00] is fully in range [10:00;19:00].

 

a2 = '10aug2018:15:00:00'dt

b2 = '10aug2018:20:00:00'dt

the result must be 4 hours. Because 10aug2018 is workday and the range [15:00;20:00] is not fully in ragne [10:00;19:00]. In this case we count time from 15 to 19 – it is 4. 

a3 = '11aug2018:15:00:00'dt

b3 = '11aug2018:18:00:00'dt

the rusult must be 0. Because 11aug2018 is Saturday, and is it not workday.

a4 = '10aug2018:18:30:00'dt

b4 = '13aug2018:11:30:00'dt

the result must be 2 hours.

Because we have:

  • 10aug2018: 30 minutes from 18:30 to 19:00
  • 11aug2018 is holiday: 0
  • 12aug2018 is holiday: 0
  • 13aug2018: 1 hour 30 minutes from 10:00 to 11:30

a5 = '10aug2018:15:00:00'dt

b5 = '14aug2018:14:00:00'dt

the result must be 17 hours

  • 10aug2018: 4 hours from 15:00 to 19:00
  • 11aug2018 is holiday
  • 12aug2018 is holiday
  • 13aug2018: 9 hours (from 10:00 to 19:00)
  • 14aug2018: 4 hours (from 10:00 to 14:00)

 

I tried to solve this case with INTXN function, but I didn't succeed.

Hope for your help.

8 REPLIES 8
novinosrin
Tourmaline | Level 20

Look at INTCK function

invilink
Calcite | Level 5

Tried both.

I tried everything I could before posting.

novinosrin
Tourmaline | Level 20

One demo:

 

data w;
a1 = '10aug2018:15:00:00'dt;

b1 = '10aug2018:18:00:00'dt;
hours=intck('hour',a1,b1);
put hours=;
run;

invilink
Calcite | Level 5

but how can I point that I need only worktime (from 10:00 to 19:00) and workdays?

novinosrin
Tourmaline | Level 20

Can you post a sample of your dataset with 5 -6 records of what you have and and output sample of what you want explaining your requirement. You are most likely to get a better automated solution

hashman
Ammonite | Level 13

@invilink:

 

There're almost infinite number of ways you can specify the interval (the first argument) to the INTNX/CK functions. For 5-day work week starting on Monday, the interval is WEEKDAY5.2. To get work hours, you'll have to use the HOURx.y interval. Dip deeper into the docs to see how the period and subperiod X and Y should be specified for what you need. Here are good places to start digging:

 

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_intervals_sect...

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_intervals_sect...

 

HTH

Paul D.

 

   

PGStats
Opal | Level 21

How are your workdays defined?

PG
FreelanceReinh
Jade | Level 19

Hi @invilink,

 

Mathematically speaking, you want to integrate a step function (taking value 1 during work hours and 0 otherwise) over time intervals. So, you could compute the integrals of this function over the intervals [t0, t] for a fixed point in time t0 (e.g. the earliest datetime you need to consider) and all relevant datetimes t, of course using a reasonable time granularity, e.g. 1 min if you don't have non-zero seconds in your datetime values or 5 min if all datetime values are multiples of 5 min. Let f(t) denote that integral. Then you can compute the desired time differences between datetimes a and b as f(b)−f(a).

 

The table of values of f can be stored in a dataset (or just create a view) and looked up by means of a hash object, as shown below:

%let delta='0:05't; /* time granularity, here: 5 min; adapt as needed */

data holidays; /* extend this as needed or use your existing dataset */
input d yymmdd8.;
cards;
20180704
20181225
;

proc sql noprint;
select d into :holidays separated by ' '
from holidays;
quit;

data integral; /* or ... / view=integral; */
do t='01JAN18:00:00'dt to '01JAN19:00:00'dt by δ /* adapt start and end date as needed */
  d=datepart(t);
  f+(weekday(d) ~in (1,7) & d ~in (&holidays) & '10:00't<timepart(t)<='19:00't)*&delta;
  output;
end;
format t datetime20. f time.;
keep t f;
run;

data have;
input id (a b) (:datetime.);
format a b datetime20.;
cards;
1 10aug2018:15:00:00 10aug2018:18:00:00 
2 10aug2018:15:00:00 10aug2018:20:00:00 
3 11aug2018:15:00:00 11aug2018:18:00:00 
4 10aug2018:18:30:00 13aug2018:11:30:00 
5 10aug2018:15:00:00 14aug2018:14:00:00 
;

data want;
dcl hash h(dataset:'integral');
h.definekey('t');
h.definedata('f');
h.definedone();
call missing(t, f);
do until(last);
  set have end=last;
  diff=ifn(h.find(key: b)=0,f,.)-ifn(h.find(key: a)=0,f,.);
  output;
end;
stop;
format diff time.;
keep id a b diff;
run;

proc print data=want noobs;
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
  • 8 replies
  • 932 views
  • 0 likes
  • 5 in conversation