PRESERVE_COL_NAMES= LIBNAME Option

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

Valid in:	SAS/ACCESS LIBNAME statement (when you create DBMS tables)
Alias:	PRESERVE_NAMES=
Default:	DBMS-specific
Restrictions:	This option applies only when you use SAS/ACCESS to create a new DBMS table. PRESERVE_COL_NAMES= does not apply to the SQL pass-through facility.
Data source:	Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase IQ, Teradata
See:	PRESERVE_COL_NAMES= data set option, SAS Names and Support for DBMS Names, VALIDVARNAME= system option

Syntax

Details

Syntax

PRESERVE_COL_NAMES=YES | NO

Syntax Description

NO: specifies that column names that are used to create DBMS tables are derived from SAS variable names (VALIDVARNAME= system option) by using the SAS variable name normalization rules. However, the database applies its DBMS-specific normalization rules to the SAS variable names when creating 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.

NO is the default for most DBMS interfaces.

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

Details

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 using the case that you want and set PRESERVE_COL_NAMES=YES. If you use special symbols or blanks, you must set VALIDVARNAME= to ANY and use N-literals. For more information, see the SAS/ACCESS naming topic in the DBMS-specific reference section for your interface in this document and also SAS Data Set Options: Reference.
To enable the DBMS to normalize the column names according to its naming conventions, specify variables using any case and set PRESERVE_COLUMN_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.

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.

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 system option VALIDVARNAME=ANY 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 in your LIBNAME statement. Here is an example.

libname mydblib oracle user=testuser password=testpass
        preserve_col_names=yes;
   proc sql dquote=ansi;
      create table mydblib.mytable ("my$column" int);