SAS Data Integration Studio cannot successfully generate code for a job that includes a DBMS table in which the double quotation mark is used in the table name or the column names.

Concurrent Queries to Teradata Tables Can Fail

You should use the CONNECTION=GLOBAL LIBNAME option whenever you have difficulty running concurrent queries to Teradata tables using the Teradata Access Engine. This setting is recommended when these queries fail. A race condition can occur when there are two concurrent queries to a table that uses a LIBNAME with a connection that is not global. This global connection can prevent a disconnect at the end of the SQL procedure.

Create as View Option Works Only When It Is Possible to Create a View

In the process flow for a job, you can display the properties window for an output table, click the Physical Storage tab, and select the Create as view option. If this option is selected, the table is created as a view, if that is possible. If it is not possible to create a view, a physical table is created even when the Create as view option is selected. For example, the Extract transformation generates implicit pass-through code. If the format of the output table is a DBMS other than SAS, the creation of a view might not be permitted in the target DBMS. Deselect this option to specify that the table should be created as physical table instead of a view.

Data Transfer Does Not Work for DBMS Tables with Special Characters in Table Names

If you create a process flow in a SAS Data Integration Studio job that transfers a DBMS table from one computer to another and the table name includes special characters, the transfer fails. Neither implicit nor explicit data transfer works in this case.

DBMS-Specific Functions Work Only with Explicit Pass-Through

The Expression Builder is a component that enables you to create SAS expressions that aggregate columns, perform conditional processing, and perform other tasks in a SAS Data Integration Studio job. When you are specifying expressions for columns in DBMS tables, the Functions tab of the Expression Builder might display a folder that is named after the DBMS, such as Teradata. Keep in mind that functions unique to a DBMS resolve only in the context of explicit pass-through processing. Otherwise, you will receive an error when the job is executed. One example of explicit pass-through processing would be in the context of the SQL Join transformation when the explicit pass-through option turned on.

Do Not Include the << and >> Signs in the Column Descriptions of a Table That Could Be Used in an Information Map

You can enter labels for columns in a SAS data set. These labels can be easier for users to manipulate and remember than the original column names. When the SAS data set is registered in a metadata repository, the labels are translated into the Description field for the column.

If you are using these SAS data sets to build information maps, then the column descriptions must not contain double less-than signs (<<) or double greater-than signs (>>). In addition, no description should end in a single greater-than sign (>). If the column description contains these characters, then these columns are not directly usable in expressions in an information map. You can use SAS Management Console or SAS Data Integration Studio to fix this problem by changing the description of the column.

Do Not Use MLE Library Tables as Targets in SAS Data Integration Studio Jobs

Tables can be stored in a library that was assigned or pre-assigned by using the metadata LIBNAME engine (MLE). Do not specify MLE Library table as a target in a SAS Data Integration Studio job. If you use an MLE library table as a target in a job, executing the job could have an unexpected impact on the job's metadata.

Enhanced Validation for Generated Code Can Be Turned Off

When SAS Data Integration Studio generates code for a job, it checks for the following conditions, among others:

No column mappings are defined for a transformation.
The number of source columns is not equal to the number of target columns in a transformation.

Previous releases of SAS Data Integration Studio did not check for these two conditions.

In most cases, if no column mappings are defined for a transformation, code generation fails with an error. This rule does not apply to the User-Written Code transformation and the Model Scoring transformation. It also does not apply to any transformation for which code is not generated (the transformation is turned off or it retrieves user-written code). If you need a transformation that does not define mappings, use a User-Written Code transformation or specify user-written code on the Process tab for a transformation.

If the number of source columns is not equal to the number of target columns in a transformation, a warning message is displayed in the SAS log for the job. You can take action, if appropriate.

Perform the following steps to temporarily turn off the new validations until the job can be fixed:

Exit SAS Data Integration Studio. (You must close SAS Data Integration Studio before attempting to edit the file in the next step.)
Locate the file named wadef.txt.
Edit this file in a simple text editor. Append the following line to the end of the file: 913Behavior=Y
Restart SAS Data Integration Studio. Most of the enhanced code generation error detections are now disabled.

After you have corrected the job to take advantage of the updated error checking, you can remove the line that turns off the error detection. Remember to close SAS Data Integration Studio before attempting to remove the line from the wadef.txt file.

Note: Turning off the enhanced error checking should be a temporary change only.

