topic Re: Get differences between 2 datetimes in hours WITHOUT Sundays or holidays in New SAS User

Get differences between 2 datetimes in hours WITHOUT Sundays or holidays

kz_ — Thu, 11 Apr 2024 21:26:44 GMT

I need to calculate the difference between two datetimes in hours, but I need to exclude Sundays and the following holidays:

1. New Year's Day

2. Memorial Day

3. July 4

4. Labor Day

5. Thanksgiving

6. Christmas

For example, using INTCK, I get column 3. I want column 4. I know there is a weekday function, but I don't know how to use it and include Saturdays.

Thanks in advance.

second_time	first_time	intck('dthour', first_time, second_time)	WANT	Why?
04MAR24:12:26:00	01MAR24:11:30:00	73	49	Exclude March 3 (Sunday)
28MAY24:10:54:00	21MAY24:21:15:00	157	109	Exclude May 26 (Sunday) and May 27 (Memorial Day)
01MAR24:09:53:00	01MAR24:09:15:00	0	0 or 1	SAS is rounding down, but up is ok too

Re: Get differences between 2 datetimes in hours WITHOUT Sundays or holidays

Patrick — Fri, 12 Apr 2024 00:45:03 GMT

You have to create a custom interval.

There is sample code in the docu that's already close to what you need: example uses custom intervals in the time function INTCK to omit holidays when counting business days

Re: Get differences between 2 datetimes in hours WITHOUT Sundays or holidays

Ksharp — Fri, 12 Apr 2024 02:58:19 GMT

/*
You need the help of function WEEKDAY() and HOLIDAY().
*/
data have;
infile cards expandtabs truncover;
input (second_time	first_time) (: datetime32.);
format second_time	first_time datetime.;
seconds=0;
do temp=first_time to second_time;
 if weekday(datepart(temp)) ne 1 and
    datepart(temp) ne holiday('NEWYEAR',year(datepart(temp))) and
	datepart(temp) ne holiday('MEMORIAL',year(datepart(temp))) and
    datepart(temp) ne holiday('USINDEPENDENCE',year(datepart(temp))) and
	datepart(temp) ne holiday('LABOR',year(datepart(temp))) and
	datepart(temp) ne holiday('THANKSGIVING',year(datepart(temp))) and
	datepart(temp) ne holiday('CHRISTMAS',year(datepart(temp))) then seconds+1;
end;
want=seconds/'01:00:00't;
drop temp seconds;
cards;
04MAR24:12:26:00	01MAR24:11:30:00	
28MAY24:10:54:00	21MAY24:21:15:00
;

Re: Get differences between 2 datetimes in hours WITHOUT Sundays or holidays

kz_ — Fri, 19 Apr 2024 15:24:56 GMT

This works perfectly. However, for a big data set, it is impossibly slow. Is there are way for the do loop to ititerate through minutes or hours rather than seconds?

Re: Get differences between 2 datetimes in hours WITHOUT Sundays or holidays

ballardw — Fri, 19 Apr 2024 18:53:18 GMT

Since datetimes are numbers of seconds change any increment to a multiple you like:

do temp=first_time to second_time by 60;
 if weekday(datepart(temp)) ne 1 and
    datepart(temp) ne holiday('NEWYEAR',year(datepart(temp))) and
	datepart(temp) ne holiday('MEMORIAL',year(datepart(temp))) and
    datepart(temp) ne holiday('USINDEPENDENCE',year(datepart(temp))) and
	datepart(temp) ne holiday('LABOR',year(datepart(temp))) and
	datepart(temp) ne holiday('THANKSGIVING',year(datepart(temp))) and
	datepart(temp) ne holiday('CHRISTMAS',year(datepart(temp))) then seconds+60;
end;

increments by 60 seconds or one minute

do temp=first_time to second_time by 3600;
 if weekday(datepart(temp)) ne 1 and
    datepart(temp) ne holiday('NEWYEAR',year(datepart(temp))) and
	datepart(temp) ne holiday('MEMORIAL',year(datepart(temp))) and
    datepart(temp) ne holiday('USINDEPENDENCE',year(datepart(temp))) and
	datepart(temp) ne holiday('LABOR',year(datepart(temp))) and
	datepart(temp) ne holiday('THANKSGIVING',year(datepart(temp))) and
	datepart(temp) ne holiday('CHRISTMAS',year(datepart(temp))) then seconds+3600;
end;

increment by 3600 seconds or 1 hour.

24*3600= 24 hours (1 day).