Specifies the schema to use when accessing tables and views in a database.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Access |
| Aliases: | DATABASE= [Hadoop, Impala] |
| OWNER= [Greenplum, HAWQ, Microsoft SQL Server, SAP IQ, Snowflake] | |
| Default: | DBMS-specific |
| Requirements: | Google BigQuery: This LIBNAME option is required to access Google BigQuery. |
| Snowflake: This LIBNAME option is required to access Snowflake. | |
| 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, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick |
| Notes: | Support for Spark was added in SAS 9.4M7. |
| Support for Yellowbrick was added in SAS 9.4M7. | |
| See: | DBCONINIT= LIBNAME option, PRESERVE_TAB_NAMES= LIBNAME option, SCHEMA= data set 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.
To use this option, you must have the appropriate privileges to the specified schema.
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.
DB2
under z/OS: If you specify DBCONINIT="SET
CURRENT SQLID='user-ID'",
then any value that is specified for SCHEMA= is ignored.
Microsoft SQL Server: Starting in SAS 9.4M9, this option is now passed automatically to PROC FEDSQL.
|
DBMS |
Default |
|---|---|
|
Amazon Redshift Google BigQuery Greenplum HAWQ JDBC Microsoft SQL Server MySQL ODBC OLE DB PostgreSQL SAP HANA SAP IQ Vertica Yellowbrick |
none |
|
Aster |
none This uses the database user's default schema. However, the user name is used instead when the user's default schema is the user name. An example is when SQLTables is called to obtain a table listing using PROC DATASETS or SAS Explorer. |
|
DB2 |
none When SCHEMA= is not specified, the SAS/ACCESS engine for DB2 uses the user ID for operations like PROC DATASETS. |
|
Hadoop |
the Hive schema named |
|
Impala |
the Impala schema named |
|
Informix |
your user name Note: The SCHEMA= LIBNAME option is ignored when you are using implicit pass-through and the Informix engine. |
|
Netezza |
your default schema Note: Netezza supports three-part names in the form <database>.<schema>.<table>, so you can provide both DATABASE= and SCHEMA= in a Netezza LIBNAME statement. |
|
Oracle |
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. |
|
SAP ASE |
none You cannot use the SCHEMA= option when you use UPDATE_LOCK_TYPE=PAGE to update a table. |
|
Snowflake |
PUBLIC |
|
Spark |
the Spark schema named |
|
Teradata |
none You can use this option to point to a different database. Using the SCHEMA= option does not establish a physical connection to the specified schema. This option lets you view or modify another 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 any reference in SAS to mydb.employee as scott.employee.
libname mydb db2 SCHEMA=SCOTT; To access an Oracle object in another schema, use the SCHEMA= option, as in this example. The schema name is typically a user name or ID.
libname mydblib oracle user=myusr1
password=mypwd1 path='mysrv1' schema=john;In this example, the Oracle SCHEDULE table resides in the AIRPORTS schema and is specified as AIRPORTS.SCHEDULE. To access this table in PROC PRINT and still use the libref (CARGO) in the SAS/ACCESS LIBNAME statement, specify the schema in the SCHEMA= option. Then put in the libref.table the DATA statement for the procedure.
libname cargo oracle schema=airports user=myusr1 password=mypwd1
path="mysrv1";
proc print data=cargo.schedule;
run;In this Teradata example, the MYUSR1 user prints the Emp table, which is located in the OTHERUSER database.
libname mydblib teradata user=myusr1 pw=mypwd1 schema=otheruser;
proc print data=mydblib.emp;
run;