First Friday of a full week

I had a recent request to determine the first Friday of the first full week of the month. The NWKDOM() function makes this very easy to accomplish since it returns the nth occurance of a weekday for the supplied month and year.

NWKDOM(n, weekday, month, year)

• n - numeric week of the month
• weekday - day of the week with 1 = Sunday
• month - numeric month value
• year - numeric year value

data _null_ ;
  mo   = 1 ;
  year = 2014 ;
  if nwkdom( 1, 1, mo, year ) > nwkdom( 1, 6, mo, year ) 
    then firstfullfriday = nwkdom( 2, 6, mo, year ) ;
  else firstfullfriday = nwkdom( 1, 6, mo, year ) ;
  put firstfullfriday worddate. ;
run ;

January 10, 2014

Validate SAS

Sometimes the exact same code on the same OS but on a different machine will not produced the same result, or more likely will produce an error on one machine but not another. If that happens, consider using the SASIQ.EXE utility to verify that SAS is properly installed.

The below macro assumes the sastest folder is below the install folder (!SASROOT). When you run the macro a new subfolder named validate_yyyymmdd.000 will be created. In my case, a 349 page PDF file named sasiq.pdf was created. The first page (see below) is a quick summary followed by details showing expected vs actual checksum values for critical SAS files.

%macro validatesas( path = c:\temp\ ) ;
  systask command "'%sysget(sasroot)\sastest\sasiq.exe' -outputpath &path." wait ;
%mend ;

For more information, see this link: http://support.sas.com/rnd/migration/planning/validation/sasiq.html

Macro IN Operator

Starting in SAS 9.2 you can use the macro IN operator via the MINOPERATOR option and associated MINDELIMITER option. The MINOPERATOR is OFF (NOMINDELIMITER) by default and can be used as a global option (e.g. options minoperator ; ) or the more flexible and preferred macro option (after the slash [ / ] on the macro definition) as shown in the below example. This is especially critical for stored and compiled macros as the options are reflected at compilation time.

The default delimiter is a BLANK so if you want to a comma, specify it using the MINDELIMITER= option.

%macro test( state ) / minoperator mindelimiter=',' ;
  %if %upcase( &state. ) in( CO, KS, MO, IL ) %then %do ;
    %put its in there ;
  %end ;
  %else %put not found ;
%mend ;

%test( KS )
%test( NY )

119  %test( KS )
its in there
120  %test( NY )
not found

If you want to use this as a negative version [ NOT IN() ] you need to place the NOT around the entire expression, not just next to the IN operator as follows:

%macro test( state ) / minoperator mindelimiter=',' ;
  %if not( %upcase( &state. ) in( CO, KS, MO, IL ) ) %then %do ;
    %put its in there ;
  %end ;
  %else %put not found ;
%mend ;

%test( ks )
%test( ny )

8    %test( ks )
not found
9    %test( ny )
its in there

Using Wingdings in PROC REPORT

The idea for this post came from the book "PROC REPORT by Example" - Techniques for Building Professional Reports Using SAS by Lisa Fine.

To get a listing of Wingdings (or other available fonts), click on Windows | All Programs | Accessories | System Tools | Character Map. In the below case, Wingdings was chosen as the font and the bolded down arrow is associated with hex code EA.

<

An example using the up (E9) and down (EA) wingding fonts with proc report can be seen below.

proc format ;
  value arrow
    low -< 14 = "ea"x
    14 - high = "e9"x ;

  value color 
    low -< 14 = red
    14 - high = green ;
run ;

ods listing close ;
ods html file = "c:\temp\wingdings.html" ;
  proc report data = sashelp.class( obs = 5 ) nowd ;
    column name age direction ;
    define name / display ;
    define age  / analysis  ;
    define direction /  
       computed 
       format = arrow. 
       style( column ) = [ font_face = wingdings just = c ] ;

    compute direction ;
      direction = age.sum ;
      call define( "direction", "style", "style = [ foreground = color. ]" ) ;
    endcomp ;
  run ;
ods html close ;
ods listing ;

The Need for Speed - %SCAN loop

Have you ever had to write a macro to parse words in a string using the %SCAN function and wondered what is the best, most efficient technique? The below code compares the time it takes to do this using three different techniques.

In my test, I repeated the 26 letters of the alphabet 10 times so that a total of 260 scans are performed as a benchmark. Review the below code and then see the bar chart of the average time it took after five attempts of each macro. It is clearly best to use the COUNTW() function once to detemine the number of times to loop.

data _null_ ;
  length string $1028 ;
  do i = 1 to 10 ;
    do j = 65 to 90 ;
      string = catx( " ", string, byte( j ) ) ;
     end ;
  end ;
  call symput( 'macvar', string ) ;
run ;

%let starttime = %sysfunc( datetime() ) ;
%macro good ;
  %local i retval ;
  %let i = 1 ;
  %do %while( %scan( &macvar., &i. ) ne ) ;
    %let i = %eval( &i + 1 ) ;
    %let retval = %scan( &macvar., &i. ) ;
  %end ;
%mend ;
%put %sysevalf( %sysfunc(datetime()) - &starttime. ) ;

%let starttime = %sysfunc( datetime() ) ;
%macro better ;
  %local i retval ;
  %let i = 1 ;
  %let retval = %scan( &macvar., &i. ) ;
  %do %while( &retval. ne ) ;
    %let i = %eval( &i. + 1 ) ;
    %let retval = %scan( &macvar., &i. ) ;
  %end ;
%mend ;
%better
%put %sysevalf( %sysfunc(datetime()) - &starttime. ) ;

%let starttime = %sysfunc( datetime() ) ;
%macro best ;
  %local i retval ;
  %let totalwords = %sysfunc( countw( &macvar ) ) ;
  %do i = 1 %to &totalwords ;
    %let retval = %scan( &macvar., &i. ) ;
  %end ;
%mend ;
%best
%put %sysevalf( %sysfunc(datetime()) - &starttime. ) ;

Click graph for a sharper image.

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

Getting around SQL reserved words

PROC SQL has some ANSI standard reserved words such as USER that cannot be used without renaming it or using the DQUOTE = ANSI option according to this web site

The first SQL query below fails because "the USER keyword will have a constant value for all rows in a query".  The second query works but requires the DQUOTE = ANSI option and you must double quote the reserved word.

I did not want to quote or rename a column. Through some trial and error I figured out that using a fully qualified table.column name was the solution that worked best for me.

data x ;
  do user = 'Billy', 'Joe', 'Bob' ;
    do i = 1 to 10 ;
      output ;
    end ;
  end ;
run ;

proc sql    ;
  create table x1 as
    select       user
               , count(*) as cnt
      from       x
      group by   user ;

  reset dquote = ansi ;
  create table x2 as
    select       "user"
               , count(*) as cnt
      from       x 
      group by   "user" ;

  create table x3 as
    select       x.user
               , count(*) as cnt
      from       x
      group by   x.user ;
quit ;