getconnection() - macro function

I wanted a way to call a SAS macro as a function to return a streaming value that would be rendered in place. Doing so would by definition eliminate global macro variable collisions.

The below source code does just that using all %local macro variables and only macro code. The returned value (a connection string to a remote database server) is used in place which will eliminate hard coding issues when server definitions changes as they always do over time.

The next image reveals the trusted connection strings used as DSN-less connections followed by the SAS macro function.

proc sql noprint stimer ;
   connect to odbc ( "%getconnection(server=prod)" ) ;
     create table mydsn as
          select       *
            from       connection to odbc 
            (
              select     *
                from     db.schema.table 
            ) ;
   disconnect from odbc ;
quit;


%macro getconnection( server = Prod ) ;
  %local libref
         path 
         closelib 
         rc
         dsid 
         returncolumn
         connection ;

  %let libref       = afutil ;
  %let path         = c:\xport\ ;
  %let closelib     = N ;
  %let returncolumn = connection ;

  %if %sysfunc( libref( &libref. ) ) ne 0 %then %do ;
    %let rc = %sysfunc( libname( &libref., &path. ) ) ;
    %if &rc ne 0 %then %do ;
      %put ERROR: could not create the libname &libref.. ;
      %return ;
    %end ;
    %let closelib = Y ;
  %end ; 

  %let dsid = %sysfunc( open( &libref..servers( where = ( upcase( server ) = upcase( "&server." ) ) ) ) ) ;                                                                          
  %let rc = %sysfunc( fetch( &dsid. ) ) ;
  %if &rc ne 0 %then %do ;
    %put ERROR: no entry found in &libref..servers where upcase( server ) = upcase( "&server." ) ;
    %let rc = %sysfunc( close( &dsid. ) ) ; 
    %return ;
  %end ; 
 
  %let connection = %sysfunc( getvarc( &dsid., %sysfunc( varnum( &dsid., &returncolumn. ) ) ) ) ;                                                                
  %let rc = %sysfunc( close( &dsid. ) ) ; 

  %if &closelib. = Y %then %let rc = %sysfunc( libname( &libref. ) ) ; 

  &connection.    
%mend ;

Moving Average

The below code reveals the concept of calculating a moving average. A _temporary_ array retains its values and is not included in the program data vector (PDV). The use of modulus division is critical in replacing the most recent value with the oldest retained value in the array.

%let rows = 3 ;
data movingaverage ;
  array ave[ &rows ] _temporary_ ; 
  do i = 1 to 5 ;
    subscript = mod( i , &rows ) ; 
    subscript = ifn( subscript, subscript, &rows ) ;
    ave[ subscript ] = i ; 
    if i >= &rows then average = mean( of ave[ * ] ) ;
    output ;
  end ;
run ;