Easysoft ODBC-SQL Server Driver

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

Article:
01029
Last Reviewed:
25th January 2012
Revision:
2

You can connect SAS 9 on Linux to MS 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. (Registration required.)
  2. Install and license the SQL Server ODBC driver on the Linux machine.

    For installation instructions, see 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 will create a default data source named SQLSERVER_SAMPLE for you in /etc/odbc.ini. Copy this data source to create a new data source named MSSQL-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 MSSQL-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 data set. 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=MSSQL-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="MSSQL-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.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.