Associates a SAS libref with a database management system (DBMS)
database, schema, server, or group of tables or views
| Valid: |
Anywhere
|
| Category: |
Data Access
|
| Required: |
You must license SAS/ACCESS software in order to use the LIBNAME statement
to access data that are stored in a DBMS file.
|
![[1]]( ../common/images/sym01.gif) LIBNAME libref SAS/ACCESS-engine-name
<SAS/ACCESS-engine-connection-options>
<SAS/ACCESS-engine-LIBNAME-options>;
|
![[2]]( ../common/images/sym02.gif) LIBNAME libref CLEAR | _ALL_
CLEAR;
|
![[3]]( ../common/images/sym03.gif) LIBNAME libref LIST | _ALL_
LIST;
|
![[4]]( ../common/images/sym04.gif) LIBNAME libref <SAS/ACCESS-engine> (library-specification-1
<. . . library-specification-n>)
< options > ;
|
-
libref
-
is a shortcut or a "nickname"
for the DBMS database, schema, or server where your tables and views are stored.
It is any SAS name when you are assigning a new libref. When you are disassociating
a currently-assigned libref or when you are listing attributes with the LIBNAME
statement, specify a libref that was previously assigned with a LIBNAME statement.
-
SAS/ACCESS-engine-name
-
is a SAS/ACCESS engine name for your DBMS,
such as ORACLE or DB2. DBMS engines may be implemented differently in different
operating environments. See SAS/ACCESS Software for Relational Databases:
Reference.
-
CLEAR
-
disassociates one or more 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 data libraries to the SAS log.
-
SAS/ACCESS-engine-connection-options
-
are options that you specify in order to
connect to a particular database; these options are different for each database.
For example, to connect to a database through ODBC, you specify your user
name, password, data source, and other options. Enclose the SAS/ACCESS-engine-connection-options
in quotation marks if they contain characters that are not allowed in SAS
names.
-
SAS/ACCESS-engine-LIBNAME-options
-
specify actions that apply to the processing
of the DBMS's tables. For example, SPOOL= specifies whether SAS creates
a utility spool file during read transactions that read data more than once.
If you have a
license for SAS/ACCESS software,
you can use a LIBNAME statement to read from and write to a DBMS table or
view, as though it were in a SAS data set. The LIBNAME statement associates
a libref with a SAS/ACCESS engine in order to access tables or views in a
DBMS. The SAS/ACCESS engine enables you to connect to a particular DBMS and,
therefore, to specify a DBMS table or view name in a two-level SAS name.
For example, consider this PROC step:
proc print data=mylib.employees_q2;
run;
MYLIB is a SAS libref that points to a particular DBMS, and EMPLOYEES_Q2
is a DBMS table name. When you specify MYLIB.EMPLOYEES_Q2 in a DATA step
or PROC step, you dynamically access the DBMS table. The SAS System now supports
reading, updating, and creating DBMS tables. See the SAS/ACCESS documentation
for more information.
To disassociate a libref from a SAS/ACCESS library or
a SAS data library, use a LIBNAME statement, specifying the libref and the
CLEAR option. You can clear a single specified libref or all current librefs.
|
LIBNAME libref CLEAR | _ALL_
CLEAR;
|
Use a LIBNAME statement to write the attributes of one
or more SAS/ACCESS libraries or SAS data libraries to the SAS log. Specify libref to list the attributes of a single SAS/ACCESS library
or SAS data library. Specify _ALL_ to list the attributes of all libraries
that have librefs in your current session
|
LIBNAME libref LIST | _ALL_ LIST;
|
When you logically concatenate two or more SAS data
libraries, you can reference them all with one libref. You can specify a library
with its physical pathname or its previously assigned libref.
|
LIBNAME libref
<SAS/ACCESS-engine> (library-specification-1 <. . . library-specification-n>)
< options > ;
|
|
In the same LIBNAME statement you can use any combination
of specifications: librefs, physical pathnames, or a combination of librefs
and pathnames. See Logically Concatenating SAS Data Libraries.
Also see Rules for Library Concatenation.
Use the LIBNAME
statement to reference a SAS data library or a DBMS. Use the FILENAME statement
to reference an external file, such as a text or ASCII file you are reading
data from or writing a report to.
In this example, the libref
MYLIB uses the ORACLE engine to connect to an ORACLE database. The SAS/ACCESS-engine-connection-options are USER=, PASSWORD=,
and PATH=, where PATH= specifies an alias for the database name.
libname mylib oracle user=scott password=tiger
path="blunzer:v7" read_lock_type=row;
proc print data=mylib.all_employees;
where state='CA';
run;
The READ_LOCK_TYPE= option in the LIBNAME statement
applies to reading a DBMS table or view. When ROW is specified, the Oracle
"SHARE" row lock is in effect while you are reading the table.
You can also specify the database engine name and connection
options with macros. Here a DATA step view is created from the DB2 table,
DEPT:
%let dbmseng= db2;
%let con = ssid=db2a server=servr7;
libname mylib &dbmseng &con connection=sharedread;
data myview2/view=myview2;
set mylib.dept(drop=deptno);
where balance > 10000;
run;
Note that you can specify the DROP= data set option
after the DB2 table MYLIB.DEPT, just as you can specify DROP= after any SAS
data set. The new DATA step view MYVIEW2 references the same columns as MYLIB.DEPT
except for the dropped DEPTNO column.
In this example,
the SQL procedure is used to join two tables in a database that is accessed
through ODBC. By using the DQUOTE= option in the PROC SQL statement, you
can specify and rename DBMS column names that otherwise would not be valid
SAS names.
%let dbmseng = odbc;
%let con = user=josuha password=freude
datasrc="Jo's Data";
libname dbms1 &dbmseng &con;
proc sql dquote=ansi;
select first.work_id, first."@lastname" as lastname,
second."birth date" as birthdate
from dbms1.employees1 as first,
dbms1.employees2 as second
where first.work_id=second.work_id;
The SQL procedure has many enhancements in Version 7.
For more information, see the SAS/ACCESS documentation and "The SQL
Procedure" in the SAS Procedures Guide.
Copyright © 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.
