Sunday, November 30, 2014

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 
  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 ;

No comments:

Post a Comment