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

Download Excel from Internet

The ability to read external data such as Excel direclty from the web can eliminate manual processes. SAS can do this but it is not intuitive. Fortunately, I found this post by Louis Sinoff on the SAS Support Communities web site. I am using a land area Excel file available on the Census bureau web site as the sample data. The purpose of this post is to provide some details on how this works.

The URL access method creates a reference to the Excel file on the web. The INFILE statement sets the RECFM = N or as a binary byte stream. NBTYE = N points back the variable N which was assigned a value of -1. NBYTE set to -1 will return the number of bytes available to the input buffer. Of course, the LENGTH = LEN assignment is used with the VARYING format.

Once downloaded, use the EXCEL data engine to read the Excel file contents in SAS. Because there is a special character ($) used in the data set name, you need to quote the name and use the 'N or name literal.

filename webfile url "http://www2.census.gov/prod2/statcomp/usac/excel/LND01.xls" ;
filename outexcel "c:\temp\x.xls" ;
data _null_ ;
  n = -1 ;
  infile webfile 
    recfm  = n 
    nbyte  = n  
    length = len ;
  file outexcel 
    recfm  = n ;
  input ;
  put _infile_ $varying32767. len ;
run;
filename webfile clear ;
filename outexcel clear ;

libname xldata excel "c:\temp\x.xls" ;

data mydata ;
  set xldata.'sheet1$'n( obs = 5 ) ;
run ;

Put it on the Map

There is a major benefit in visually displaying data on maps as it is easier to consume the information. The below program creates sample data for 16 states using their numeric FIPS codes then PROC GMAP to render the content.

The custom %mapanno macro uses the MAPS.USCENTER data set to locate the center of each state and place its data value (if found) below it. The mapanno data set uses the postion= annotate variable to place the values on the map. The next image displays the location for select position= values.

However, that technique does not work well for some coastal states on the eastern seaboard. Those states contain an ocean = 'Y' column value and in those cases, the state abbreviation is shown followed by a slash (/) and its value.

There was still an issue when displaying the value for Massachusetts (MA) as its value was being truncated. The solution was to utilize a technique from SAS/Graph expert, Robert Allison's samples where he uses title5 a=-90 h=6pct " "; to provide some extra space for those values. Here is the finished result:

data sample ;
  format value comma9. ;
  do state = 6, 13, 17, 20, 23, 24, 25, 26, 28, 34, 36, 37, 48, 49, 53, 56 ;
    value = int( ranuni( 1 ) * 10000 ) ;
    output ;
  end ;
run ;

%macro mapanno( dsn =, mapvar=, textsize = 1.5 ) ;
  data mapanno ;
    length function $8 text $20 size 8 ;
    retain xsys ysys '2' hsys '3' when 'a' style "'Albany AMT'" ;

    merge &dsn.( keep = state &mapvar. in = xm ) maps.uscenter ;
      by state ;
    where fipstate( state ) not in( 'AK', 'DC', 'HI', 'PR' ) ;
    lagocean = lag( ocean ) ;
    size = &textsize. ;

    if ocean = 'Y' then do ;
      if not missing( &mapvar. ) then text = catx( " / ", fipstate( state )
        , ifc( vtype( &mapvar ) = 'C', &mapvar.
          , strip( putn( &mapvar., vformat( &mapvar. ) ) ) ) ) ;
      else text = fipstate( state ) ;
      function = 'label' ;
      position = '6' ;
      output ;
      function = 'move' ;
      output ;
    end ;

    position = '5' ;
    if ocean = 'N' then do ;
      if lagocean = 'Y' then do ;
        function = 'draw' ;
        size = &textsize / 4 ;
      end ;
      else do ;
        function = 'label' ;
        if not missing( &mapvar ) then do ;
          position = '2' ;
          text = fipstate( state ) ;
          output ;
          position = '5' ;
          text = ifc( vtype( &mapvar. ) = 'C', &mapvar.
            , strip( putn( &mapvar., vformat( &mapvar. ) ) ) ) ;
        end ;
        else text = fipstate( state ) ;
      end ;
      output ;
    end ;
  run ;
