Calculating the number of working days between two dates

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:

/* 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

21 Comments

  1. Reni
    Posted May 9, 2011 at 3:18 pm | Permalink

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

  2. Alan Churchill
    Posted May 9, 2011 at 5:05 pm | Permalink

    Great article Chris.

  3. Jared
    Posted May 10, 2011 at 11:15 am | Permalink

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

  4. Syed Ibrahim
    Posted May 11, 2011 at 9:48 am | Permalink

    Good one... appreciated

  5. Lindsey Puryear
    Posted June 3, 2011 at 1:10 pm | Permalink

    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
    Posted June 15, 2012 at 6:48 am | Permalink

    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
      Posted June 15, 2012 at 8:58 am | Permalink

      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.

      • Budi C
        Posted June 17, 2012 at 11:35 pm | Permalink

        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.

        • Chris Hemedinger Chris Hemedinger
          Posted June 18, 2012 at 10:55 am | Permalink

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

  7. Patrick
    Posted December 4, 2012 at 5:19 am | Permalink

    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
    Posted December 4, 2012 at 8:22 pm | Permalink

    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
      Posted December 5, 2012 at 9:34 am | Permalink

      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
        Posted December 5, 2012 at 10:56 pm | Permalink

        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
          Posted December 6, 2012 at 10:19 am | Permalink

          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 < 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; 
          

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

  9. Patrick
    Posted December 11, 2012 at 7:22 am | Permalink

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

    • Chris Hemedinger Chris Hemedinger
      Posted December 11, 2012 at 9:26 am | Permalink

      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
        Posted December 13, 2012 at 3:50 am | Permalink

        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
          Posted December 13, 2012 at 9:54 am | Permalink

          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;
          • Patrick
            Posted December 14, 2012 at 7:54 am | Permalink

            Hi Chris,
            Thanks alot!! It's now perfect. Much appreciated

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>