It's a simple task to use SAS to compute the number of weekdays between two dates. You can use the INTCK function with the WEEKDAY interval to come up with that number.

diff = intck('WEEKDAY', start_date, end_date); |

If you want to compute the number of *working days* between two dates, and assuming that every weekday is also a workday, this is perfect. However, most cultures observe certain non-productive days that they call "holidays", and this use of the INTCK function does not consider these when discounting the total sum of days.

Microsoft Excel supplies a function called NETWORKDAYS that can count the weekdays and consider a range of holiday dates when computing the total. This type of function is useful for project planners when they want to tick down the number of remaining days available to complete some work task. While there is no direct analogy to that function in SAS, it's not difficult to write your own functions by using the FCMP procedure. Here is an example of this function:

/* * Mimic the NETWORKDAYS example here: * https://office.microsoft.com/en-us/excel-help/networkdays-HP005209190.aspx */ proc fcmp outlib=work.myfuncs.dates; function networkdays(d1,d2,holidayDataset $,dateColumn $); /* make sure the start date < end date */ start_date = min(d1,d2); end_date = max(d1,d2); /* read holiday data into array */ /* array will resize as necessary */ array holidays[1] / nosymbols; if (not missing(holidayDataset) and exist(holidayDataset)) then rc = read_array(holidayDataset, holidays, dateColumn); else put "NOTE: networkdays(): No Holiday data considered"; /* INTCK computes transitions from one day to the next */ /* To include the start date, if it is a weekday, then */ /* make the start date one day earlier. */ if (1 < weekday(start_date)< 7) then calc_start_date = start_date-1; else calc_start_date = start_date; diff = intck('WEEKDAY', calc_start_date, end_date); do i = 1 to dim(holidays); if (1 < weekday(holidays[i])< 7) and (start_date <= holidays[i] <= end_date) then diff = diff - 1; end; return(diff); endsub; run; quit; |

This function can read a range of holiday dates from a data set. Assuming that you have a data set named USHOLIDAYS with a date column named HOLIDAYDATE, you could use the function like this:

This shows an example of the result:

/* set search path for custom functions */ options cmplib=work.myfuncs; /* test with data set of values */ data test; length dates 8; format dates date9.; infile datalines dsd; input dates : date9.; workdaysSince = networkdays(dates, today(), "work.usholidays","holidaydate"); datalines; 01NOV2010 21NOV2010 01DEC2010 01APR2011 ; |

If you want to see the complete example with sample holiday data, I've placed it on the support site here. (Special thanks to my colleague Jason, whose paper I referred to, for his help in refining this example.)

## 32 Comments

Excellent! Thanks for the how-to on the solution for Excel's "networkdays", and on doing custom functions. (Thanks Jason and Chris).

Great article Chris.

I use "intkc" in a weekly report to calculate unit sales needed before a deadline. It's a great function.

Good one... appreciated

Here is an alternative suggested by Radhika Kulkarni, and implemented by Liping Cai, Lindsey Puryear and Chuck Kelly. Proc CPM does require SAS/OR.

data usholidays;

length holidaydate 8;

format holidaydate date9.;

infile datalines dsd;

input holidaydate : date9.;

datalines;

30MAY2011

04JUL2011

05SEP2011

24NOV2011

25NOV2011

26DEC2011

27DEC2011

28DEC2011

29DEC2011

30DEC2011

;

run;

%macro cc;

data act;

activity='a';

successor='';

duration=0;

a_start=&d1;

a_finish=&d2;

run;

proc cpm data=act holidata=%sysfunc(dequote(&holidayDataset)) date=&d1 out=out interval=weekday;

holiday %sysfunc(dequote(&dateColumn));

activity activity;

successor successor;

duration duration;

actual / a_start=a_start a_finish=a_finish;

run;

proc sql noprint;

select a_dur into :adur from out;

quit;

%let p=&adur;

%mend cc;

proc fcmp outlib=work.myfuncs.dates;

function networkdays(d1,d2,holidayDataset $,dateColumn $);

rc=run_macro('cc', d1,d2,holidayDataset,dateColumn,p);

return (p);

endsub;

run;

options cmplib=work.myfuncs;

/* test with data set of values */

data test;

length dates 8;

format dates date9.;

infile datalines dsd;

input dates : date9.;

workdaysSince=networkdays(dates, today(), "work.usholidays","holidaydate");

datalines;

01JAN2011

01APR2011

01MAY2011

01JUN2011

;

run;

I use SAS 9.1.3 and when I run the function you gave, the result won't come out. And the log gave me this error: "ERROR: Cannot find a library containing subroutine READ_ARRAY."

Is there any READ_ARRAY function in SAS 9.1.3 version? if not, is there any another way so I can run your function?

Budi, PROC FCMP received lots of enhancements in SAS 9.2, and READ_ARRAY might have been among them. If you're comfortable with DATA step, you could re-implement similar logic using hash tables in SAS 9.1.3. It wouldn't be as elegant as the function syntax, but it would get the job done.

