Data Set Options for Relational Databases PRESERVE_COL_NAMES= Data Set Option 201
DBMS support: DB2 OS/390, DB2 UNIX/PC, Informix, ODBC, OLE DB, Oracle,
Microsoft SQL Server
Default value:
LIBNAME setting
Syntax
PRESERVE_COL_NAMES=NO | YES
Syntax Description
NO
specifies that column names that are used in DBMS table creation are derived from
SAS variable names by using the SAS variable name normalization rules (see the
VALIDVARNAME system option for more information). However, the database
applies its DBMS-specific normalization rules to the SAS variable names when it
creates the DBMS 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 DBMS
with special characters and the exact, case-sensitive spelling of the name preserved.
In the SAS/ACCESS interface to Teradata, YES is the only supported value for
this option.
Details
This option applies only when you use SAS/ACCESS to create a new DBMS table. When
you create a table, you assign the column names by using one of the following methods:
To control the case of the DBMS column names, specify variables 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. For more information,
see the section about names in
SAS/ACCESS for Relational Databases: Reference
and the section on system options in SAS Language Reference: Dictionary.
To allow the DBMS to normalize the column names according to its naming
conventions, specify variables with any case and set
PRESERVE_COLUMN_NAMES= NO.
Note: When you use SAS/ACCESS to 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 variable does not matter.
For more information, see the topic about naming in the documentation for your
SAS/ACCESS interface.
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:
