SAS Dictionary Tables vs Views

The metadata availble in SAS dictionary tables can be a valuable resource in developing applications. This data can be accessed using PROC SQL and the special dictionary libref or from the SASHELP.V* views. The below comparison reveals that PROC SQL is about 100X faster than the associated view.

For more information about SAS dictionary tables, view the following paper by Frank Dilorio and Jeff Abolafia titled Dictionary Tables and Views: Essential Tools for Serious Applications.

options fullstimer ;
%let viewstart = %sysfunc( datetime() ) ;
data columninfodatastep ;
   set sashelp.vcolumn ;
     where     libname = 'SASHELP'
           and memname = 'CLASS' ;
run ;
%put dictionary view extract time:  %sysfunc(putn(%sysevalf(%sysfunc(datetime())-&viewstart.),time13.3)) ;

%let sqlstart = %sysfunc( datetime() ) ;
/* same thing using dictionary tables (vs sashelp.v* views) */
proc sql ;
  create table columninfosql as 
    select   *
      from   dictionary.columns
      where      libname = 'SASHELP'
             and memname = 'CLASS' ;
quit ;
%put dictionary table extract time:  %sysfunc(putn(%sysevalf(%sysfunc(datetime())-&sqlstart.),time13.3)) ;

<

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 ;