How do I access Microsoft SQL Server from SAS on Linux?

You can connect SAS on Linux to Microsoft SQL Server by using the SQL Server ODBC driver with the SAS/ACCESS Interface to ODBC.

  1. Download the SQL Server ODBC driver for your Linux platform.
  2. Install and license the SQL Server ODBC driver on the Linux machine.

    For installation instructions, refer to the SQL Server ODBC driver documentation.

  3. Add the following paths to the LD_LIBRARY_PATH environment variable: 
    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/sqlserver
export LD_LIBRARY_PATH

    This variable can be set in !SASROOT/bin/sasenv_local.

  4. The SQL Server ODBC driver installation script creates a default data source named SQLSERVER_SAMPLE for you in /etc/odbc.ini. Copy this data source to create a new data source named SQL-SERVER-SAS-DSN (or another name, if you prefer).
  5. In the new data source, replace the Server, Database, (and optionally Port) attribute values with values appropriate for your SQL Server instance.
  6. Test the data source with unixODBC's isql command line utility. For example: 
    $ cd /usr/local/easysoft/unixODBC/bin
$ ./isql -v SQL-SERVER-SAS-DSN myuser mypassword
  7. SAS/ACCESS provides two methods for accessing a database, the LIBNAME statement and the SQL pass-through facility. The LIBNAME statement lets you access and manipulate your SQL Server data as if it were a SAS dataset. The SQL pass-through facility, unlike the LIBNAME statement, lets you use non-ANSI standard SQL.

    • LIBNAME Example

      In SAS, use the LIBNAME statement to create a SAS alias for your SQL Server database. For example: 
      LIBNAME mylibref ODBC DSN=SQL-SERVER-SAS-DSN USER=myuser PASSWORD=mypassword SCHEMA=sales;

      This LIBNAME statement creates a SAS alias named mylibref, which uses the SAS/ACCESS Interface to ODBC (ODBC) to connect to the SQL Server ODBC driver data source. The statement specifies the sales schema in SQL Server.

    • SQL pass-through example

      proc sql;
   connect to odbc as mydb (datasrc="SQL-SERVER-SAS-DSN" user=myuser password=mypassword);
   execute (exec sp_who) by mydb;
disconnect from mydb;
quit;

      This SQL pass-through example executes a SQL Server stored procedure.

Further information