The LIBNAME Statement for Relational Databases LIBNAME Statement Syntax for Relational Databases 67
using libname oralib oracle
user=scott pw=tiger datasrc=orsrv;
quit;
Note: The USING LIBNAME syntax is used to embed LIBNAME statements in
SQL views. For more information on the USING LIBNAME syntax, see the PROC SQL
topic in the Base SAS Procedures Guide.
Assigning a Libref with a SAS/ACCESS LIBNAME Statement
The following statement creates a libref, MYDBLIB, that uses the SAS/ACCESS
interface for 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= option to cause the DBMS
connection prompting window to appear and prompt you for connection information:
libname mydblib oracle dbprompt=yes;
