The LIBNAME Statement for Relational Databases

PRESERVE_TAB_NAMES= LIBNAME Option

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

Alias:	PRESERVE_NAMES= (see "Details")
Default value:	DBMS-specific
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	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

Syntax
Syntax Description
Details
Example
See Also

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), the tables whose names do not conform to the SAS member name normalization rules do not appear in the output. In SAS line mode, here is how SAS indicates the number of tables that do not display from PROC DATASETS because of this restriction:

Note: "Due to the PRESERVE_TAB_NAMES=NO LIBNAME option setting, 12 table(s) have not been displayed." [cautionend]

You do not get this warning when using 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. For 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. For 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, then 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 does not display because it is not in a form that is normalized for Oracle, and 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, then the PROC DATASETS output includes all three table names, My_Table, MY_TABLE, and MY TABLE.

PRESERVE_TAB_NAMES= LIBNAME Option

Syntax

Syntax Description