a week ago - last edited a week ago
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)
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.
a week ago
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
a week ago
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:
a week ago
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)*δ 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;