You should modify existing jobs to take advantage of the enhanced error detection at your earliest opportunity. Future releases of SAS Data Integration Studio might not enable you to turn off error detection for generated code.

External File Wizard: Cannot Sort Displayed Data by Column

The External File wizards have a Column Definition page. The bottom half of that page has a Data tab that displays data from the external file, if you have specified enough information about the file. When viewing data in the Data tab, you cannot click a column heading and sort the displayed data by the values in that column. The ability to sort the columns is currently not supported.

Jobs with Implicit Data Transfers between Different Hosts Fail

Do not use a loader transformation to specify an input table from one type of host (such as a PC) and an output table on a different type of host (such as MVS). This job flow will fail. To enable a transfer between different hosts, you must insert a Data Transfer transformation before the loader. This step ensures that the data is already in the target format before it is loaded into the target table.

Limit Line Lengths in the Expression Builder to 128 Characters

Limit line lengths in the Expression Builder to 128 characters to avoid problems caused by server line-length restrictions.

Maximum Integer Value for a Field in SAS Data Integration Studio

In SAS Data Integration Studio, for an integer column with the SAS format w., the maximum allowable value is 2,147,483,646 (Integer.MAX_VALUE - 1). A double (floating point) column with the SAS format BESTw. does not have this limitation.

Microsoft Queue Writer Transformation Does Not Transform Missing Date Values

The Microsoft Queue Writer transformation does not transform missing date values to some other value. If missing date values are encountered, then an error occurs. Tables that are transmitted on the Microsoft MQ messaging system should be handled in accordance with the following recommendations:

Use the Data Validation transformation to clean up the missing date values before you send any tables via a Microsoft queue writer.
Send tables in transactions on transactional queues. If a non-transactional queue is used, then all the rows of the table are sent until a row with any error (such as a missing date value) is encountered. At this point, the error prevents any further rows of the table being sent.

Migrating from SAS/Warehouse Administrator to SAS Data Integration Studio

For information about migrating from SAS/Warehouse Administrator to SAS Data Integration Studio, see Migration: Converting from SAS/Warehouse Administrator to SAS Data Integration Studio, which is available at the following website: http://support.sas.com/rnd/migration/planning/software/etlstatement.html.

Out-Of-Date Generated Transformations Are Updated Automatically When Included in Jobs That Are Deployed or Redeployed

If you include an out-of-date generated transformation in a job that is deployed or redeployed, SAS Data Integration Studio automatically updates the transformation in the deployed job. The updated transformation code is generated instead of the original code, so the results of the job can be affected.

SAS Data Integration Studio Jobs Cannot Output HP Neoview Tables That Have Keys

The HP Neoview DBMS does not support constraints (keys) on tables. Accordingly, SAS Data Integration Studio jobs cannot output HP Neoview tables that have keys.

SAS Metadata Bridges

SAS Data Integration Studio enables you to import and export metadata in formats that are supported by a SAS Metadata Bridge, such as Common Warehouse Metamodel (CWM) format. Make sure that you have the appropriate bridge. Ask your SAS representative for details.

Saving Metadata Changes in the Corresponding Physical Target

When you update a SAS Data Integration Studio job that has produced output tables at least once, any changes that you make to the column metadata for its tables are not reflected in the physical tables immediately. The column changes are not seen until you select Drop Target on the Load Technique tab of the Loader transformation for the tables, and you successfully execute the job again. Drop Target is not selected by default.

Saving Temporary Output Tables to a Permanent Location

Most transformations in a SAS Data Integration Studio job send their output to a temporary output table. By default, temporary output tables are stored in the WORK library. Perform the following steps to change the library where the temporary output table is stored for a transformation:

Right-click the icon for the temporary output table in a process flow (green circle). Select Properties from the pop-up menu.
Click the Physical Storage tab.
Change the library and other physical storage information as desired.

Sign-on Scripts for SAS/CONNECT Servers

SAS Data Integration Studio uses a SAS/CONNECT server to submit generated SAS code to computers that are remote from the default SAS Application Server. A SAS/CONNECT server can also be used for interactive access to remote libraries.

For SAS Data Integration Studio to generate the appropriate code for scripted sign on to a SAS/CONNECT server, you must specify a valid user ID and password in the sign-on script. The sign-on script is specified in the metadata for the SAS/CONNECT server in SAS Management Console.

