SAS/ACCESS Software for Relational Databases: Reference

DBMS Column Names to SAS Variable Names

The SAS system option VALIDVARNAME=V7 is the default value for your SAS session unless you set this option to a different value. (VALIDVARNAME=V7 applies to Version 7 and later of SAS software.) Therefore, to change the standard SAS rules for names, you must set one of the following options: VALIDVARNAME=ANY, PRESERVE_TAB_NAMES=YES, PRESERVE_COL_NAMES=YES, or PROC SQL DQUOTE=ANSI.

If the aforementioned options are not set, the following rules apply when you map DBMS column names to SAS variable names:

Characters that are not standard in SAS names (such as @ and #) that appear in DBMS column names are changed to underscores in SAS variable names. For example, the DBMS column name MY$DEPT becomes SAS variable name MY_DEPT.
SAS makes DBMS column names into unique SAS variable names by appending a number (starting with 0) to the variable name when they are changed to conform with SAS rules. For example, DBMS column names MY$DEPT, My$Dept, and my$dept become SAS variable names MY_DEPT, MY_Dept0, and MY_DEPT1.

The following two tables describe how SAS processes DBMS names when it is retrieving DBMS data. This information applies generally to the DBMS names; see your DBMS chapter for possible exceptions. See Naming Examples for examples that illustrate the different kinds of naming actions and defaults.

DBMS Column to SAS Variable Names When Reading DBMS Data
If your DBMS column name is a... ...and you want this SAS variable name... ...Then use these LIBNAME, PROC SQL, or System Options (table note 1)

Case-sensitive DBMS column name, such as Flight Default SAS variable name (uppercase), such as FLIGHT preserve_col_names=no

DBMS column name with characters that are not valid in SAS names, such as My$Flight Default SAS variable name (uppercase) where an underscore replaces the invalid characters, such as MY_FLIGHT preserve_col_names=no

Case-sensitive DBMS column name, such as Flight Case-sensitive SAS variable name, such as Flight preserve_col_names=yes

DBMS column name with characters that are not valid in SAS names, such as My$Flight Case-sensitive SAS variable name where an underscore replaces the invalid characters, such as My_Flight preserve_col_names=yes

DBMS column name with characters that are not valid in SAS names, such as My$Flight Nonstandard, case-sensitive SAS variable name, such as My$Flight proc sql dquote=ansi and preserve_col_names=yes or, in a DATA or PROC step, use a SAS name literal such as 'My$Flight'n and preserve_col_names=yes validvarname=any

***DBMS Column to SAS Variable Names When Reading DBMS Data***
If your DBMS column name is a...	...and you want this SAS variable name...	...Then use these LIBNAME, PROC SQL, or System Options (table note 1)
Case-sensitive DBMS column name, such as `Flight`	Default SAS variable name (uppercase), such as `FLIGHT`	`preserve_col_names=no`
DBMS column name with characters that are not valid in SAS names, such as `My$Flight`	Default SAS variable name (uppercase) where an underscore replaces the invalid characters, such as `MY_FLIGHT`	`preserve_col_names=no`
Case-sensitive DBMS column name, such as `Flight`	Case-sensitive SAS variable name, such as `Flight`	`preserve_col_names=yes`
DBMS column name with characters that are not valid in SAS names, such as `My$Flight`	Case-sensitive SAS variable name where an underscore replaces the invalid characters, such as `My_Flight`	`preserve_col_names=yes`
DBMS column name with characters that are not valid in SAS names, such as `My$Flight`	Nonstandard, case-sensitive SAS variable name, such as `My$Flight`	`proc sql dquote=ansi` and `preserve_col_names=yes` or, in a DATA or PROC step, use a SAS name literal such as `'My$Flight'n` and `preserve_col_names=yes validvarname=any`

TABLE NOTE 1:

These options might not be required. Default values for these options are DBMS-specific.

DBMS Table to SAS Data Set Names When Reading DBMS Data
If your DBMS table name is a ... ...And you want this SAS data set name... ...Then use these LIBNAME, PROC SQL, or System Options (table note 1)

Default DBMS table name, such as STAFF Default SAS data set or member name (uppercase), such as STAFF preserve_tab_names=no

Case-sensitive DBMS table name, such as Staff Case-sensitive SAS data set, such as Staff preserve_tab_names=yes

DBMS table name with characters that are not valid in SAS names, such as All$Staff Nonstandard, case-sensitive SAS data set name, such as All$Staff proc sql dquote=ansi and preserve_tab_names=yes or, in a DATA step or PROC, use a SAS name literal such as 'All$Staff'n and preserve_tab_names=yes

***DBMS Table to SAS Data Set Names When Reading DBMS Data***
If your DBMS table name is a ...	...And you want this SAS data set name...	...Then use these LIBNAME, PROC SQL, or System Options (table note 1)
Default DBMS table name, such as `STAFF`	Default SAS data set or member name (uppercase), such as `STAFF`	`preserve_tab_names=no`
Case-sensitive DBMS table name, such as `Staff`	Case-sensitive SAS data set, such as `Staff`	`preserve_tab_names=yes`
DBMS table name with characters that are not valid in SAS names, such as `All$Staff`	Nonstandard, case-sensitive SAS data set name, such as `All$Staff`	`proc sql dquote=ansi` and `preserve_tab_names=yes` or, in a DATA step or PROC, use a SAS name literal such as `'All$Staff'n` and `preserve_tab_names=yes`

TABLE NOTE 1:

These options might not be required. Default values for these options are DBMS-specific.

Chapter Contents
Previous
Next
Top of Page