The LIBNAME Statement for Relational Databases

LIBNAME Statement Syntax for Relational Databases

Associates a SAS libref with a DBMS database, schema, server, or a group of tables and views.

Valid:

Anywhere

Syntax
Arguments
Details
	Using Data from a DBMS
	Disassociating a Libref from a SAS Library
	Writing SAS Library Attributes to the SAS Log
SQL Views with Embedded LIBNAME Statements
Assigning a Libref with a SAS/ACCESS LIBNAME Statement
Using the Prompting Window When Specifying LIBNAME Options
Assigning a Libref to a Remote DBMS
See Also

Syntax

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

LIBNAME libref CLEAR|_ALL_ CLEAR;

LIBNAME libref LIST|_ALL_ LIST;

Arguments

The SAS/ACCESS LIBNAME statement takes the following arguments:

libref

is any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views. Like the global SAS LIBNAME statement, the SAS/ACCESS LIBNAME statement creates shortcuts or nicknames for data storage locations. While a SAS libref is an alias for a virtual or physical directory, a SAS/ACCESS libref is an alias for the DBMS database, schema, or server where your tables and views are stored.

engine-name

is the SAS/ACCESS engine name for your DBMS, such as oracle or db2 . The engine name is required. Because the SAS/ACCESS LIBNAME statement associates a libref with a SAS/ACCESS engine that supports connections to a particular DBMS, it requires a DBMS-specific engine name. See the DBMS-specific reference section for details.

SAS/ACCESS-connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS; these arguments are different for each database. For example, to connect to an Oracle database, your connection options are USER=, PASSWORD=, and PATH=:

libname myoralib oracle user=testuser password=testpass path='voyager';

If the connection options contain characters that are not allowed in SAS names, enclose the values of the arguments in quotation marks. On some DBMSs, if you specify the appropriate system options or environment variables for your database, you can omit the connection options. For detailed information about connection options for your DBMS, see the reference section for your SAS/ACCESS interface.

SAS/ACCESS-LIBNAME-options

define how DBMS objects are processed by SAS. Some LIBNAME options can enhance performance; others determine locking or naming behavior. For example, the PRESERVE_COL_NAMES= option lets you specify whether to preserve spaces, special characters, and mixed case in DBMS column names when creating tables. The availability and default behavior of many of these options are DBMS-specific. For a list of the LIBNAME options that are available for your DBMS, see the reference section for your SAS/ACCESS interface. For more information about LIBNAME options, see LIBNAME Options for Relational Databases.

CLEAR

disassociates one or more currently assigned librefs.

Specify libref to disassociate a single libref. Specify _ALL_ to disassociate all currently assigned librefs.

_ALL_

specifies that the CLEAR or LIST argument applies to all currently assigned librefs.

LIST

writes the attributes of one or more SAS/ACCESS libraries or SAS libraries to the SAS log.

Specify libref to list the attributes of a single SAS/ACCESS library or SAS library. Specify _ALL_ to list the attributes of all libraries that have librefs in your current session.

Details

Using Data from a DBMS

You can use a LIBNAME statement to read from and write to a DBMS table or view as if it were a SAS data set.

For example, in MYDBLIB.EMPLOYEES_Q2, MYDBLIB is a SAS libref that points to a particular group of DBMS objects, and EMPLOYEES_Q2 is a DBMS table name. When you specify MYDBLIB.EMPLOYEES_Q2 in a DATA step or procedure, you dynamically access the DBMS table. SAS supports reading, updating, creating, and deleting DBMS tables dynamically.

Disassociating a Libref from a SAS Library

To disassociate or clear a libref from a DBMS, use a LIBNAME statement. Specify the libref (for example, MYDBLIB) and the CLEAR option as shown here:

libname mydblib CLEAR;

You can clear a single specified libref or all current librefs.

The database engine disconnects from the database and closes any free threads or resources that are associated with that libref's connection.

Writing SAS Library Attributes to the SAS Log

Use a LIBNAME statement to write the attributes of one or more SAS/ACCESS libraries or SAS libraries to the SAS log. Specify libref to list the attributes of a single SAS/ACCESS library or SAS library, as follows:

libname mydblib LIST;

Specify _ALL_ to list the attributes of all libraries that have librefs in your current session.

SQL Views with Embedded LIBNAME Statements

