Obsidian | Level 7

## macro to exclude week end days and company holidays from the count of days

good day,

I need to calculate number of work days between 2 given dates (start and the end). I need to do a couple things

1) I want to exclude weekends (sat and sunday) from my count

2) and I want to exclude company holidays (6 days) from my count

as an example, if the start-date is May 1 2017 and the end-date is may may 19 2017. Assuming that May 4 and May 5 are company holidays, then I want the macro to return a count of 13... here is how it should count

M    T   W    T     F   S   S

1     2    3     4    5    6   7

8     9   10   11  12  13 14

15  16  17   18  19

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: macro to exclude week end days and company holidays from the count of days

Try the following:

```data wrkdays (keep=begin);
format begin date9.;
array holidays(6);
do date = '01jan1971'd to '31dec2070'd ;
if date eq intnx('year',date,0,'b') then do;
call missing(of holidays(*));
i=0;
end;
if date eq holiday('NEWYEAR', year(date)) or
date eq holiday('USINDEPENDENCE',year(date)) or
date eq holiday('THANKSGIVING', year(date)) or
date eq holiday('CHRISTMAS', year(date)) or
date eq holiday('MEMORIAL', year(date)) or
date eq holiday('LABOR', year(date)) then do;
i+1;
holidays(i)=date;
end;
if not(date in holidays or weekday(date) in (1,7)) then do;
season=1;
begin=date;
output;
end;
end;
run;

options intervalds=(workdays=wrkdays);

data test;
days=intck('workdays','1may2017'd,'19may2017'd);
run;
```

Art, CEO, AnalystFinder.com

15 REPLIES 15
Super User

## Re: macro to exclude week end days and company holidays from the count of days

I would recommend building a custom time interval instead.

Even if you don't have SAS/ETS this is still valid for INTNX(). See the latter example which uses INTCK (not INTNX).

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

Obsidian | Level 7

## Re: macro to exclude week end days and company holidays from the count of days

Hi,

I've tried a few things and nothing worked so far. here is what I am trying to do

current query

proc sql;

select (date_end-date_start) as Calender_days

from

Table_A;

this query return calander days between start and the end date

I want the same query to now return only the work days minus Holidays (selected ones)... here is the script that I found to identify the holidays but I am unable to figure out the logic

data holidays ;
length HolidayName \$ 30 ;
array WkDayShift [7] _temporary_ ( 1 5*0 -1 ) ;
retain ShiftToggle /* 0 */ 1 ;
do Year = 1971 to 2070 ;

HolidayName = "New Year's Day" ;
* 1 Jan if not Mon 2 Jan. ;
HoliDate = holiday('newyear', year) ;
dow = weekday(HoliDate) ;
HoliDate = intnx( 'day', HoliDate, WkDayShift[dow] * ShiftToggle ) ;
if not ( (dow EQ 7) and ShiftToggle ) then output ;

HolidayName = "Martin Luther King Day" ;
* 3rd Mon in Jan ;
HoliDate = holiday('mlk', year) ;
output ;

HolidayName = "Presidents' Day" ;
* 3rd Mon in Feb ;
HoliDate = holiday('uspresidents', year) ;
output ;

HolidayName = "Memorial Day" ;
* Last Mon in May ;
HoliDate = holiday('memorial', year) ;
output ;

HolidayName = "Independence Day" ;
* 4 Jul if not Mon 5 Jul or Fri 3 Jul ;
HoliDate = holiday('usindependence',year) ;
HoliDate = intnx('day',
HoliDate,
WkDayShift[ weekday(HoliDate) ] * ShiftToggle) ;
output ;

HolidayName = "Labor Day" ;
* 1st Mon in Sep ;
HoliDate = holiday('labor', year) ;
output ;

HolidayName = "Thanksgiving Day" ;
*4th Thu in Nov ;
HoliDate = holiday('thanksgiving', year) ;
output ;

HolidayName = "Christmas" ;
* 25 Dec if not Mon 26 Dec or Fri 24 Dec ;
HoliDate = holiday('christmas', year) ;
HoliDate = intnx('day',
HoliDate,
WkDayShift[ weekday(HoliDate) ] * ShiftToggle) ;
output ;

HolidayName = "New Year's Eve" ;
* Fri 31 Dec or not at all ;
HoliDate = mdy(12, 31, year) ;
if weekday(HoliDate) EQ 6 and ShiftToggle then output ;

end ;

keep year HolidayName HoliDate ;
format HoliDate weekdate17. ;
run ;

proc print data=holidays ;
where year in (2017, 2018) ;
by year ;
id year ;
run ;

Super User

## Re: macro to exclude week end days and company holidays from the count of days

Are you in the US? Which Holidays are you factoring in?

Obsidian | Level 7

## Re: macro to exclude week end days and company holidays from the count of days

yes, I am in US and want to factor in following hollidys

• Memorial Day
• Independence Day
• Labor Day
• Thanksgiving Day
• Christmas  Day
• New Year's Day

Thanks

Super User

## Re: macro to exclude week end days and company holidays from the count of days

You created the holidays dataset but it doesn't show you trying to use it anywhere. Am I missing something here?

Obsidian | Level 7

## Re: macro to exclude week end days and company holidays from the count of days

here is the code that I've used

%let date = feb2017;
%let date1 = '01feb2017'd;
%let date2 = '01mar2017'd;

options intervalds=(WorkDays=WorkDayDS);

