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.
| Tip: |
The association between
a libref and a DBMS database, schema, or server lasts only for the duration
of the SAS session or until you change it or discontinue it with another LIBNAME
statement. You may change a libref as often as you want. |
- 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.
| Requirement: |
To access data from
a DBMS table, you must specify SAS/ACCESS-engine-name. |
- CLEAR
- disassociates one or more currently assigned
librefs.
| Tip: |
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 data libraries to the SAS log.
| Tip: |
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. |
-
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.
| See: |
the SAS/ACCESS documentation
for documentation on these options. Support for many of these options is DBMS-specific. |
-
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.
| Interaction: |
Some SAS/ACCESS-engine-LIBNAME
options have an equivalent data set option. For an individual table, you can
override the value that is specified for the library in a LIBNAME statement
by using the corresponding data set option after the table name in a DATA
or PROC step. |
| See: |
the SAS/ACCESS documentation
for documentation on these options. Support for many of these options is DBMS-specific. |
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=. PATH= specifies an alias for the ORACLE driver, node, and database
names, as required by SQL*NET version 2.0 or later.
libname mylib oracle user=scott password=tiger
path="blunzer:v7" schema=hrdept;
proc print data=mylib.all_employees;
where state='CA';
run;
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.
| Statement:
|
| SAS/ACCESS
documentation for your
DBMS |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.
