SAS ODBC-connection directly in SAS

The SAS libname statement below shows you how to make an ODBC-connection to an SQL-server through ODBC without first having to create the ODBC-connection in the OS.

libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server;&nbsp;SERVER=<Name of server>; DATABASE=<Name of database on server>; TRUSTED_CONNECTION=yes" schema=<Name of schema in database> access=readonly;

TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.

access=readonly‘ is done for precaution. If the user is only suppose to read the data then this should be set on the datasource – not on the libname.

It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.

libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server;&nbsp;SERVER=<Name of server>; DATABASE=<Name of database on server>; UID=<name of user>; PWD=<Password for user>" schema=<Name of schema in database> access=readonly;

It’s also possible to make SQL pass-through without having an ODBC-connection in the operating system (OS).

proc sql noprint;

	connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; 
	DATABASE=<Your DB>; TRUSTED_CONNECTION=yes");
	execute
        (
             <Your SQL sentence>
         ) by odbc;

	disconnect from odbc;
quit;

TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.

It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.

proc sql noprint;
connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; UID=<Your username>; PWD=<Your password>"); execute (

<Your SQL sentence>

) by odbc;
disconnect from odbc;
quit;

It’s also possible to make a pass-through connection using an DSN created eg in the ‘Data Sources (ODBC)’ in Windows.

proc sql noprint; connect to odbc (DSN=<Your DSN name>&nbsp;USER=<Your username>; Password=<Your password>);
execute (

<Your SQL sentence>

) by odbc; disconnect from odbc; quit;

Below is a link to an article from SAS explaining different ways of using SAS ODBC-connection directly in SAS . It stats that NOPROMT can’t be used with the SQL Server ODBC Driver, but I haven’t experienced that.

Usage Note 52777: Examples of SAS/ACCESS® Interface to ODBC LIBNAME code used to access a Microsoft SQL Server database without configuring an ODBC data source name
http://support.sas.com/kb/52/777.html

Leave a Reply

Your email address will not be published. Required fields are marked *