SCHEMA= LIBNAME Option

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

Valid in:	SAS/ACCESS LIBNAME statement
Default:	DBMS-specific
Data source:	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
See:	SCHEMA= data set option, PRESERVE_TAB_NAMES= LIBNAME option

Syntax

Details

Example

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 the appropriate privileges to the specified schema.

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. An example is when SQLTables is called to obtain 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.

Example

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 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 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, specify the schema in the SCHEMA= option and then put in the libref.table 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;