PRESERVE_TAB_NAMES= LIBNAME Statement Option

Preserves spaces, special characters, and case sensitivity in DBMS table names.

Valid in:	SAS/ACCESS LIBNAME statement
Category:	Data Set Control
Alias:	PRESERVE_NAMES= [see “Details”]
Default:	YES [Aster, DB2 under z/OS, Greenplum, HAWQ, Impala, Informix, Microsoft SQL Server, MySQL, OLE DB, SAP HANA, SAP IQ, Snowflake, Teradata, Yellowbrick]
	NO [Amazon Redshift, DB2 under UNIX and PC Hosts, Hadoop, JDBC, Netezza, Oracle, PostgreSQL, Spark, Vertica]
	Varies [ODBC, see “Details”]
Interaction:	If you use the DS2 or FedSQL language, quoting and casing of names is different. For more information, see the identifiers topic in SAS DS2 Language Reference or SAS FedSQL Language Reference.
Data source:	Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PC Files, PostgreSQL, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick
Notes:	Support for Spark was added in SAS 9.4M7.
Notes:	Support for Yellowbrick was added in SAS 9.4M7.
See:	PRESERVE_COL_NAMES= LIBNAME option, PRESERVE_TAB_NAMES= data set option, DBINDEX= data set option, SAS/ACCESS naming, SCHEMA= LIBNAME option, naming conventions in the DBMS-specific reference section for your SAS/ACCESS interface

Table of Contents

Syntax

PRESERVE_TAB_NAMES=YES | NO

Syntax Description

YES

specifies that table names are read from and passed to the DBMS with special characters, and the exact, case-sensitive spelling of the name is preserved.

SAP HANA: To use reserved words when naming a table for output to the database, you must specify PRESERVE_TAB_NAMES=YES.

NO

specifies that when you create DBMS tables or refer to an existing table, the table names are derived from SAS member names by using SAS member name normalization. However, the database applies DBMS-specific normalization rules to the SAS member names. Therefore, the table names are created or referenced in the database following the DBMS-specific normalization rules.

When you use SAS to read a list of table names, tables with names that do not conform to SAS member name normalization rules do not appear in output. In SAS line mode, here is how SAS indicates the number of tables that are not displayed from PROC DATASETS because of this restriction:

Due to the PRESERVE_TAB_NAMES=NO LIBNAME option value, 
12 table(s) have not been displayed.

You do not receive this warning when you use SAS Explorer. SAS Explorer displays DBMS table names in capitalized form when PRESERVE_TAB_NAMES=NO. This is now how the tables are represented in the DBMS.

NO is the default for most DBMS interfaces.

Details

To use table names in your SAS program that are not valid SAS names, use one of these techniques.

Use the PROC SQL option DQUOTE= and place double quotation marks around the table name. The libref must specify PRESERVE_TAB_NAMES=YES. Here is an example.
```
libname mydblib oracle user=myusr1 password=mypwd1
        preserve_tab_names=yes;
proc sql dquote=ansi;
   select * from mydblib."my table";
```
Use name literals in the SAS language. The libref must specify PRESERVE_TAB_NAMES=YES. Here is an example.
```
libname mydblib oracle user=myusr1 
        password=mypwd1 preserve_tab_names=yes;
proc print data=mydblib.'my table'n;
run;
```

To save some time when coding, specify the PRESERVE_NAMES= alias if you plan to specify both the PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= options in your LIBNAME statement.

Greenplum, HAWQ: Unless you specify PRESERVE_TAB_NAMES=YES, the table name that you enter is converted to lowercase.

Hadoop, Spark: Hive does not preserve case sensitivity and forces all identifiers to be lowercase. However, when PRESERVE_TAB_NAMES=NO (the default value), a Hive table name is modified by SAS to be uppercase to match SAS naming rules. If you want to preserve the case of the table name as it is in Hive, then specify PRESERVE_TAB_NAMES=YES. SAS then preserves lowercase table names for Hive tables.

ODBC: The default for ODBC depends on the case sensitivity of the underlying ODBC driver.

Oracle: Unless you specify PRESERVE_TAB_NAMES=YES, the table name that you enter for the SCHEMA= LIBNAME option or for the DBINDEX= data set option is converted to uppercase.

Example

If you use PROC DATASETS to read the table names in an Oracle database that contains three tables, My_Table, MY_TABLE, and MY TABLE. The results differ depending on the value of PRESERVE_TAB_NAMES.

If the libref specifies PRESERVE_TAB_NAMES=NO, the PROC DATASETS output is one table name, MY_TABLE. This is the only table name that is in Oracle normalized form (uppercase letters and a valid symbol, the underscore). My_Table is not displayed because it is not in a form that is normalized for Oracle. MY TABLE is not displayed because it is not in SAS member normalized form: The embedded space is a nonstandard SAS character.

If the libref specifies PRESERVE_TAB_NAMES=YES, the PROC DATASETS output includes all three table names: My_Table, MY_TABLE, and MY TABLE.

Last updated: February 3, 2026