Connecting SAS to SQL Server or Azure SQL

To connect SAS or SAS Viya to SQL Server or Azure SQL you can either use:

Note that the SAS/ACCESS Interface to Microsoft SQL Server also uses an ODBC driver. The difference between the two interfaces is that the SAS/ACCESS Interface to Microsoft SQL Server has an ODBC driver bundled with it, the SAS/ACCESS Interface to ODBC requires you to obtain an ODBC driver separately (which means that you're not limited to SQL Server should your requirements change).

This article covers the SAS/ACCESS Interface to ODBC.

To check whether your SAS distribution includes the SAS/ACCESS Interface to ODBC, use this SAS code:

proc SETINIT; run;

This lists all the installed SAS software. Check the command's output for: 

SAS/ACCESS Interface to ODBC

If SAS/ACCESS Interface to ODBC isn't listed, contact your SAS sales representative.

If your SAS distribution does include the SAS/ACCESS Interface to ODBC, obtain a SQL Server ODBC driver that matches your SAS platform and architecture.

This article describes using SAS with our SQL Server ODBC driver, which:

Follow these steps, to connect SAS to SQL Server:

  1. From the Easysoft web site, download the SQL Server for your SAS platform.

    If your version of SAS is 32-bit, download the 32-bit version of the ODBC driver. If your version of SAS is 64-bit, download the 64-bit version of the ODBC driver. To check your SAS architecture, run this SAS code:

    %PUT %SYSFUNC(GETOPTION(SYSADDRBITS));

    If this command returns 64, SAS is 64-bit. If this command returns 32, SAS is 32-bit.

  2. As root, install the SQL Server ODBC driver on your SAS machine.
  3. The installation runs licshell, which lets you obtain a trial license for the SQL Server ODBC driver.
  4. Post-installation, configure a SQL Server ODBC driver data source, which is where you specify the connection details for your SQL Server instance. Do this in the odbc.ini file for SYSTEM data sources. For example: 
    $ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini

$ sudo vi /etc/odbc.ini
% more /etc/odbc.ini
[SQLSERVER_SAMPLE]
Driver=Easysoft ODBC-SQL Server
Description=Easysoft SQL Server ODBC driver
Server=my_sql_server_machine\sqlexpress
Database=pubs
User=sa
Password=1234abcd
  5. Test the ODBC driver connection outside of SAS. For example: 
    cd /usr/local/easysoft/unixODBC/bin
./isql.sh -v SQLSERVER_SAMPLE

    Correct any errors before trying to connect from SAS. If you can't get the ODBC connection working with isql, you won't be able to get it working from SAS.

    For help with some common errors, refer to these Easysoft articles:

  6. In your SAS environment file (for example, sasenv_local, sasenv_deployment, cas_usermods.settings, or workspaceserver_usermods.sh) configure the environment so that SAS can load the unixODBC Driver Manager and SQL Server ODBC driver. For example: 
    export ODBCSYSINI=/etc
export ODBCINI=odbc.ini
export ODBCINSTINI=odbcinst.ini
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/easysoft/sqlserver:/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib

    If you didn't install the SQL Server ODBC driver under /usr/local, edit the LD_LIBRARY entry and set EASYSOFT_ROOT. For example: 

    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/easysoft/sqlserver:/opt/easysoft/lib:/opt/easysoft/unixODBC/lib
export EASYSOFT_ROOT=/opt

    Note The SAS/ACCESS Interface to ODBC is linked against the unixODBC Driver Manager. When you specify the ODBC data source for SQL Server in SAS, the Driver Manager loads the SQL Server ODBC driver, which then connects to SQL Server. The unixODBC Driver Manager is included in the SQL Server ODBC driver distribution.

  7. Use either a LIBNAME statement or PROC SQL Pass-Through to work with SQL Server data in SAS.

Using the LIBNAME statement

Assign a SAS library reference (libref) to the ODBC data source SQLSERVER_SAMPLE:

LIBNAME mssqlodbc ODBC DSN="SQLSERVER_SAMPLE";

After running this command, check the SAS log. If the connection is successful, you'll get:

NOTE: Libref mssqlodbc was successfully assigned as follows:
Engine: ODBC
Physical Name: SQLSERVER_SAMPLE

If you get something like:

Data source name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

review steps 5 and 6 in the procedure shown earlier.

Examples

Create a dsn-less connection:

LIBNAME dsnlessodbc ODBC NOPROMPT="DRIVER=Easysoft ODBC-SQL Server;Server=my_sql_server_machine\sqlexpress;UID=sa;PWD=1234abcd;Database=pubs;"

This removes the need to create an ODBC data source or set ODBCSYSINI, ODBCINI, and ODBCINSTINI in your SAS environment.

Create a read-only connection:

LIBNAME mssqlodbc ODBC DSN="SQLSERVER_SAMPLE" READONLY=YES;

List tables:

proc datasets lib=mssqlodbc;
run;

Access a particular table's data

data authors;
    set mssqlodbc.authors;
run;

Run a query:

proc print data=mssqlodbc.publishers;
   where pub_name='New Moon Books';
run;

Read remote SQL Server data into a local table:

proc SQL;
    create table local_authors as
    select * from mssqlodbc.authors;
quit;

Using PROC SQL Pass-Through

Examples

Run a query:

proc sql;
  connect to odbc (dsn="SQLSERVER_SAMPLE");
  select * from connection to odbc
  (
    select * from publishers where pub_name = 'New Moon Books'
  );
  disconnect from odbc;
quit;

Use a SAS macro variable in a query:

proc sql;
  %let royalty = 100;
  connect to odbc (dsn="SQLSERVER_SAMPLE");
  select * from connection to odbc
  (
    select * from title where royalty = &royalty
  );
  disconnect from odbc;
quit;

Insert data:

proc sql;
   connect to odbc (dsn="SQLSERVER_SAMPLE");
   execute
   (
      insert into publishers (pub_id, pub_name, city, state, country)
      values (1000, 'ACME Publishers', 'Boston', 'MA', 'USA')
   ) by odbc;
   disconnect from odbc;
quit;

Run a stored procedure:

proc sql;
   connect to odbc (dsn="sqlserver_sample");
   create table royalty as
   select * from connection to odbc
   (
      exec byroyalty 60
   );
   disconnect from odbc;
quit;