LIBNAME Statement for the ODBC Engine

Overview

This section describes the LIBNAME statement that SAS/ACCESS Interface to ODBC supports. For general information about this feature, see LIBNAME Statement for Relational Databases.

Here is the LIBNAME statement syntax for accessing ODBC.

LIBNAME libref odbc <connection-options> <LIBNAME-options>;

For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

odbc

specifies the SAS/ACCESS engine name for the ODBC interface.

connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. When you use the LIBNAME statement, you can connect to ODBC in many different ways. Specify only one of these methods for each connection because they are mutually exclusive.

USER=, PASSWORD=, DATASRC=
COMPLETE=
NOPROMPT=
PROMPT=
REQUIRED=

Here is how these options are defined.

Note: All of the following connection options are also valid in the CONNECT statement when you use the SQL pass-through facility (SQL procedure) to connect to your DBMS.

USER=<'>ODBC-user-name<'>

lets you connect to an ODBC database with a user ID that is different from the default ID. USER= is optional.

Alias: UID=

PASSWORD=<'>ODBC-password<'>

specifies the ODBC password that is associated with your user ID. PASSWORD= is optional. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks. If you do not want to enter your ODBC password in uncoded text on this statement, see PROC PWENCODE in the Base SAS Procedures Guide for a method to encode it.

Alias: PWD=

DATASRC=<'>ODBC-data-source<'>

specifies the ODBC data source to which you want to connect. For Windows platforms, data sources must be configured by using the ODBC Driver Manager. For UNIX platforms, data sources must be configured by modifying the .odbc.ini file. This option indicates that the connection is attempted using the ODBC SQLConnect API, which requires a data source name. You can also use a user ID and password with DSN=. If you want to use an ODBC file called DSN, instead of specifying DATASRC=<'>ODBC-data-source<'>, use the PROMPT= or NOPROMPT= option, followed by "filedsn=(name-of-your-file-dsn);". Here is an example:

libname mydblib odbc noprompt="filedsn=d:\share\msafiledsn.dsn;";

Alias: DATABASE=, DB=, DSN=

COMPLETE=<'>ODBC-connection-options<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, you are prompted with a dialog box that displays the values from the COMPLETE= connection string. You can edit any field before you connect to the data source. This option is not supported on UNIX platforms. See your ODBC driver documentation for more details.

NOPROMPT=<'>ODBC-connection-options<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. If you do not specify enough correct connection options, an error is returned. No dialog box is displayed to help you complete the connection string.

PROMPT=<'>ODBC-connection-information<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. PROMPT= does not immediately try to connect to the DBMS. A dialog box is displayed in SAS windowing environment instead that contains the values that you entered in the PROMPT= connection string. You can edit values or enter additional values in any field before you connect to the data source.

Restriction: This option is not available on UNIX platforms.

REQUIRED=<'>ODBC-connection-options<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, a dialog box prompts you for the connection options. REQUIRED= lets you modify only required fields in the dialog box.

Restriction: not supported on UNIX platforms

See your ODBC driver documentation for a list of the ODBC connection options that your ODBC driver supports.

The following ODBC connection options are not supported on UNIX:

COMPLETE=
PROMPT=
REQUIRED=

LIBNAME-options

specify how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to ODBC, with the applicable default values. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure.For details, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for ODBC
Option	Default Value	Valid in CONNECT
ACCESS=	none
AUTHDOMAIN=	none
AUTOCOMMIT=	data-source specific	●
BL_LOG=	none
BL_OPTIONS=	none
BULKLOAD=	NO
CONNECTION=	UNIQUE when data source supports only one cursor per connection, SHAREDREAD otherwise	●
CONNECTION_GROUP=	none	●
CURSOR_TYPE=	FORWARD_ONLY	●
DATETIME2=	NO
DBCOMMIT=	1000 when inserting rows, 0 when updating rows
DBCONINIT=	none	●
DBCONTERM=	none	●
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS	●
DBINDEX=	NO
DBLIBINIT=	none
DBLIBTERM=	none
DBMAX_TEXT=	1024	●
DBMSTEMP=	NO
DBNULLKEYS=	YES
DBNULLWHERE=	YES
DBPROMPT=	NO	●
DBSASLABEL=
DBSLICEPARM=	THREADED_APPS,2 or THREADED_APPS,3
DEFER=	NO	●
DELETE_MULT_ROWS=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
IGNORE_READ_ONLY_COLUMNS=	NO
INSERT_SQL=	data-source specific
INSERTBUFF=	based on row length
KEYSET_SIZE=	0
LOGIN_TIMEOUT=	0
MULTI_DATASRC_OPT=	NONE
PRESERVE_COL_NAMES=	see Naming Conventions for ODBC
PRESERVE_COMMENTS=	NO	●
PRESERVE_GUID=	YES
PRESERVE_TAB_NAMES=	see Naming Conventions for ODBC
PRESERVE_USER=	NO
QUALIFIER=	none
QUERY_TIMEOUT=	0	●
QUOTE_CHAR=	none
READ_ISOLATION_LEVEL=	RC (see Locking in the ODBC Interface)	●
READ_LOCK_TYPE=	ROW	●
READBUFF=	0
REREAD_EXPOSURE=	NO	●
SCANSTRINGCOLUMNS=	NO
SCHEMA=	none
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
STRINGDATES=	NO	●
TRACE=	NO	●
TRACEFILE=	none	●
UPDATE_ISOLATION_LEVEL=	RC (see Locking in the ODBC Interface)
UPDATE_LOCK_TYPE=	ROW	●
UPDATE_MULT_ROWS=	NO
UPDATE_SQL=	driver-specific
USE_ODBC_CL=	NO	●
UTILCONN_TRANSIENT=	NO
WARN_BIGINT=	NO

ODBC LIBNAME Statement Examples

In this example, USER=, PASSWORD=, and DATASRC= are connection options.

libname mydblib odbc user=myusr1 password=mypwd1 datasrc=mydatasource;

In this next example, the libref MYLIB uses the ODBC engine to connect to an Oracle database. The connection options are USER=, PASSWORD=, and DATASRC=.

libname mydblib odbc datasrc=mydatasource user=myusr1 password=mypwd1;

proc print data=mydblib.customers;
   where state='CA';
run;

In the next example, the libref MYDBLIB uses the ODBC engine to connect to a Microsoft SQL Server database. The connection option is NOPROMPT=.

libname mydblib odbc
   noprompt="uid=myusr1;pwd=mypwd1;dsn=sqlservr;"
   stringdates=yes;

proc print data=mydblib.customers;
   where state='CA';
run;

Last updated: February 3, 2026