PRESERVE_COL_NAMES= Data Set Option

Preserves spaces, special characters, and case sensitivity in column names when you create tables.

Valid in:	DATA and PROC steps
Default:	LIBNAME statement setting
Supports:	All

Syntax

Details

Syntax

PRESERVE_COL_NAMES=NO | YES

Syntax Description

NO: specifies that column names that are used in table creation are derived from SAS column names by using the SAS column name normalization rules. (For more information, see the VALIDVARNAME= System Option .) However, the data source applies its specific normalization rules to the SAS column names when it creates the column names.

The use of N-Literals to create column names that use database keywords or special symbols other than the underscore character might be illegal when DBMS normalization rules are applied. To include nonstandard SAS symbols or database keywords, specify PRESERVE_COL_NAMES=YES.

YES: specifies that column names that are used in table creation are passed to the data source with special characters and the exact, case-sensitive spelling of the name preserved.

Teradata Details: YES is the only supported value for this option.

Details

This option applies only when you create a new table. When you create a table, you assign the column names by using one of the following methods:

To control the case of the column names, specify columns with the desired case and set PRESERVE_COL_NAMES=YES. If you use special symbols or blanks, you must set VALIDVARNAME=ANY and use N-Literals.
To enable the DBMS to normalize the column names according to its naming conventions, specify columns with any case and set PRESERVE_COL_NAMES=NO.

Note: When you read from, insert rows into, or modify data in an existing DBMS table, SAS identifies the database column names by their spelling. Therefore, when the database column exists, the case of the column does not matter.

For additional information, see the naming conventions topic in the appropriate data source reference in the DataFlux Federation Server Administrator’s Guide.

Specify the alias PRESERVE_NAMES= if you plan to specify both the PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= options in your LIBNAME statement. Using this alias saves you some time when coding.

To use column names in your SAS program that are not valid SAS names, you must use one of the following techniques:

Use the DQUOTE= option in PROC SQL and then reference your columns using double quotation marks. For example:
```
proc sql dquote=ansi;
   select "Total$Cost" from mydblib.mytable;
```
Specify the global system option VALIDVARNAME=ANY and use name literals in the SAS language. For example:
```
proc print data=mydblib.mytable;
   format 'Total$Cost'n 22.2;
```

Note that if you are creating a table in PROC SQL, you must also include the PRESERVE_COL_NAMES=YES option. For example:

libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd;
proc sql dquote=ansi;
   create table mydblib.mytable (preserve_col_names=yes) ("my$column" int);

PRESERVE_COL_NAMES= does not apply to the pass-through facility.