Supporting Case and Special Characters in Table and Column Names :: SAS(R) Data Integration Studio 4.9: User's Guide

Overview

The following topics describe how to support case and special characters in table and column names:

About Case and Special Characters in SAS Names
About Case and Special Characters in DBMS Names
Set Default Name Options for New Tables
Set Name Options in the Register Tables Wizard
Set Name Options for Registered Tables

About Case and Special Characters in SAS Names

Rules for SAS Names

By default, the names for SAS tables and columns must follow these rules:

Blanks cannot appear in SAS names.
The first character must be a letter (such as A through Z) or an underscore (_).
Subsequent characters can be letters, numeric digits (such as 0 through 9), or underscores.
You can use uppercase or lowercase letters. SAS processes names as uppercase, regardless of how you enter them.
Special characters are not allowed, except for the underscore. In filerefs you can use only the dollar sign ($), pound sign (#), and at sign (@).

The following SAS language elements have a maximum length of eight characters:

librefs and filerefs
SAS engine names and passwords
names of SAS/ACCESS access descriptors and view descriptors (to maintain compatibility with SAS 6 names)
variable names in SAS/ACCESS access descriptors and view descriptors

Beginning in SAS 7 software, SAS naming conventions have been enhanced to allow longer names for SAS data sets and SAS variables. The conventions also allow case-sensitive or mixed case names for SAS data sets and variables.

The following SAS language elements can now be up to 32 characters in length:

members of SAS libraries, including SAS data sets, data views, catalogs, catalog entries, and indexes
variables in a SAS data set macros and macro variables

For a complete description of the rules for SAS names, see the topic, "Names in the SAS Language" in SAS Language Reference: Concepts.

Case and Special Characters in SAS Names

By default, the names for SAS tables and columns must follow the rules for SAS names. However, SAS Data Integration Studio supports case-sensitive names for tables, columns, and special characters in column names if you specify the appropriate table options, as described in Set Name Options for Registered Tables or Set Default Name Options for New Tables. Double-byte character set (DBCS) column names are supported in this way, for example.

The DBMS name options apply to all SAS and DBMS table types, with a few exceptions for SAS tables. The following special rules apply to SAS tables:

Special characters are not supported in SAS table names.
Leading blanks are not supported for SAS column names and are removed if you used them.
Neither the External File wizards nor SAS/SHARE libraries and tables support case-sensitive names for SAS tables or special characters in column names. When you use these components, the names for SAS tables and columns must follow the standard rules for SAS names.

About Case and Special Characters in DBMS Names

Overview

You can access tables in a database management system (DBMS), such as Oracle or DB2, through a special SAS library that is called a database library. SAS Data Integration Studio cannot access a DBMS table with case-sensitive names or with special characters in names unless the appropriate DBMS name options are specified in both of these places:

in the metadata for the database library that is used to access the table
in the metadata for the table itself

For more information, see Enable Name Options for a New Database Library or Enable Name Options for an Existing Database Library. Use the following methods to avoid or fix problems with case-sensitive names or with special characters in names in DBMS tables.

DBMSs for Which Case and Special Characters are Supported

SAS Data Integration Studio generates SAS/ACCESS LIBNAME statements to access tables and columns that are stored in DBMSs. You should check your database to see whether it supports case-sensitive names and names with special characters.

Verify Case and Special Character Handling Options for Database Libraries

Perform the following steps to verify that the appropriate DBMS name options have been set for all database libraries where you want to support case and special character handling for tables:

Select the library that you want to verify. To easily locate libraries, you can expand the Libraries folder in the Inventory tree.
Right-click a database library and select Display LIBNAME from the pop-up menu. A SAS LIBNAME statement is generated for the selected library. In the LIBNAME statement, verify that both the Preserve DBMS table names option is set to YES and the Preserve column names as in the DBMS option have been set correctly.
If these options are not set correctly, update the metadata for the library, as described in Enable Name Options for an Existing Database Library.

Enable Name Options for a New Database Library

The following task describes how to specify name options for a new relational database library such as Oracle, Sybase, and Teradata. These name options ensure that table and column names are supported as they are in the DBMS. This task is typically done by an administrator. It is assumed that the appropriate database server has been installed and registered, and the appropriate database schema has been registered. For more information about database servers and schemas, see the chapters about common data sources in the SAS Intelligence Platform: Data Administration Guide. Perform the following steps to specify name options:

From the desktop, select NewLibrary. The New Library Wizard opens.
In the first window of the New Library wizard, select the appropriate kind of database library and click Next.
Enter a name for the library and click Next.
Enter a SAS LIBNAME for the library, and then click Advanced Options. The Advanced Options window displays.
In the Advanced Options window, click the Output tab. In the Preserve column names as in the DBMS field, select Yes.
Click OK and enter the rest of the metadata as prompted by the wizard.

Enable Name Options for an Existing Database Library

Perform the following steps to update the existing metadata for a database library in order to support table and column names as they exist in the DBMS:

In SAS Data Integration Studio, click the Inventory tab to display the Inventory tree.
In the Inventory tree, expand the folders until the Libraries folder is displayed.
Select the Libraries folder and then select the library for which metadata must be updated.
Select FileProperties from the menu bar. The properties window for the library displays.
In the properties window, click the Options tab.
On the Options tab, click Advanced Options. The Advanced Options window displays.
In the Advanced Options window, click the Output tab. In the Preserve column names as in the DBMS field, select Yes.
In the Advanced Options window, click the Input/Output tab. In the Preserve DBMS table names field, select Yes.
Click OK twice to save your changes.

Verify DBMS Name Options in Table Metadata

Perform the following steps to verify that the appropriate DBMS name options have been set for DBMS tables that are used in SAS Data Integration Studio jobs:

From the SAS Data Integration Studio desktop, select the Inventory tree.
In the Inventory tree, open the Jobs folder.
Right-click a job that contains DBMS tables and select Open from the pop-up menu. The job opens in the Job Editor window.
In the process flow diagram for the job, right-click a DBMS table and select Properties from the pop-up menu.
In the properties window, click the Physical Storage tab.
Verify that the Enable case-sensitive DBMS object names option and the Enable special characters within DBMS object names option are selected.
If these options are not set correctly, update the metadata for the table, as described in Set Name Options for Registered Tables.

Set Default Name Options for New Tables

You can set default name options for all table metadata that is entered with the Register Tables wizard or the New Tables wizard in SAS Data Integration Studio. These defaults apply to tables in SAS format or in DBMS format.

Defaults for table and column names can make it easier for users to enter the correct metadata for tables. Administrators still have to set name options on database libraries, and users should verify that the appropriate name options are selected for a given table.

Perform the following steps to set default name options for all table metadata that is entered with the Register Tables wizard or the New Table wizard in SAS Data Integration Studio:

Start SAS Data Integration Studio.
Open the connection profile that specifies the metadata server where the tables are registered.
On the SAS Data Integration Studio desktop, select Tools Options from the menu bar. The Options window is displayed.
In the Options window, select the General tab.
On the General tab, select Enable case-sensitive DBMS object names to have the Register Tables wizard and the New Table wizard support case-sensitive table and column names by default.
On the General tab, select Enable special characters within DBMS object names to have the Register Tables wizard and the New Table wizard support special characters in table and column names by default.
Click OK to save any changes.

Set Name Options in the Register Tables Wizard

The second page in the Register Tables wizard for a DBMS table enables you to select the library that contains the table or tables for which you want to generate metadata. In the first window, check the boxes labeled Enable case-sensitive DBMS object names and Enable special characters within DBMS object names.

Set Name Options for Registered Tables

Perform the following steps to enable name options for tables that have been registered on a metadata server. These steps apply to tables in SAS format or in DBMS format.

From the SAS Data Integration Studio desktop, display the Inventory tree or another tree view.
Open the Tables folder.
Select the desired table and then select FileProperties from the menu bar. The properties window for the table displays.
In the properties window, click the Physical Storage tab.
On the Physical Storage tab, select the check box to enable the appropriate name option for the current table. Select Enable case-sensitive DBMS object names to support case-sensitive table and column names. Select Enable special characters within DBMS object names to support special characters in table and column names.
Click OK to save your changes.