PRESERVE_COL_NAMES= LIBNAME Statement Option

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

Valid in:	LIBNAME statement (when you create tables)
Default:	NO
Supports:	All

Syntax

Syntax Description

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 VALIDVARNAME= System Option.) However, the data source applies its specific normalization rules to the SAS column names when creating the column names.

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

If you name a column with an SQL or FedSQL reserved word, an error message is issued.

YES

specifies that column names that are used in table creation are 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.

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 using the desired case and set PRESERVE_COL_NAMES=YES. If you use special symbols or blanks, you must set VALIDVARNAME= to ANY and use name literals.
To enable the DBMS to normalize the column names according to its naming conventions, specify columns using any case and set PRESERVE_COLUMN_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 name does not matter.

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 programming.

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;
```

If you are creating a table in PROC SQL, you must also include the PRESERVE_COL_NAMES=YES option in your LIBNAME statement. For example:

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

For more information about using a SAS Federation Server reserved word as a column name, see Reserved Language Keywords.

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

PRESERVE_COL_NAMES= LIBNAME Statement Option

Syntax

Syntax Description

NO

YES

Details

See Also