PRESERVE_COL_NAMES= Data Set Option

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

Valid in:	DATA and PROC steps (when creating DBMS tables using SAS/ACCESS software).
Category:	Data Set Control
Alias:	QUOTE_NAMES= [Impala, SAP IQ]
Default:	LIBNAME option value
Interaction:	If you use the DS2 or FedSQL language, quoting and casing of names is different. For more information, see the identifiers topic in SAS DS2 Language Reference or SAS FedSQL Language Reference.
Data source:	Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick
Note:	Support for Spark and Yellowbrick was added in SAS 9.4M7.
See:	PRESERVE_COL_NAMES= LIBNAME option, PRESERVE_TAB_NAMES= LIBNAME option, VALIDVARNAME= system option, SAS Names and Support for DBMS Names, and the DBMS-specific naming conventions sections for your SAS/ACCESS interface

Table of Contents

Syntax

PRESERVE_COL_NAMES=YES | NO

Syntax Description

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 are preserved.

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 invalid when DBMS normalization rules are applied. To include nonstandard SAS symbols or database keywords, specify PRESERVE_COL_NAMES=YES.

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:

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 specify VALIDVARNAME=ANY and use name literals. For more information, see the naming topic in this document and also SAS Data Set Options: Reference.
SAP HANA: When you specify PRESERVE_COL_NAMES=YES, you can use reserved words for column names.
To enable the DBMS to normalize the column names according to its naming conventions, specify variables with any case and set PRESERVE_COL_NAMES=NO.

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 use column names in your SAS program that are not valid SAS names, you must use one of these techniques.

Use the DQUOTE= option in PROC SQL and reference your columns using double quotation marks. Here is an example.
```
proc sql dquote=ansi;
   select "Total$Cost" from mydblib.mytable;
```
Specify the global VALIDVARNAME=ANY system option and use name literals in the SAS language. Here is an 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. Here is an example.

libname mydblib hadoop user=myusr1 password=mypwd1 server=hadoopsvr;
   proc sql dquote=ansi;
     create table mydblib.mytable (preserve_col_names=yes) ("my$column" int);

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

Last updated: February 3, 2026