Pages

SyntaxHighlighter

Tuesday, October 1, 2013

Last Business Friday of Month

The %lastBusinessFridayOfMonth macro (see below) can be used to produce a SAS data set containing the last business Friday of all months between a user supplied start and stop year. This code leverages the %holidays macro that can be found in one of my previous blog entries here.

I had a request to treat the Friday after Thanksgiving as a holiday. Data step code was added after the %holidays macro call to support this requirement. The holiday dates get loaded into a SAS hash object so that its .check() method can be used to determine if the last Friday of the month is a holiday.

The new in SAS 9.2 nwkdom() function was used to obtain the last Friday of each month. The nwkdom() function much easier to use than the intnx() function that would have been used prior to the release of SAS 9.2. If the last Friday is a holiday, that date is pushed back a week and tested again within the do while( holidays.check() = 0 ) loop.

A good example of a two consecutive Friday holdidays is December 31, 2010. Going back a week from December 31 puts us on December 24 or Christmas day so the last business Friday for December 2010 happens to be Decmber 17.

/**************************************************************************
*     Program: lastBusinessFridayOfMonth.sas
*      Author: Tom Bellmer
*     Created: 01OCT2013  
*     Purpose: determine last business friday of each month taking into 
*              consideration holidays.  If last friday is a holiday go 
*              back a week and try again using a hash object of holidays.
*       Usage: %lastBusinessFridayOfMonth( startyear=2000, stopyear=2100 )
***************************************************************************/

%macro lastBusinessFridayOfMonth( startyear =, stopyear = ) ;
  %holidays
    (   startyear = &startyear.
      , stopyear  = &stopyear.
      , outdsn    = work.holidays
      , view      = n 
    )

  data holidays ;
    set holidays( keep = date ) ;
    if month( date ) = 11 and day( date ) >= 20 then do ;
      date + 1 ; /* move Thanksgiving to a Friday */
      output ;
    end ;
    else output ;
  run ;

  data lastBusinessFridayOfMonth( keep = date ) ;
    if 0 then set work.holidays ;
    dcl hash holidays( dataset: 'work.holidays' ) ;
    holidays.definekey( 'date' ) ;
    holidays.definedone() ;

    do year = &startyear. to &stopyear. ;
      do month = 1 to 12 ;
        date = nwkdom( 5, 6, month, year ) ; *-- get last Friday of month ;
        do while( holidays.check() = 0 ) ;
          date = date - 7 ; *-- holiday found, go back a week ;
        end ;
        output ;
      end ;
    end ;
  run ;

%mend ;

/*EOF: lastBusinessFridayOfMonth.sas */