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