turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- difference between two datetime fileds with condit...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

a week ago - last edited a week ago

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'dtb1 = '10aug2018:

18:00:00'dtthe 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'dtb2 = '10aug2018:

20:00:00'dtthe 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'dtb3 = '11aug2018:

18:00:00'dtthe rusult must be 0. Because 11aug2018 is Saturday, and is it not workday.

a4 = '10aug2018:

18:30:00'dtb4 = '13aug2018:

11:30:00'dtthe result must be 2 hours.

Because we have:

- 10aug2018:
30 minutesfrom 18:30 to 19:00- 11aug2018 is holiday: 0
- 12aug2018 is holiday: 0
- 13aug2018:
1 hour 30 minutesfrom 10:00 to 11:30

a5 = '

10aug2018:15:00:00'dtb5 = '

14aug2018:14:00:00'dtthe result must be 17 hours

- 10aug2018:
4 hoursfrom 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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to invilink

a week ago

Look at INTCK function

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

a week ago

Tried both.

I tried everything I could before posting.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to invilink

a week ago

One demo:

data w;

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

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

hours=intck('hour',a1,b1);

put hours=;

run;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to novinosrin

a week ago

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to invilink

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to invilink

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:

HTH

Paul D.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to invilink

a week ago

How are your workdays defined?

PG

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to invilink

a week ago

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)*δ
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;
```