topic Re: difference between two datetime fileds with condition in SAS Programming

difference between two datetime fileds with condition

invilink — Sat, 11 Aug 2018 13:48:25 GMT

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.

Re: difference between two datetime fileds with condition

novinosrin — Sat, 11 Aug 2018 14:34:06 GMT

Look at INTCK function

Re: difference between two datetime fileds with condition

invilink — Sat, 11 Aug 2018 14:35:46 GMT

Tried both.

I tried everything I could before posting.

Re: difference between two datetime fileds with condition

novinosrin — Sat, 11 Aug 2018 14:38:57 GMT

One demo:

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

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

Re: difference between two datetime fileds with condition

invilink — Sat, 11 Aug 2018 14:41:35 GMT

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

Re: difference between two datetime fileds with condition

novinosrin — Sat, 11 Aug 2018 14:50:07 GMT

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

Re: difference between two datetime fileds with condition

PGStats — Sat, 11 Aug 2018 21:40:05 GMT

How are your workdays defined?

Re: difference between two datetime fileds with condition

hashman — Sun, 12 Aug 2018 00:03:47 GMT

@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_sect006.htm

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

HTH

Paul D.

Re: difference between two datetime fileds with condition

FreelanceReinh — Sun, 12 Aug 2018 16:41:47 GMT

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 &delta; /* 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;