%mend ;
%mapanno( dsn = sample, mapvar = value )

goptions reset = pattern ;
pattern1 v = s c = cxff0000 ;
pattern2 v = s c = cxffa500 ;
pattern3 v = s c = cxffff00 ;
pattern4 v = s c = cx008000 ;
pattern5 v = s c = cx0000ff ;

ods listing close ;
ods html path = "c:\temp\" body = "anno.html" ;
  title "Sample Random Data" ;
  title5 a=-90 h=6pct "  " ;

  proc gmap
      data = sample
      map  = maps.us
      anno = mapanno 
      all ;
         id state ;
   where fipstate( state ) not in( 'AK', 'DC', 'HI', 'PR' ) ;
   choro value / levels = 5 ;
  run ;
  quit ;
ods html close ;
ods listing ;

Matrix Validation

Requirements were to examine the data searching for adjacent cells to the right or below and identify cases where the value decreased. An "A" is used in the output data set to signify an issue going across. A "D" signifies a downward violation while a "B" means there were issues going both across and down.

In order to read ahead, I created the test2 data set and added an extra blank row since firstobs = 2 to ensure all data from test gets read in. Arrays are used to generically compare the values and write out any offending values.

data 
  test
  test2
   ( rename = 
     ( x1 = y1 
       x2 = y2 
       x3 = y3 
       x4 = y4 
       x5 = y5 
     ) 
   ) ;

  infile datalines eof = onemore ;
  input x1 - x5;
  output ;
 return ;

 onemore:
    call missing( of _all_ ) ;
    output test2 ;
 return ;

  datalines;
1 2 3 4 5
1 1 1 2 1
4 3 3 2 1
5 4 3 1 2
1 0 1 0 2
;
run ;


data results( keep = ac: ) ;
  set test ( firstobs = 1 ) nobs = nobs  ;
  set test2 ( firstobs = 2  ) ;
  
  array aa[ * ] x: ;
  array ab[ * ] y: ;
  array ac[ * ] $1 ac1 - ac5 ;

  do i = 1 to dim( aa )  ;
    if i < dim( aa ) then do ;
      if aa[ i ] > aa[ i + 1 ] then ac[ i ] = "A" ;
    end ;
    if _n_ < nobs then do ;
      if aa[ i ] > ab[ i ] then do ;
        if missing( ac[ i ] ) then ac[ i ] = "D" ;
        else ac[ i ] = "B" ;
      end ;
    end ;
  end ;
run ;

Hash Object Throwdown: SetCur() vs Find_Next() methods

Is SAS's hash iterator object's setcur() method faster than a hash object's find_next() method when extracting multiple values from a key value? In code below, 5 million rows were created for key values 'A', 'B' and 'C' then the 'B' value was searched and extracted.

It turns out the hash object's find_next() method is about 25% faster than the iterator's setcur() method.

data input ;
  length
    key $1
    sat  5 ;
 
  do key = 'A', 'B', 'C' ;
    do sat = 1 to 5000000 ;
      output ;
    end ;
  end ;
run ;
 
data
  xiterator( keep = key sat )
  xhash( keep = key sat ) ;
  if 0 then set input ;
 
  dcl hash hh( dataset: 'input', ordered: 'a', multidata: 'y' ) ;
  dcl hiter hi( 'hh' ) ;
  hh.definekey( 'key' ) ;
  hh.definedata( 'key', 'sat' ) ;
  hh.definedone() ;
 
  findthis = 'B' ;
 
  temp_start = datetime() ;
  do rc = hi.setcur( key: findthis ) by 0 while( rc = 0 and key = findthis ) ;
    output xiterator ;
    rc = hi.next() ;
  end ;
  temp_end = datetime() - temp_start ;
  put temp_end time10.4 ;
 
  temp_start = datetime() ;
  do rc = hh.find( key: findthis ) by 0 while( rc = 0 ) ;
    output xhash ;
    rc = hh.find_next() ;
  end ;
  temp_end = datetime() - temp_start ;
  put temp_end time10.4 ;
 
  stop ;