With SAS software, you can embed LIBNAME statements in the definition of an SQL view. This means that you can store a LIBNAME statement in an SQL view that contains all information that is required to connect to a DBMS. Whenever the SQL view is read, PROC SQL uses the embedded LIBNAME statement to assign a libref. After the view has been processed, PROC SQL de-assigns the libref.

In this example, an SQL view of the Oracle table DEPT is created. Whenever you use this view in a SAS program, the ORALIB library is assigned. The library uses the connection information (user name, password, and data source) that is provided in the embedded LIBNAME statement.

proc sql;
    create view sasuser.myview as
       select dname from oralib.dept
          using libname oralib oracle
                user=scott pw=tiger datasrc=orsrv;
quit;

Note: You can use the USING LIBNAME syntax to embed LIBNAME statements in SQL views. For more information about the USING LIBNAME syntax, see the PROC SQL topic in the Base SAS Procedures Guide. [cautionend]

Assigning a Libref with a SAS/ACCESS LIBNAME Statement

The following statement creates a libref, MYDBLIB, that uses the SAS/ACCESS interface to DB2:

libname mydblib db2 ssid=db2a authid=testid server=os390svr;

The following statement associates the SAS libref MYDBLIB with an Oracle database that uses the SQL*Net alias AIRDB_REMOTE. You specify the SCHEMA= option on the SAS/ACCESS LIBNAME statement to connect to the Oracle schema in which the database resides. In this example Oracle schemas reside in a database.

libname mydblib oracle user=testuser password=testpass
        path=airdb_remote schema=hrdept;

The AIRDB_REMOTE database contains a number of DBMS objects, including several tables, such as STAFF. After you assign the libref, you can reference the Oracle table like a SAS data set and use it as a data source in any DATA step or SAS procedure. In the following SQL procedure statement, MYDBLIB.STAFF is the two-level SAS name for the STAFF table in the Oracle database AIRDB_REMOTE:

proc sql; 
   select idnum, lname
      from mydblib.staff
      where state='NY'
      order by lname;
quit;

You can use the DBMS data to create a SAS data set:

data newds;
   set mydblib.staff(keep=idnum lname fname);
run;

You can also use the libref and data set with any other SAS procedure. This statement prints the information in the STAFF table:

proc print data=mydblib.staff;
run;

This statement lists the database objects in the MYDBLIB library:

proc datasets library=mydblib; 
quit;

Using the Prompting Window When Specifying LIBNAME Options

The following statement uses the DBPROMPT= LIBNAME option to cause the DBMS connection prompting window to appear and prompt you for connection information:

libname mydblib oracle dbprompt=yes;

When you use this option, you enter connection information into the fields in the prompting window rather than in the LIBNAME statement.

You can add the DEFER=NO LIBNAME option to make the prompting window appear at the time that the libref is assigned rather than when the table is opened:

libname mydblib oracle dbprompt=yes defer=no;

Assigning a Libref to a Remote DBMS

SAS/CONNECT (single-user) and SAS/SHARE (multiple user) software give you access to data by means of remote library services (RLS). RLS lets you access your data on a remote machine as if it were local. For example, it permits a graphical interface to reside on the local machine while the data remains on the remote machine.

This access is given to data stored in many types of SAS files. Examples include external databases (through the SAS/ACCESS LIBNAME statement and views that are created with it) and SAS data views (views that are created with PROC SQL, the DATA step, and SAS/ACCESS software). RLS lets you access SAS data sets, SAS views, and relational DBMS data that SAS/ACCESS LIBNAME statements define. For more information, see the discussion about remote library services in the SAS/SHARE User's Guide.

You can use RLS to update relational DBMS tables that are referenced with the SAS/ACCESS LIBNAME statement.

In the following example, the SAS/ACCESS LIBNAME statement makes a connection to a DB2 database that resides on the remote SAS/SHARE server REMOS390. This LIBNAME statement is submitted in a local SAS session. The SAS/ACCESS engine name is specified in the remote option RENGINE=. The DB2 connection option and any LIBNAME options are specified in the remote option ROPTIONS=. Options are separated by a blank space. RLSDB2.EMPLOYEES is a SAS data set that references the DB2 table EMPLOYEES.

libname rlsdb2 rengine=db2 server=remos390 
               roptions="ssid=db2a authid=testid";
                      
proc print data=rlsdb2.employees;
run;