Blogs

Blogs

Calculating the number of working days between two dates

32
By Topics | Programming Tips

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
;

Example output using this custom function

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

Tags custom functions fcmp networkdays SAS programming SAS tips
Share

About Author

Chris Hemedinger

Director, SAS User Engagement

+Chris Hemedinger is the Director of SAS User Engagement, which includes our SAS Communities and SAS User Groups. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies. 

32 Comments

  1. Reni on

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

  3. Jared on

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

  5. Lindsey Puryear on

    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;

  6. Budi C on

    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?

    • Chris Hemedinger
      Chris Hemedinger on

      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.

  7. Patrick on

    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!

  8. Diane Hindmarsh on

    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?

    • Chris Hemedinger
      Chris Hemedinger on

      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:

      networkdays(start_date, end_date, "","");

      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.

      options cmplib=work.myfuncs;
 data dates(keep=start end span dow);
  length start 8 end 8 dow 8;
  format start date9. end date9. dow weekday2.;
  retain start 0;
  if (_n_=1) then
    start = today()-365;
  do i = 1 to 100;
    start + 1;
    end = start;
    dow=start;
    span = networkdays(start, end, "","");
    output;
  end;
run;
      • Diane Hindmarsh on

        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.

        • Chris Hemedinger
          Chris Hemedinger on

          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.

          if (1 &lt; weekday(start_date)&lt; 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 &lt; weekday(holidays[i])&lt; 7) and
     (start_date &lt;= holidays[i] &lt;= end_date) then
        diff = diff - 1; 
end;

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

  9. Patrick on

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

    • Chris Hemedinger
      Chris Hemedinger on

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

      • Patrick on

        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!

        • Chris Hemedinger
          Chris Hemedinger on

          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.

          data test;
 start_date='10jan2011'd;
 end_date='10feb2011'd;
 diff=intck("DAY", start_date,end_date);
 do day = start_date to end_date by 1;
   /* if Sunday, then diff - 1 */
   if weekday(day) = 1 then
     diff = diff-1;
 end;
run;
  10. Michelle Renick on

    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?

    • Chris Hemedinger
      Chris Hemedinger on

      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.

  11. LD on

    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.

  12. zls_sas on

    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

  13. tom vincent on

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

    • Chris Hemedinger
      Chris Hemedinger on

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

  14. Lakshmi on

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

    TEST =networkdays('20aug2014'd,''22mar2016'd , "work.holidays","ph")-1; 
 0 then holidays -1 else 0 - weekends + case when day_of_week(20aug2014) between 2 to 6 then 1 else 0 -1 as test
left join  (left join with calendar table to get sum of holidays and sum of weekends )
 calendar table (sum(holidays) and sum(weekends) between 20aug2014 and 22mar2016.

    I get 402 as test value .
    Do you have any idea?

    • Chris Hemedinger
      Chris Hemedinger on

      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?

Back to Top