Add SAS Data Set to SQL Server

It is often advantageous or required to add a SAS data set to a remote database. The below example adds the SASHELP.CLASS data set to SQL Server as a global ##temp table. The below image reveals that the SAS data set appears in SQL Server's Management Studio (SSMS) under the Databases | System Databases | tempdb | Temporary Tables node prior to clearing the sqltemp libref.

This example utilizes the %getconnection macro to stream back the connection string. Information on that macro can be found here.

libname sqltemp 
  odbc
  noprompt = "%getconnection( server = prod )"
  bcp = yes
  connection = global ;

/* add last date into SQL Server ##temp table */
data sqltemp.'##sasclass'n ;
  set sashelp.class ;
run ;

proc sql ;
  connect to odbc ( "%getconnection( server = prod )" ) ;

    create table sqlclass as
      select       *
        from       connection to odbc
        (
          select   * 
            from   ##sasclass
        ) ;

 disconnect from odbc ;
quit ;

libname sqltemp clear ;

Rolling Moving Average

I needed to create a 5 year rolling moving average of transposed weekly data. Jerry from SAS tech support came up with what I thought was a great response. He suggested using a multilabel format along with proc summary and the CLASS var / MLF option.

I used a macro loop to create the dynamic proc format statement. The use of the SAS colon (:) wildcard makes it easy to select like named variables such as week1 - week52.

data sample ;
  do year = 2000 to 2015 ;
    do weekno = 1 to 52 ;
      value = int( ranuni( 1 ) * 1000 ) ;
      output ;
    end ;
  end ;
run ;

proc transpose
  data   = sample
  out    = transposed( drop = _name_ )
  prefix = week ;

  var value ;
  id  weekno ;
  by  year ;
run ;

%macro yearformat(
    startyear = 2000
  , endyear   = 2015
  , periods   = 5
)
;
  %local year ;
  proc format ;
    value yearf ( multilabel )
    %do year = &startyear. %to %eval( &endyear. - &periods. + 1 ) ;
      &year - %eval(&year + &periods. -1) = "%eval(&year. + &periods. -1)"
    %end ;
  ;
  run ;
%mend ;
%yearformat

proc summary
  data = transposed
  nway ;

  class year / mlf ;
  var week: ;

  format
    year yearf.
    week: 8.2 ;

  output
    out  = movingaverage(drop = _type_ _freq_ )
    mean = ;
run ;

Weeks Per Year

There are 24 hours in a day, 7 days in a week and 52 weeks in a year - until there are 53. I have an application that needs to know how many Fridays ( day of week = 6 ) fall in a year. Since 1999 to 2016 the following years have 53 Fridays in a year: 1999, 2004, 2010 and 2016.

The below function will return a value of 52 or 53 as both a custom function and then as a macro function.

proc fcmp outlib = work.functions.utilies ;
  function weeksperyear( weekday, year ) ;
    if year( intnx( 'week.6', nwkdom( 1, weekday, 1, year ), 52 ) ) = year then retval = 53 ;
    else retval = 52 ;
    return ( retval ) ;
  endsub ;
run ;

options cmplib = ( work.functions ) ;

data x ;
  do year = 1992 to 2016 ;
    weeks = weeksperyear( 6, year ) ;
    output ;
  end ;
run ;

/**************************************************************************
*     Program: weeksperyear.sas
*      Author: Tom Bellmer
*     Created: 07NOV2014 
*     Purpose: returns either 52 or 53 weeks
*       Usage: %weeksperyear( weekday = 6, year = 2014 )
*       Notes: weekday= day of the week with 1 = Sun, 2 = Mon ... 7 = Sat
**************************************************************************/

%macro weeksperyear( weekday = 6, year =  2014 ) ;
  %local retval ;

  %if &weekday. eq %then %do ;
    %put %str(E)RROR: Invalid weekday value of &weekday. ;
    %return ;
  %end ;

  %if &year. eq %then %do ;
    %put %str(E)RROR: Invalid year value of &year. ;
    %return ;
  %end ;

  %if %eval( %sysfunc( year( 
               %sysfunc( intnx( week.6
                 , %sysfunc( nwkdom( 1, &weekday., 1, &year. ) )
                 , 52 ) 
               ) 
             ) 
           ) = &year. ) %then %let retval = 53 ;
   %else %let retval = 52 ;

   &retval. 
%mend ;

/* EOF: weeksperyear.sas  */

LagLead Macro

UPDATE: If you have SAS/ETS installed, you can use proc expand as an alternative. There are a ton of options with this procedure but here is the syntax to emulate the below code:

proc expand data = sashelp.class out = example method = none ;
  convert age = sumage / transform = ( cmovsum 5 trimleft 2 trimright 2 ) ;
run ;

The SAS supplied LAG() function is used to retain previously read variables. There is no supplied LEAD function so the below lagLead function was created.

The key to making this work is to LINK to a DO LOOP that utilizes the POINT= option on the SET statement to read the data in random access mode. A RENAME= option was required to not overwrite the existing VAR of interest in the program data vector (PDV).

In the below example, while sitting on row 5 the prior two, current row and next two rows are summed resulting in a value of 65 ( 13 + 14 + 14 + 12 + 12 ).

/**************************************************************************
*     Program: lagLead.sas
*      Author: Tom Bellmer
*     Created: 05NOV2014 
*     Purpose: read and sum X rows before and after current row
**************************************************************************/

%macro lagLead( 
    dsn      = sashelp.class
  , outdsn   = example
  , var      = age
  , outvar   = sumage
  , lag      = 2
  , lead     = 2 
) ;

  %local tempvar ;
  %let tempvar = temp_%sysfunc(ceil( %sysfunc( ranuni(0) ) * 10000 ) ) ;

  data &outdsn.( drop = &tempvar. ) ;
    set &dsn. nobs = totalrows ;
      if _n_ > &lag. and _n_ <= ( totalrows - &lead. ) then link laglead ;
      output ;
    return ;

    laglead:
      do i = ( _n_ - &lag. ) to ( _n_ + &lead. ) ;
        set &dsn.( keep = &var. rename = ( &var. = &tempvar. ) ) point = i ;
        &outvar. = sum( &outvar., &tempvar. ) ;
      end ;
    return ;
  run ;
%mend ;

/* EOF: lagLead.sas  */

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

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 ;

Alternating colored rows and columns

The below code shows how to use SAS's proc report to display alternating colored rows and to have the header and 1st column have matching colors. This is all accomplished using ODS style attributes.

ods html body = "x.html" style=sasweb ;
  proc report 
    data = sashelp.class
    nowd 
    style( header ) = [ background = red ]
  ;
    where age > 14 ;

    column name sex age height weight ;

    compute name ;
      count + 1 ;
      if mod( count, 2 ) then 
           call define( _row_, 'style', 'style=[background = cxd9d9d9]' ) ;
      else call define( _row_, 'style', 'style=[background = cxe9f3ff]' ) ;

      call define( _col_, 'style', 'style = [ foreground = white background = red]' ) ;
    endcomp ;     
  run ;

ods html close ;

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 ;