DBMS Access Using the LIBNAME Statement (1)

Accessing Other Vendors' DBMS Data

DBMS Access Using the LIBNAME Statement

Referencing a DBMS

To access DBMS data, you can use the SAS/ACCESS LIBNAME statement with the appropriate SAS/ACCESS engine and options. After you assign a libref to a DBMS, you can reference its tables and views as SAS data sets, using standard two-level SAS names.

You can store the SAS/ACCESS LIBNAME statement with any SAS program to reference the SAS library automatically when you submit the program.

General form, SAS/ACCESS LIBNAME statement:

LIBNAME libref SAS/ACCESS-engine-name
<SAS/ACCESS-engine-connection-options>
<SAS/ACCESS-LIBNAME-options>;

where

SAS/ACCESS-engine-name is the engine name for your DBMS, such as ORACLE or SYBASE
SAS/ACCESS-engine-connection-options are connection options specific to the database, such as USER= and PASSWORD=
SAS/ACCESS-LIBNAME-options are processing options specific to the database, such as PATH= and DBINDEX=.

For example, the SAS/ACCESS LIBNAME statement below associates the libref Dblib with an ORACLE database management system. If the ORACLE DBMS contains a table named Employees, you can reference Dblib.Employees as if it were a SAS data set.
libname dblib oracle user=tester1 password=testpw path=ora7db dbindex=y;

Since you can define any number of librefs in SAS programs, you can process data from multiple DBMS sources from within the same SAS program.

Clearing Librefs
To dissassociate a libref from a DBMS, specify a SAS/ACCESS LIBNAME statement with the libref followed by the CLEAR option:
libname dblib clear;

The SAS/ACCESS engine disconnects from the DBMS and frees any resources that are associated with the connection.