DBGEN_NAME= Data Set Option

Specifies how SAS automatically renames columns (when they contain characters that SAS does not allow, such as $) to valid SAS variable names.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	DBMS
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, Sybase IQ, Teradata
See:	DBGEN_NAME= LIBNAME option, VALIDVARNAME= system option

Syntax

Details

Example

Syntax

DBGEN_NAME=DBMS | SAS

Syntax Description

DBMS: specifies that SAS renames DBMS columns to valid SAS variable names. SAS converts any disallowed characters to underscores. If it converts a column to a name that already exists, it appends a sequence number at the end of the new name.

SAS: specifies that SAS converts DBMS columns with disallowed characters into valid SAS variable names. SAS uses the format _COLn, where n is the column number, starting with 0. If SAS converts a name to a name that already exists, it appends a sequence number at the end of the new name.

Details

SAS retains column names when it reads data from DBMS tables unless a column name contains characters that SAS does not allow, such as $ or @. SAS allows alphanumeric characters and the underscore (_).

This option is intended primarily for National Language Support, notably converting kanji to English characters. English characters that are converted from kanji are often those that SAS does not allow. Although this option works for the single-byte character set (SBCS) version of SAS, SAS ignores it in the double-byte character set (DBCS) version. So if you have the DBCS version, you must first set VALIDVARNAME=ANY before using your language characters as column variables.

In SAS 6, each SAS/ACCESS interface handled name collisions differently: Some appended at the end of the name, some replaced one or more final characters in the name, some used a single sequence number, and still others used unique counters. When you specify VALIDVARNAME=V6, SAS handles name collisions as it did in SAS 6.

Example

If you specify DBGEN_NAME=SAS, SAS renames a DBMS column named Dept$Amt to _COLn. If you specify DBGEN_NAME=DBMS, SAS renames the Dept$Amt column to Dept_Amt.