run ;

For more information, read this excellent paper on Hash Objects: Black Belt Hashigana

getSASLogIssues Macro

It is always a very good idea to search through your SAS log to look for any issues that may have occured.

The below macro will scan the SAS DMS log or one supplied from a proc printto log=file statement. See below example followed by the source code.

data x ;
  set sashelp.class ;
  abc = . ;
  if xyz = 1 then age = 99 ;
  weight = weight + abc ;
run ;

%getsaslogissues() ;

/**************************************************************************
*     Program: getsaslogissues.sas
*      Author: Tom Bellmer
* Responsible: Tom Bellmer
*     Created: 04Apr2014 
*     Purpose: Parse SAS log looking for issues
*       Usage: %getsaslogissues()
*       Notes: if logfile is blank then interactive SAS log is used
*              showtable only works if logfile= (blank) or interactive
*    Modified: 
**************************************************************************/

%macro getsaslogissues
  ( 
      logfile   =
    , outdsn    = saslogissues
    , showtable = y  
  ) ;

  %local 
    interactive 
    _efierr_ ;

  %let interactive = n ;   
  %let _efierr_ = 0 ;

  %if &logfile. = %then %do ;
    %let interactive = y ;
    %let logfile = %lowcase( %sysfunc( pathname( work ) ) )\temp%sysfunc( round( %sysfunc( ranuni( -1 ) ) * 800000 ) ).log ;
    dm log "file &logfile. replace" ;
  %end ;
  %else %do ;
    %if not %sysfunc( fileexist( &logfile. ) ) %then %do ;
      %put ERROR: &logfile. does not exist. ;
      %return ;
    %end ;
  %end ;

  data &outdsn. ( keep = lineno issue ) ;
    length lineno 4 ;

    infile "&logfile." 
      truncover 
      length = linelength 
      lrecl  = 32767
    ;
    input issue $varying1024. linelength ;

    if _n_ = 1 then do ;
      issue = "LOG File Location: &logfile." ;
      output ;
    end ;

    if substr( issue, 1, 4 ) in ( 'ERRO', 'WARN', 'NOTE' ) then do ;

      if substr( issue, 1, 4 ) in ( 'ERRO', 'WARN' )
        or find( issue, "converted to numeric values", 'i' ) > 0 
        or find( issue, "converted to character values", 'i' ) > 0 
        or find( issue, "new line when input", 'i' ) > 0
        or find( issue, "lost card", 'i' ) > 0
        or find( issue, "one or more lines were truncated", 'i' ) > 0
        or find( issue, "division by zero detected", 'i' ) > 0
        or find( issue, "mathematical operations could not be performed", 'i' ) > 0
        or find( issue, "missing values were generated", 'i' ) > 0 
        or find( issue, "repeats of by values", 'i' ) > 0 
        or find( issue, "cartesian", 'i' ) > 0
        or find( issue, "a case expression has no else clause", 'i' ) > 0 
        or find( issue, "identifier after a quoted string may change", 'i' ) > 0
        or find( issue, "repeats of by value", 'i' ) > 0
        or find( issue, "uninitialized", 'i' ) > 0
        or find( issue, "invalid", 'i' ) > 0
      then do ;
        lineno = _n_ ;
        output ;
      end ;

    end ;

    if _error_ then call symputx( '_efierr_', 1 ) ;
  run ;

  %if &_efierr_. = 1 %then %do ;
    data &outdsn. ;
      issue = 'problem with %getsaslogissues macro' ;
    run ;
  %end ;

  %if &interactive. = y %then %do ;
    dm "log; clear;" ;
    %if %lowcase( &showtable. ) = y %then %do ;
      dm "viewtable &outdsn. view=table" viewtable  ;
    %end ;
  %end ;
