SCHEMA= LIBNAME Statement Option

Enables you to read database objects, such as tables, in the specified DBMS schema.

Valid in: LIBNAME statement
Default: data source-specific
Supports: DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, Teradata
Syntax
Details
Examples
Example 1: Use SCHEMA to Access a Table in Your Schema
Example 2: Use SCHEMA to Access Another User’s Schema
Example 3: Use SCHEMA to Access a DBMS Schema
Example 4: Use SCHEMA to Access a Teradata Database
See Also

Syntax

SCHEMA= schema-name

Details

If this option is omitted, you connect to the default schema for your data source.
The values for SCHEMA= can be case-sensitive, depending on the data source, so use care when you specify this option. You should set PRESERVE_TAB_NAMES=YES when the value for SCHEMA= contains mixed case characters.
Oracle Details: 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.
Teradata Details: 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 enables you to 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.) The Teradata alias for SCHEMA= is DATABASE=. For more information about changing the default database, see the DATABASE statement in your Teradata documentation.

Examples

Example 1: Use SCHEMA to Access a Table in Your Schema

In the following example, SCHEMA= causes any reference in SAS to MyDB.Employee to be interpreted by DB2 as Scott.Employee. 
libname mydb fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=db2dsn schema=SCOTT;

Example 2: Use SCHEMA to Access Another User’s Schema

To access an Oracle object in another schema, use the SCHEMA= option as in the following example. The schema name is typically a person's user name or ID. 
libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn schema=john;

Example 3: Use SCHEMA to Access a DBMS Schema

In the following example, the Oracle table Schedule resides in the AIRPORTS schema. To access this table using the PRINT procedure with the libref Cargo, you specify the DBMS schema in the SCHEMA= option. Then you specify Cargo.Schedule in the procedure's DATA statement. 
libname cargo fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn schema=airports;
proc print data=cargo.schedule;
run;

Example 4: Use SCHEMA to Access a Teradata Database

In the following Teradata example, the user Id TESTUSER prints the Emp table, which is located in the OTHERUSER database. 
libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=teradsn schema=otheruser;
proc print data=mydblib.emp;
run;

See Also

To apply this option to an individual table, use the SCHEMA= data set option.
Copyright © SAS Institute Inc. All Rights Reserved.