Previous Page | Next Page

Data Set Options for Relational Databases

PRESERVE_COL_NAMES= Data Set Option



Preserves spaces, special characters, and case sensitivity in DBMS column names when you create DBMS tables.
Alias: PRESERVE_NAMES= (see "Details")
Default value: LIBNAME setting
Valid in: DATA and PROC steps (when creating DBMS tables using SAS/ACCESS software).
DBMS support: Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase IQ, Teradata

Syntax
Syntax Description
Details
See Also

Syntax

PRESERVE_COL_NAMES=YES | NO

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. (For more information see the VALIDVARNAME= system option.) However, the database applies its DBMS-specific normalization rules to the SAS variable names when it creates the DBMS 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 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.


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 these methods:

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 SAS/ACCESS naming topic in the DBMS-specific reference section for your interface.

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.

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

If you are creating a table in PROC SQL, you must also include the PRESERVE_COL_NAMES=YES option. Here is an example:
libname mydblib oracle user=testuser password=testpass;
   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.


See Also

To assign this option to a group of relational DBMS tables or views, see the naming in your interface for the PRESERVE_COL_NAMES= LIBNAME Option.

SAS Names and Support for DBMS Names

VALIDVARNAME= System Option

Previous Page | Next Page | Top of Page