SQL Delete Transformation: Limitations with Hadoop (Hive), HAWQ, Impala, LASR, PI, or SASHDAT Tables

The SQL Delete transformation does not allow Hadoop (Hive), HAWQ, Impala, LASR, PI, or SASHDAT target tables because the UPDATE statement is not supported.

SQL Insert Rows Transformation: Limitations with Hadoop (Hive), HAWQ, Impala, LASR, PI, or SASHDAT Tables

The SQL Insert Rows transformation does not allow Hadoop (Hive), HAWQ, Impala, LASR, PI, or SASHDAT target tables because the UPDATE statement is not supported.

SQL Join Transformation: Adding Multiple Sources with Primary Keys to an SQL Join Transformation Job

You can add more than one source table with a primary key to a job that includes the SQL Join transformation. However, only the first primary key match is shown in the SQL Join query. The auto-join feature of SQL Join assumes that primary keys must have foreign key relationships with the other tables participating in the join before auto-join finds them.

If two tables have different primary keys, and you want both keys to participate in the join, and you want auto-join to find both of them for you, you must make foreign key associations to represent the relationships between the two tables. Then, you do not have to enter one key manually. This feature ensures that the key relationships found for the tables are consistent.

SQL Merge Transformation: Input and Output Issues

The SQL Merge transformation cannot accept a temporary output table as a source table. You must use a table from one of the supported database management systems. In addition, you cannot use the same table as both the input and output table for the SQL merge transformation.

SQL Merge Transformation: Not Supported on z/OS

The SQL Merge transformation does not work with tables stored on z/OS.

SQL Merge Transformation: Use the SQLNCLI10 Driver to Access SQL Server 2008

When an SQL Merge transformation writes to a table in SQL Server 2008 format, use the SQLNCLI10 driver (SQL Server Native Client 10.0), not the older SQLOLEDB driver, to access SQL Server 2008.

SQL Update and SQL Merge Transformations: Manual Updates Are Required If Subqueries Are Used

The SQL Update transformation and the SQL Merge transformation do not support table aliases on the target table for the transformation. However, the Subquery interface for these transformations generates a table alias (‘t’) in the code for the subquery. To specify a subquery for a table that is the target of an SQL Update or SQL Merge transformation, for databases other than Oracle, edit the generated code as follows:

Add a subquery as usual to an SQL Update transformation or an SQL Merge transformation in a job. For example, open the properties window for an SQL Merge transformation. Click the Source tab. Select Subquery in the Source control to display the Subquery Builder. Then, click the Filter and Sort tab to specify a filter for the subquery.
Open the properties window for the transformation.
On the Code tab, select All user written from the Code Generation Mode control.
Find and delete the table alias 't' after libname.tablename in the UPDATE statement.
Click OK to save your changes.

If you want to specify a subquery for an Oracle table that is the target of an SQL Update or SQL Merge transformation, contact SAS Technical Support.

SQL Update Transformation: Limitations with Hadoop (Hive), HAWQ, Impala, LASR, PI, or SASHDAT Tables

The SQL Update transformation does not allow Hadoop (Hive), HAWQ, Impala, LASR, PI, or SASHDAT target tables because the UPDATE statement is not supported.

SQL Transformation Limitations with SASHDAT Tables as Input

SASHDAT tables cannot be read by any of the transformations because SASHDAT does not support read outside specialized procedures or DS2. SASHDAT tables are not allowed as source to most transformations. An error is generated if you connect a SASHDAT table as a source to the SQL transformations Join, Set Operators, and Create Table.

SQL Transformation Limitations with SASIOLA (LASR) Tables as Input

Tables from the SASIOLA (LASR) library engine cannot be a source for any SQL transformations. SQL transformations Join, Set Operators, and Create Table do not allow SASIOLA type tables as input due to explicit pass-thought in PROC SQL.

Synchpoint Option on WebSphere Queue Reader Does Not Prevent All Data Commits to Target When an Error Occurs

If the synchpoint option in the Advanced Setting window for the WebSphere Queue Reader transformation is set to Yes, you might expect that any error while reading messages from the queue would prevent any data from being written from the queue to the target. Instead, all of the messages that precede the message where the error occurred are written to the target and the remaining messages are not.

