Last 3 days of month using SQL

I needed to get the last 3 entries for each month of a data set. This is not as easy as the last 3 calendar days of the month so a self join was involved. This solution was supplied by Michelle at SAS technical support.

data dates ;
  do date = '01jan2013'd to '31dec2014'd ;
    var = ranuni( 123 ) * 100 ;
    if weekday( date ) not in ( 1, 7 ) then output ;
  end ;
  format date date9. ;
run ;


proc sql ;
  create table results( drop = ranking ) as
    select        *
                , ( select   count( b.date )
                      from   dates b
                      where  b.date <= a.date 
                  ) as ranking 
      from        dates a 
      group by    year( date )
                , month( date ) 
      having      ranking between max( ranking ) and max( ranking ) - 2 
      order by    date ; 
quit ;

Macro Formatter

My previous blog entry, Code Formatter, showed a technique to clean up ugly SAS code. This entry utilizes the %codeformat macro to make macro code processed using the mprint and mfile options easier to read.

A special filename reference called MPRINT and the options MPRINT and MFILE must be turned on prior to running the macro code. In order to handle this, the first parameter of macroformatter contains the macro call to be processed without the leading % sign. The output of the rendered MPRINT/MFILE is difficult to read because each line starts in the first column. That is where the codeformatter macro comes into play to make it easier to read.

Consider the following macro that contains multiple commands on a single line followed by the output using MPRINT/MFILE

%macro x( what ) ;
  data x ; set sashelp.class; saywhat  = "&what." ; run ;
  title "%sysfunc(date(), downame. ) report" ;
  proc print data = x ; var name age weight saywhat ; run ;
%mend ;
%x( hello )

data x ;
set sashelp.class;
saywhat = "hello" ;
run ;
title "   Monday report" ;
proc print data = x ;
var name age weight saywhat ;
run ;
filename mprint clear ;

If you were to call the %x(what) macro using the macroformatter you'd get the output that follows:

%macroformatter( mname = x( hello ), outputfile = c:\temp\tdb.sas )

data x ;
  set sashelp.class;
  saywhat = "hello" ;
run ;
title "   Monday report" ;

proc print data = x ;
  var name age weight saywhat ;
run ;
filename mprint clear ;

Below is the source code.

/**************************************************************************
*     Program: macroformatter.sas
*      Author: Tom Bellmer
*     Created: 12OCT2014  
*     Purpose: writes SAS macro to a file and formats it
**************************************************************************/
%macro macroformatter( mname =, outputfile = c:\temp\formattedcode.sas ) ;
  %local tempfile fref rc ;

  %*-- delete tempfile to keep it from being appended ;
  %let tempfile = %sysfunc( pathname( work ) )/macroformatter.txt ;
  %let rc = %sysfunc( filename( fref, &tempfile. ) ) ;
  %let rc = %sysfunc( fdelete( &fref. ) ) ;
 
  filename mprint "&tempfile." ;
  options  mprint mfile ;
    %&mname. 
  filename mprint clear ;
  options  nomprint nomfile ;
  
  %codeformatter( inputfile = &tempfile., outputfile = &outputfile )
%mend ;

Code Formatter

In July 2010, Chris Hemedinger author of The SAS Dummy blog wrote an article titled 'Hope for ugly programs'.

That article showed that SAS Enterprise Guide had a shortcut of Ctrl-I ("indent this!") to convert ugly code into something much more readable. The below code is my attempt to emulate the behaviour in Base SAS.

/**************************************************************************
*     Program: codeformatter.sas
*      Author: Tom Bellmer
*     Created: 10Oct2014 
*     Purpose: Make SAS program more readable by placing one statemnt per  
*              line, indent code below PROC and DATA steps and indenting DO
*              constructs
*       Usage: %codeformatter( infile = , outfile = c:\temp\beautify.sas )
**************************************************************************/
%macro codeformatter( inputfile =, outputfile = c:\temp\codeout.sas ) ;
  %if not %sysfunc( fileexist( &inputfile. ) ) %then %do ;
    %put %str(E)RROR: The input file &inputfile. does not exist. ;
    %return ;
  %end ;

  data _null_ ;
    length 
      semicolonlocation startposition indent i commentoff 5 
      sasstatement $8 
      word $32767 ;
    retain commentoff 1 ;

    infile "&inputfile."  lrecl = 32767 truncover end = eof ;
    file   "&outputfile." lrecl = 32767 ;

    do until( eof ) ;
      input line $32767. ;
      startposition = 1 ;

      if find( line, '/*' ) then commentoff = 0 ;

      do i = 1 to max( count( line, ';' ), 1 ) ;
        semicolonlocation = find( line, ';', startposition  ) + 1 ; 

        if semicolonlocation = 1 then word = line ;
        else word = substr( line, startposition, ( semicolonlocation 
          - startposition ) ) ;

        startposition = semicolonlocation ;
        sasstatement = upcase( compress( scan( word, 1 ), ';' ) ) ;

        if sasstatement in ( 'LIBNAME', 'GOPTIONS', 'DATA', 'PROC', 'QUIT'
             , 'RUN', '*', '/*', 'OPTIONS', 'ODS', 'TITLE', 'FOOTNOTE'
             , 'FILENAME', 'RSUBMIT')  then do ;
          if sasstatement in ( 'PROC', 'DATA' ) then put ;
          indent = 0 ;
        end ;
        else indent = max( 2, indent ) ;

        if commentoff and ( find( word, 'end ', 'i' ) 
          or find( word, 'end;', 'i' ) ) 
           then indent = indent - 2 ;
        put +indent word ;

        if commentoff and ( find( lowcase( word ), 'do ' ) 
          or find( lowcase( word ), 'do;' ) ) 
           then indent + 2 ;
      end ;

      if find( line, '*/' ) then commentoff  = 1 ;
 
    end ;
  run ;
%mend ;

/* EOF: codeformatter.sas  */