The LIBNAME Statement for Relational Databases

SCHEMA= LIBNAME Option

Allows reading of such database objects as tables and views in the specified schema.

Default value:	DBMS-specific
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

SCHEMA=schema-name

Syntax Description

schema-name: specifies the name that is assigned to a logical classification of objects in a relational database.

Details

For this option to work, you must have appropriate privileges to the schema that is specified.

If you do not specify this option, you connect to the default schema for your DBMS.

The values for SCHEMA= are usually case sensitive, so use care when you specify this option.

Aster nCluster: The default is none, which uses the database user's default schema. However, the user name is used instead when the user's default scheme is the user name--for example, when SQLTables is called to get a table listing using PROC DATASETS or SAS Explorer.

Oracle: Specify a schema name to be used when referring to database objects. SAS can access another user's database objects by using a specified schema name. If PRESERVE_TAB_NAMES=NO, SAS converts the SCHEMA= value to uppercase because all values in the Oracle data dictionary are uppercase unless quoted.

Sybase: You cannot use the SCHEMA= option when you use UPDATE_LOCK_TYPE=PAGE to update a table.

Teradata: If you omit this option, a libref points to your default Teradata database, which often has the same name as your user name. You can use this option to point to a different database. This option lets you view or modify a different user's DBMS tables or views if you have the required Teradata privileges. (For example, to read another user's tables, you must have the Teradata privilege SELECT for that user's tables.) For more information about changing the default database, see the DATABASE statement in your Teradata documentation.

Examples

In this example, SCHEMA= causes DB2 to interpret any reference in SAS to mydb.employee as scott.employee.

 libname mydb db2 SCHEMA=SCOTT;

To access an Oracle object in another schema, use the SCHEMA= option as shown in this example. The schema name is typically a user name or ID.

libname mydblib oracle user=testuser
        password=testpass path='hrdept_002' schema=john;

In this next example, the Oracle SCHEDULE table resides in the AIRPORTS schema and is specified as AIRPORTS.SCHEDULE. To access this table in PROC PRINT and still use the libref (CARGO) in the SAS/ACCESS LIBNAME statement, you specify the schema in the SCHEMA= option and then put the libref.table in the DATA statement for the procedure.

libname cargo oracle schema=airports user=testuser password=testpass 
        path="myorapath";
proc print data=cargo.schedule;
run;

In this Teradata example, the testuser user prints the emp table, which is located in the otheruser database.

libname mydblib teradata user=testuser pw=testpass schema=otheruser;
proc print data=mydblib.emp;
run;