For example, if an error occurs on the fifth message in a ten-message queue, messages one to four are written to the target, and messages five to ten are not written. In addition, an Ended with errors status is displayed for the job, and an error message is added to the log. Finally, all of the messages remain in the queue when an error occurs. If no errors are present, all of these messages are removed.

Transformations That Use PROC SQL Rename SAS Reserved Words Unless Case Sensitive Names and Special Characters Are Recognized

Any transformations that generate code that uses PROC SQL rename SAS reserved words without issuing a warning. (These reserved words are sometimes used as column names.) However, you can change this behavior by selecting the Enable case-sensitive DBMS object names check box and the Enable special characters within DBMS object names check box on the Physical Storage tab. Then, the SAS reserved words are treated as n literals and are not renamed.

Unrestricted Users Cannot Perform Tasks That Require Logins from the Metadata Server

An unrestricted user is one of the administrative users that can be defined for a SAS Metadata Server. If SAS Data Integration Studio and related software have been configured with the SAS Deployment wizard, a default unrestricted user called sasadm is created .

An unrestricted user such as sasadm cannot access other servers by retrieving logins from the metadata server. For example, you cannot log on to SAS Data Integration Studio as an unrestricted user and access the servers that are required by the Register Tables wizards and the New Table wizard. It also means that an unrestricted user cannot use the metadata Export wizard to include or replace physical tables in a DBMS.

For details about the unrestricted user, see the SAS Intelligence Platform: Security Administration Guide.

Update Table Metadata Cannot Be Used for Some Tables

You can use the Update Table Metadata feature to update DBMS table names and column names that have special characters. However, you cannot use it for SAS table names and column names with special characters. A special character is any character that is not an underscore, a letter of the alphabet, or a numeric digit (such as 0 through 9). You also cannot use this feature for PC format files, such as Microsoft Excel, or for parameterized tables.

Update Table Metadata on z/OS Platforms

The Update Table Metadata feature updates table metadata so that it matches the corresponding physical table. However, if the physical table resides on a z/OS platform, the update might fail for large tables. A z/OS limit on the number of characters in a single line causes this problem.

Updates Might Be Needed to the Names of Primary Keys for Oracle Tables

When SAS Data Integration Studio creates PRIMARY KEY definitions on Oracle tables, it uses the ADD PRIMARY KEY statement. This statement does not support the naming of the Primary Key. Accordingly, the key gets a generic name generated by Oracle. This happens even if you specify a name that you want to use for the key in the properties window for the table. The result is that the error message that is returned when a constraint is violated might be hard to understand.

If this behavior becomes a problem for you, update the transformation that specifies the primary key for the table. Switch Code Generation from Automatic to User-Written Body, and then replace the existing ADD PRIMARY KEY syntax with the ADD CONSTRAINT name PRIMARY KEY syntax.

Very Large Tables or Files in WebSphere Message Queues Can Cause Memory Overruns

When very large tables or files of 500,000 rows or more are run in a job that includes a WebSphere message queue, memory overruns can occur. These overruns occur because memory handles are not freed during WebSphere MQ calls. Instead, the memory handles are freed only when the DATA step ends. This delay in freeing the memory means that a potential exists to run the server out of memory when you write very large files to a queue. You can correct the problem by splitting the writes and reads up into smaller sets of data. You can also modify the generated code to free up memory by adding code to the generated code for the SAS job.

The following lines of SAS code must be added in order to free handles when you write records from a table to a queue. They must also be added when you read records into a table from the messages on a queue. Add the following lines immediately before the line that consists of the label etls_mqexit: in the generated SAS code:

if etls_hmd ^=0 then
do;
/* Free message descriptor handle */
CALL MQFREE(etls_hmd);
end;
if etls_hmap ^=0 then
do;
/* Free map descriptor handle */
CALL MQFREE(etls_hmap);
end;
if etls_hdata ^=0 then
do;
/* Free Data handle */
CALL MQFREE(etls_hdata);
end;

When reading messages from a queue to a table, the SAS code to free up memory must be moved if it is added. Move the following mqmap call code to a position immediately after the line do i=1 to .....;:

call mqmap(etls_hmap, etls_qrc, etls_desc0, ....);
if etls_qrc ^= 0 then
do;
%rcSetDS(8000);
etls_qmessage = sysmsg();
put "ERROR: MQMAP: failed with reason code: " etls_qmessage;
goto etls_mqexit;
end;

Last updated: January 16, 2018