DATA Step, Macro, Functions and more

difference between two datetime fileds with condition

Reply
New Contributor
Posts: 3

difference between two datetime fileds with condition

[ Edited ]

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.

Super User
Posts: 2,068

Re: difference between two datetime fileds with condition

Look at INTCK function

New Contributor
Posts: 3

Re: difference between two datetime fileds with condition

Posted in reply to novinosrin

Tried both.

I tried everything I could before posting.

Super User
Posts: 2,068

Re: difference between two datetime fileds with condition

One demo:

 

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

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

New Contributor
Posts: 3

Re: difference between two datetime fileds with condition

Posted in reply to novinosrin

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

Super User
Posts: 2,068

Re: difference between two datetime fileds with condition

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

Frequent Contributor
Posts: 112

Re: difference between two datetime fileds with condition

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

 

   

Esteemed Advisor
Posts: 5,626

Re: difference between two datetime fileds with condition

How are your workdays defined?

PG
Trusted Advisor
Posts: 1,327

Re: difference between two datetime fileds with condition

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;

 

Ask a Question
Discussion stats
  • 8 replies
  • 112 views
  • 0 likes
  • 5 in conversation