%mend ;

/* EOF: getsaslogissues.sas */

RGB Color Matrix

There is a scene in the movie Vegas Vacation where cousin Eddie says "I'll have some of the yella and don't get cheap on me". If you were to select a yellow, just how yellow is yellow, how blue is blue, etc...?

RGB (red, green, blue) is an additive color scheme that can produce 16.7 million colors. Each of the primary colors is comprised of hexidecimal (hex) values that range from 00 to FF or 256 unique values. If you take those 256 values to the 3rd power, for each of the 3 RGB color, you get 256**3 = 16,777,216. SAS identifies RGB colors with a CX prefix where CX000000 is black (no color), CXFF0000 is red, CX00FF00 is green, CX0000FF is blue and CXFFFFFF is white (all colors).

The idea for this post comes from a paper by Perry Watts, one of the foremost experts on SAS and colors. What I did was to extract and whittle down the 280 predefined SAS colors in the version 8 documentation and changed some of the grouped assignments. The goal was to create a one page matrix of the 161 colors that were retained. The below image reveals the structure of the retained colors in the CNS (color naming system) SAS data set.

The below code uses proc SQL to determine the size of the two dimensional array and to assign column names to the output data set. A DOW-loop is used to load data into the temporary two dimensional array to properly slot the colors into the correct locations. After that, the data from the temporary array is assigned to the columns and written to the output data set.

The compute block in proc report is used to define the background color using the RGB color code. The next image is the final resulting color matrix.

%macro rgbcolormatrix ;
  %local
    columnnames
    columncount
    tablecolumns
    tablerows
    i
    column
  ;
 
  proc sql noprint ;
    select       group
               , count( * )
      into       :columnnames separated by " "
               , :columncount separated by ","
      from       cns
      group by   group ;
  quit ;
 
  %let tablecolumns = &sqlobs. ;
  %let tablerows = %sysfunc( max( &columncount. ) ) ;
 
  data sascolors( keep = obs &columnnames. ) ;
    length Obs 3 ;
    array atemp[ &tablerows., &tablecolumns. ] $8 _temporary_ ;
    array akeep[ &tablecolumns. ] $8 &columnnames. ;
 
    do until( eof ) ;
      col + 1 ;
      do row = 1 by 1 until( last.group ) ;
        set cns end = eof ;
          by group ;
        atemp[ row, col ] = rgb ;
      end ;
    end ;
 
    do row = 1 to &tablerows. ;
      do col = 1 to &tablecolumns. ;
        akeep[ col ] = atemp[ row, col ] ;
      end ;
      obs + 1 ;
      output ;
    end ;
  run ;
 
  ods listing close ;
  ods pdf
    file      = "%sysfunc( pathname( work ) )\rgbcolormatrix.pdf"
    notoc
    author    = "Tom Bellmer"
    title     = "RGB Color Matrix" ;
 
    options nodate nonumber ;
    title ;
 
    proc report
      data = sascolors
      style( header ) = [ background = cx4d7ebf foreground = cx000000 ]
      nowd
    ;
 
      columns obs ( 'RGB Colors' &columnnames. ) ;
      define obs / center ;
 
      %do i = 1 %to &tablecolumns. ;
        %let column = %scan( &columnnames., &i. ) ;
        compute &column. ;
          if not missing( &column. ) then do ;
            if substr( &column., 3, 1 )
              in ( '0', '1', '2', '3', '4', '5', '6' ,'7' )
              and &column. not in ( 'CX00FF00', 'CX00FFFF' ) then
              colorval = cat( 'style={foreground=white background='
                , &column., ' font_size = 8pt}' ) ;
            else
              colorval = cat( 'style={foreground=black background='
                , &column., ' font_size = 8pt}' ) ;
 
            call define( _col_, 'style', colorval ) ;
          end ;
        endcomp ;
      %end ;
 
    run;
  ods pdf close;
  ods listing;
%mend ;