Chris, can you help me to re-write the function using hash object/tables? Because I'm not familiar with hash object/tables. *Newbie in SAS here*

Thanks in advance.

This is a great question for the forums at communities.sas.com. Try posting in the DATA step forum for a solution...

Thanks for the suggestion Chris, think I've found the solution using hash object in this link https://communities.sas.com/message/100119#100119.

Hi Budi C.

Would you please share how you used the hash functions. I read the article in the link you send, but l did not understand it. Perhaps if you can paste a code, that would be helpful. Thanks in advance!

Patrick, check out this free "SAS Talk" from Michelle Burlew on the Hash object. It should be enough to get you started.

I have used your code, but I am finding an anomaly whichI don't understand. Of 200 records where the start and finish dates are exactly the same, 23 are coded as having 0 workdays and the remainder coded as having 1 workday. 9 of the 23 occur on the weekend, so they should be 0 workdays, but I cannot see why these other 16 are coded as 0 and the remainder 1. The original data included datetime variables, but I used datepart(var) to extract the date part to allow the code to work. Any ideas?

Diane,

Are you including a Holiday data set as well? Perhaps it has to do with how the holidays fall. Try changing the function use to:

And see how that works. You should get 0 for all weekend days, and 1 for any weekdays.

Here's the test I ran. When the DOW var (day of week) is 7 or 1, the span is 0. Otherwise it's a weekday, so the span is 1.

Yes that works, but if there is a holiday on the Monday (for instance) in the holiday dataset and start and end dates are on the Tuesday, which is a working day, the code also gives zero.

I see the problem! The current logic decrements the "start_date" value when it falls on a weekday to ensure that the start_date is counted. But if the previous day is a holiday, the logic also decrements the difference value. So we end up with this special "off by 1" condition.

To fix it quickly, I added a "holding value" for the start_date to use in calculations, leaving the original start_date untouched for use in the holiday checking.

Will update the original program in the post to reflect this. Thanks for the feedback!

Saturday can also be a working day. How can one modify the above function to include satudays as working days?

Patrick,

Saturday is day "7" in the function logic, so simply change the checks for whether the day is a weekday from "1 < day < 7" to "day > 1" (where "1" means Sunday).

Thanks Chris,

I tried that but it won't give me the correct days, for instance:

data test;

start_date='10jan2011'd;

end_date='10feb2011'd;

if weekday(start_date)>1 then calc_start_date=start_date-1;

diff=intck("WEEKDAY", calc_start_date,end_date);

run;

The correct answer is 27 but l get 24. Basically, l am calculating the number of days from the 10th of each month to the 10 of the next month, where weekdays plus saturdays are considered as business days.

Many thanks!

Patrick,

I think I understand now. Perhaps it is better to use the DAY method for INTCK instead, and then go back and subtract all of the SUNDAY occurrences.

Hi Chris,

Thanks alot!! It's now perfect. Much appreciated

I need to calculate business hours. The data is in DATE20 format. I need the limit the business hours to be between 8am and 5pm, exclude weekends and holidays. So I have a date/time of "09MAY2014;18:35:00" and I need that to be represented as count as a start time as of "12MAY2014;08:00:00". So basically any time that is not between the hours of 08:00 and 17:00 I need to move to the next business day. Has anyone written that in SAS?

Michelle, so you're just trying to "round up" to the next business day, if the timestamp falls outside of the business hours? Yes, I'm sure that someone has done that -- there isn't much that hasn't been done, by someone! Still, this would be a good question to pose in the SAS Language/DATA Step communities forum. If you post a sample of what you have (for data, several records) and what you want, I'm sure that you'll get several helpful responses.

I use SAS 9.2TS2M3 - the function returns ERROR 68-185: The function NETWORKDAYS is unknown, or cannot be accessed.

even when using your test data. Do I need to make a system setting change or am I missing something.

Did you try running the complete example that I provided? Let me know if the complete example does not work.

Hi Chris, I have used your code and it works!!! such a great piece of work.

However,I have some difficulties to apply it for my work. I need to calculate the working days between two days, which requires to exclude the weekends and holidays.

First, different provinces have different holidays. Could you add one more parameter "province" in the format function?

Second, if one of the holidays falls in the weekend, the consecutive weekday will not be the working day. How to exclude those weekdays?

Thank you very much for your help

The options cmplib=work.myfuncs; requirement really should be mentioned at the top of the article.

Good catch Tom. It's in my complete code example, but omitted in the snippets within the article. I've added it.

Hi Chris,

I have one query related to above code exactly , I am trying to replicate the same holiday excluding in SQL.

I get 402 as test value .

Do you have any idea?

Hi, I'm not sure I follow all of your code here. You want to create a table that shows which days on the calendar are weekends or holidays? Or just count how many workdays/nonworkdays there are in a range of dates?

Clearly, SAS has made this too hard.

The INTCK and INTNX functions are extremely powerful when it comes to calculating time spans -- most other languages don't have analogies to these. This post was mostly about how you can extend the SAS language by adding your own functions.