PRESERVE_TAB_NAMES= LIBNAME Option

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

Valid in:	SAS/ACCESS LIBNAME statement
Alias:	PRESERVE_NAMES=
Default:	DBMS-specific
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase IQ, Teradata
See:	PRESERVE_TAB_NAMES= data set option, DBINDEX= data set option, SAS/ACCESS naming, SCHEMA= LIBNAME option. See also naming conventions for DB2 under UNIX and PC Hosts, DB2 under z/OS, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB , Oracle, Teradata

Syntax

Details

Example

Syntax

PRESERVE_TAB_NAMES=YES | NO

Syntax Description

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 (for example, in the SAS Explorer window), 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 setting, 
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.

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.

Details

For more information, see the SAS/ACCESS naming topic in the DBMS-specific reference section for your interface in this document.

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=testuser password=testpass
        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=testuser 
        password=testpass 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.

Oracle: Unless you specify PRESERVE_TAB_NAMES=YES, the table name that you enter for 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 setting 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.

To apply this option to an individual data set, see the naming in your DBMS interface for the PRESERVE_TAB_NAMES= data set option.