SCHEMA= Data Set Option

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

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Alias:	DATABASE= [Teradata]
Default:	LIBNAME option [Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Informix, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ], AUTHID= [DB2 under z/OS]
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:	PRESERVE_TAB_NAMES= LIBNAME option, SCHEMA= 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 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 be careful when you specify this option.

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

Oracle: The default is the LIBNAME setting. 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.

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

Teradata: The default is the LIBNAME setting. 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 MYDB.TEMP_EMPS as SCOTT.TEMP_EMPS.

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

In this next example, SAS sends any reference to Employees as Scott.Employees.

libname mydblib oracle user=testuser password=testpass path="myorapath";
proc print data=employees (schema=scott);
run;

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

libname mydblib teradata user=testuser pw=testpass;
proc print data=mydblib.employees(schema=donna);
run;