PRESERVE_TAB_NAMES= LIBNAME Statement Option

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

Valid in:	LIBNAME statement
Default:	NO
Supports:	All

Syntax

Details

Example

Syntax

PRESERVE_TAB_NAMES=NO | YES

Syntax Description

NO: specifies that when you create tables or refer to an existing table, the table names are derived from SAS member names by using SAS member name normalization. However, most databases apply 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.

If you use an SQL or DataFlux FedSQL language reserved word as a table name, an error is issued.

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, the number of tables that do not display from PROC DATASETS due to this restriction is noted as a note. Here is an example:

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

You will 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.

YES: specifies that table names are read from and passed to the data source with any special characters, reserved words, and the exact, case-sensitive spelling of the name preserved. For Teradata, YES is the only supported value for this option. For SAS data sets, you need this option only if you are using an SQL reserved word as a table name. In all other cases, a valid SAS name must be used.

Details

To use table names in your SAS program that are not valid SAS names, use one of the following 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 fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1 dsn=oradsn;
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 fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn preserve_tab_names=yes;
proc print data=mydblib.'my table'n;
run;

To use a DataFlux Federation Server reserved word with DataFlux FEDSQL language or PROC SQL, see Reserved Language Keywords.

Specify the alias PRESERVE_NAMES= to save time if you are specifying both PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= in your LIBNAME statement.

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

Example: Results of PRESERVE_TAB_NAMES=YES versus PRESERVE_TAB_NAMES=NO

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 is not displayed because it is not in Oracle normalized form. 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 Statement Option

Syntax

Syntax Description

Details

Example: Results of PRESERVE_TAB_NAMES=YES versus PRESERVE_TAB_NAMES=NO

See Also