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) |
|---|---|
| Category: | Data Set Control |
| Aliases: | DBMIXED= [Impala] |
| PRES_COL= [Impala] | |
| PRESERVE_NAMES= (see “Details”) | |
| QUOTE_NAMES= [Impala, SAP IQ] | |
| Default: | YES [Greenplum, HAWQ, Impala, MySQL, ODBC to Microsoft SQL Server, Snowflake, Teradata, Yellowbrick] |
| NO [Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Hadoop, Informix, JDBC, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Spark, Vertica] | |
| 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. | |
| 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 |
| Notes: | Support for Spark was added in SAS 9.4M7. |
| Support for Yellowbrick was added in SAS 9.4M7. | |
| See: | PRESERVE_COL_NAMES= data set option, SAS Names and Support for DBMS Names, VALIDVARNAME= system option |
Table of Contents
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.
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 invalid 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.
When you create a table, you assign the column names by using one of these methods.
SAP HANA: When you specify PRESERVE_COL_NAMES=YES, you can use reserved words for column names.
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.
Amazon Redshift, Hadoop, Spark: The SAS/ACCESS engine automatically converts all schema, table, and column names to lowercase.
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 that are not valid SAS names in your SAS program, you must use one of these techniques.
proc sql dquote=ansi;
select "Total$Cost" from mydblib.mytable;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=myusr1 password=mypwd1
preserve_col_names=yes;
proc sql dquote=ansi;
create table mydblib.mytable ("my$column" int);