Calculating the number of working days between two dates

May 9, 2011
By

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.

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 = intkc(‘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:

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 start_date = start_date-1;
       diff = intck(‘WEEKDAY’, 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:

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
;
 

This shows an example of the result:

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

This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post.

Tags: , , , ,

Welcome!

SAS-X.com offers news and tutorials about the various SAS® software packages, contributed by bloggers. You are welcome to subscribe to e-mail updates, or add your SAS-blog to the site.

Sponsors







Dear readers, proc-x is looking for sponsors who would be willing to support the site in exchange for banner ads in the right sidebar of the site. If you are interested, please e-mail me at: tal.galili@gmail.com
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.