data WorkDayDS(keep=BEGIN);
start = '15DEC1998'D;
stop = '15JAN2030'D;
nwkdays = INTCK('WEEKDAY',start_Date,End_date);
do i = 0 to nwkdays;
BEGIN = INTNX('WEEKDAY',start_Date,i);
year = YEAR(BEGIN);
if BEGIN ne HOLIDAY("NEWYEAR",year) and
BEGIN ne HOLIDAY("MEMORIAL",year) and
BEGIN ne HOLIDAY("USINDEPENDENCE",year) and
BEGIN ne HOLIDAY("LABOR",year) and
BEGIN ne HOLIDAY("THANKSGIVING",year) and
BEGIN ne HOLIDAY("CHRISTMAS",year) then
output;
end;
format BEGIN DATE.;
run;

proc sql;
create table temp2
as
select *,
INTCK('WorkDays',Start_Date,End_date)/(60*60*24) as workdays
from DB.v_all_2017
where &date1 <= sched_dt < &date2;

-------------------------------

Issue 1: the output has 0 values for all the columns

Issue 2: if the holiday is on the weekend, then it's not switching... my other code that I've posted previously was switching the days perfectly but I don't know how to use it in the code above

Thanks

Super User

## Re: macro to exclude week end days and company holidays from the count of days

@art297 solution will not deal with #2, but to be honest, you should be able to modify the code. I generally don't recommend running code if you don't understand it.

I'm assuming you're referring to day in-lieu days - if XMAS is Sunday then Monday would be a day off as well. The idea seems clear - create a dataset that includes all the dates that would be working dates and then use that as Intervalds option.  The variable must be named BEGIN and it needs to be the only variable in the dataset.

@tparvaiz Why did you divide the number of days by (60/60/24) in your SQL query? It returns the number of days.

Obsidian | Level 7

Opal | Level 21

## Re: macro to exclude week end days and company holidays from the count of days

If your company holidays are always on weekdays, then you could get away with something as simple as:

```data _null_;
count=intck('weekdays','01may2017'd,'19may2017'd)-1;
put count;
run;
```

Art, CEO, AnalystFinder.com

Lapis Lazuli | Level 10

## Re: macro to exclude week end days and company holidays from the count of days

check out this paper on making a report of activity by weeks

Pretty Dates All in a Row

Dianne Rhodes

www2.sas.com/proceedings/sugi31/015-31.pdf
Opal | Level 21

## Re: macro to exclude week end days and company holidays from the count of days

Try the following:

```data wrkdays (keep=begin);
format begin date9.;
array holidays(6);
do date = '01jan1971'd to '31dec2070'd ;
if date eq intnx('year',date,0,'b') then do;
call missing(of holidays(*));
i=0;
end;
if date eq holiday('NEWYEAR', year(date)) or
date eq holiday('USINDEPENDENCE',year(date)) or
date eq holiday('THANKSGIVING', year(date)) or
date eq holiday('CHRISTMAS', year(date)) or
date eq holiday('MEMORIAL', year(date)) or
date eq holiday('LABOR', year(date)) then do;
i+1;
holidays(i)=date;
end;
if not(date in holidays or weekday(date) in (1,7)) then do;
season=1;
begin=date;
output;
end;
end;
run;

options intervalds=(workdays=wrkdays);

data test;
days=intck('workdays','1may2017'd,'19may2017'd);
run;
```

Art, CEO, AnalystFinder.com

Obsidian | Level 7

## Re: macro to exclude week end days and company holidays from the count of days

here is what I did... ran a code similar to the one below

```data wrkdays (keep=begin);
format begin date9.;
array holidays(6);
do date = '01jan1971'd to '31dec2070'd ;
if date eq intnx('year',date,0,'b') then do;
call missing(of holidays(*));
i=0;
end;
if date eq holiday('NEWYEAR', year(date)) or
date eq holiday('USINDEPENDENCE',year(date)) or
date eq holiday('THANKSGIVING', year(date)) or
date eq holiday('CHRISTMAS', year(date)) or
date eq holiday('MEMORIAL', year(date)) or
date eq holiday('LABOR', year(date)) then do;
i+1;
holidays(i)=date;
end;
if not(date in holidays or weekday(date) in (1,7)) then do;
season=1;
begin=date;
output;
end;
end;
run;

options intervalds=(workdays=wrkdays);

data test;  set DB.v_all_2017 (where = (&date1 <= sched_dt < &date2));  days =intck('workdays','1may2017'd,'19may2017'd);  workdays = intck('workdays', start_date, end_date);
run;```

-----issue ----------------------------------------------

the count of days are coming just fine but the workdays field is showing 0 values for all the fields

----Output ---------------------------------------------

my table looks something like this (with workdays showing 0 values)... it works when I pass a fixed date value

 Table: v_all_2017 start_date end_date workdays days 24JAN2017:09:45:00.000 08FEB2017:15:30:00.000 0 14 28JAN2017:00:35:00.000 08FEB2017:17:30:00.000 0 14 03FEB2017:08:49:10.940 07FEB2017:14:35:00.000 0 14

Super User

## Re: macro to exclude week end days and company holidays from the count of days

Your variables are DATETIME not DATE variables.

Use DATEPART() to convert them to DATE type to pass to the INTCK function.

Obsidian | Level 7

## Re: macro to exclude week end days and company holidays from the count of days

it worked with following change

workdays = intck('workdays', datepart(start_date), datepart(end_date));
Discussion stats
• 15 replies
• 4998 views
• 9 likes
• 4 in conversation