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; 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; 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>; USER=<Your username>; Password=<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> USER=<Your username>; Password=<Your password>”);
execute (

<Your SQL sentence>

) by odbc; disconnect from odbc; quit;

Leave a Reply

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