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