Data Set Options for Relational Databases

SCHEMA= Data Set Option

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

Alias:	DATABASE= [Teradata]
Default value:	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]
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
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 be careful when you specify this option.

Aster nCluster: The default is none, which uses the database user's default schema. However, 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--the user name is used instead.

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 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.) 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 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;