Specifies the schema to use when accessing tables and views in a database.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Category: | Data Access |
| Aliases: | DATABASE= [Impala] |
| OWNER= [Greenplum, HAWQ, Microsoft SQL Server, SAP IQ, Snowflake] | |
| Default: | LIBNAME option value [Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, Microsoft SQL Server, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP ASE, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick] |
| AUTHID= [DB2 under z/OS] | |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | DBCONINIT= LIBNAME option, PRESERVE_TAB_NAMES= LIBNAME option, SCHEMA= LIBNAME option |
Table of Contents
specifies the name that is assigned to a logical classification of objects in a relational database.
If the schema name contains spaces or non-alphanumeric characters, enclose the value in quotation marks.
For this option to work, you must have appropriate privileges to access the schema that is specified.
If you do not specify this option, you connect to any schema in the default database for your DBMS. When you specify SCHEMA=, this option acts as a filter to access only the tables and views that belong to that schema.
Aster: The
default is none, which uses the database
user's default schema. When the user's default schema 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.
DB2
under z/OS: If you specify DBCONINIT="SET
CURRENT SQLID='user-ID'",
then any value that is specified for SCHEMA= is ignored.
Informix: The SCHEMA= data set option disables implicit pass-through.
Netezza: Starting in Netezza 7.0.3, you can configure your Netezza server for multiple schema support. The Netezza System Administrator’s Guide describes how to provision schema support. Multiple schema support is not enabled by default. The default is the database user’s default schema.
Oracle: The default is the LIBNAME value. 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.
SAP ASE: You cannot use the SCHEMA= option when you use UPDATE_LOCK_TYPE=PAGE to update a table.
Teradata: The default is the LIBNAME value, if specified. You can use this option to point to a database that is different from your default database. Using the SCHEMA= option does not establish a physical connection to the specified schema. 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.
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=myusr1 password=mypwd1 path="myorapath";
proc print data=employees (schema=scott);
run;In this example, user MYUSR1 prints the contents of the Employees table, which is located in the Donna database.
libname mydblib teradata user=myusr1 pw=mypwd1;
proc print data=mydblib.employees(schema=donna);
run;