SCHEMA= Data Set Option

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

Valid in:	DATA and PROC steps
Default:	LIBNAME statement setting
Supports:	DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, Teradata

Syntax

Syntax Description

Details

Examples

Example 1: Specifying a Schema for a SAS Data Set

Example 2: Specifying a Schema for an Oracle Table

Example 3: Specifying a Schema for a Database

Syntax

SCHEMA=schema-name

Syntax Description

schema-name

is 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.

The values for SCHEMA= are usually case-sensitive, so be careful when you specify this option.

Oracle Details: If PRESERVE_TAB_NAMES=NO, SAS converts the SCHEMA= value to uppercase because all values in the Oracle data dictionary are converted to 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 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 interface alias for SCHEMA= is DATABASE=. For more information about changing the default database, see the DATABASE statement in your Teradata documentation.

Examples

Example 1: Specifying a Schema for a SAS Data Set

In the following example, SCHEMA= causes MyDB.Temp_Emps to be interpreted by DB2 as Scott.Temp_Emps.

proc print data=mydb.temp_emps (schema=SCOTT);
run;

Example 2: Specifying a Schema for an Oracle Table

In the following example, SAS sends any reference to Employees as Scott.Employees.

libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd;
proc print data=mydblib.employees (schema=scott);
run;

Example 3: Specifying a Schema for a Database

In the following example, user TESTUSER prints the contents of the Employees table, which is located in the Donna database.

libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=teradsn dsnuser=terauser dsnpwd=terapwd;
proc print data=mydblib.employees (schema=donna);
run;