Providing software solutions since 1976
Sample 26044: Determine the number of business days between two SAS dates
 |  |  |  |  |  |
Use DATA step functions to help determine the number of business days between two SAS dates. Non-business days would include weekends and U.S. holidays.
Note: This sample illustrates how to determine exact dates for some specific U.S. holidays. Modifications to this sample may be required to meet the needs of your company. For more information about algorithms used to determine holidays and observed holidays, you may want to visit
http://aa.usno.navy.mil/faq/docs/holidays.html
See also Using SAS to determine dates for U.S. holidays.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
/***************************************************************************/
/* This sample creates three data sets, HOLIDAYS, WEEKENDS, and ALLDAYS */
/* which are combined to create CALENDAR. If you must often calculate the */
/* number of business days between dates, you may prefer to make CALENDAR */
/* a permanent data set rather than recreate it each time. */
/* */
/* Two methods are provided below which use CALENDAR and a second data set */
/* with multiple start and end dates to determine the number of business */
/* dates between STARTDT and STOPDT. */
/***************************************************************************/
/* If you choose, you can edit your calendar ranges here. No other edits */
/* are needed to the steps creating the data set CALENDAR. */
%let start='01jan1960'd;
%let stop='01jan2060'd;
/* Create a date set for holidays. Adjust to fit your company's needs. */
/* Note logic is illustrated for 'static' holidays and 'observed' holidays. */
data holidays;
length type $25;
do year=year(&start) to year(&stop);
/* example of 'observed' holiday logic */
type='New Years Day Observed';
dt=MDY(1,1,YEAR);
FDOY=dt;
if weekday(dt)=1 then dt=dt+1;
else if weekday(dt)=7 then dt=mdy(12,31,year-1);
output;
/* example of static holiday logic */
type='Martin Luther King Day';
dt=intnx('week.2',fdoy,(weekday(fdoy) ne 2)+2);
output;
type="Presidents Day";
fdo_feb=intnx('month',fdoy,1);
dt=intnx('week.2',fdo_feb,(weekday(fdo_feb) ne 2)+2);
output;
type='Memorial Day';
fdo_may=intnx('month',fdoy,4);
dt=intnx('week.2',fdo_may,(weekday(fdo_may) in (1,7))+4);
output;
type='Independance Day Observed';
dt=MDY(7,4,YEAR);
if weekday(dt)=1 then dt=dt+1;
else if weekday(dt)=7 then dt=dt-1;
output;
type='Labor Day';
fdo_sep=intnx('month',fdoy,8);
dt=intnx('week.2',fdo_sep,(weekday(fdo_sep) ne 2));
output;
type='Election Day';
fdo_nov=intnx('month',fdoy,10);
dt=intnx('week.3',fdo_nov,1);
output;
type='Veterans Day Observed';
dt=MDY(11,11,YEAR);
if weekday(dt)=1 then dt=dt+1;
else if weekday(dt)=7 then dt=dt-1;
output;
type='Thanksgiving Day';
dt=intnx('week.5',fdo_nov,(weekday(fdo_nov) ne 5)+3);
output;
type='Christmas Day Observed';
dt=MDY(12,25,YEAR);
if weekday(dt)=1 then dt=dt+1;
else if weekday(dt)=7 then dt=dt-1;
output;
end;
keep dt type;
run;
proc sort data=holidays;
by dt;
run;
/* Create a data set of weekends via the WEEKDAY function */
data weekends;
length type $25;
type='Weekend';
do dt=&start to &stop;
if weekday(dt) in (1,7) then output;
end;
run;
/* Create a data set of all the days in the specified date range. */
/* TYPE will have the value 'Workday' for all observations. */
/* Specifying ALLDAYS first in the following MERGE will allow any */
/* date matches from from HOLIDAY or WEEKEND to overwrite the */
/* value of TYPE with the appropriate type of day. */
data alldays;
length type $25;
type='Workday';
do dt=&start to &stop;
output;
end;
run;
data calendar;
format dt date9.;
merge alldays(in=a) weekends(in=w) holidays(in=h);
by dt;
run;
/* Generate dummy data to use with CALENDAR for testing purposes */
data test;
startdt='01nov2006'd; stopdt='30nov2006'd;
output;
startdt='01jan2006'd; stopdt='10jul2006'd;
output;
startdt='20dec2006'd; stopdt='15jan2007'd;
output;
format startdt stopdt date9.;
run;
/* Method 1: SQL */
proc sql;
create table final_sql as
select startdt format=date9.,
stopdt format=date9.,
(select count(*)
from calendar
where dt between stopdt and startdt
and type = 'Workday') as workdays
from test;
quit;
proc print data=final_sql;
title 'Output from PROC SQL';
run;
/* Method 2: DATA step using an INDEX and KEY= */
/* Build index on CALENDAR */
proc datasets library=work nolist;
modify calendar;
index create dt;
quit;
data final_idx;
set test;
workdays=0;
/* For each date between STARTDT and STOPDT, check to see if DT is a workday. */
/* If so, increment the new variable WORKDAYS by 1. */
do i=startdt to stopdt;
dt=i;
/* Look up the current value of DT in CALENDAR using the index on DT */
set calendar key=dt/unique;
/* Check return code from search */
select (_iorc_);
/* Match found */
when (%sysrc(_sok)) do;
if type='Workday' then workdays+1;
if i=stopdt then output;
end;
/* Match not found in master */
when (%sysrc(_dsenom)) do;
_ERROR_=0;
end;
otherwise do;
put 'Unexpected ERROR: _iorc_= ' _iorc_;
stop;
end;
end;
end;
keep startdt stopdt workdays;
run;
proc print data=final_idx;
title "Output from DATA Step";
run;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
Output from PROC SQL Obs startdt stopdt workdays 1 01NOV2006 30NOV2006 19 2 01JAN2006 10JUL2006 131 3 20DEC2006 15JAN2007 16 Output from DATA Step Obs startdt stopdt workdays 1 01NOV2006 30NOV2006 19 2 01JAN2006 10JUL2006 131 3 20DEC2006 15JAN2007 16
Use DATA step functions to help determine the number of business days between two SAS dates.
| Type: | Sample |
| Topic: | SAS Reference ==> DATA Step SAS Reference ==> Functions ==> Date and Time |
| Date Modified: | 2007-04-17 03:02:56 |
| Date Created: | 2006-03-07 09:19:59 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | 6.12 | n/a |
