Appendix 1: Usage Notes

---

General Usage Notes

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

Components affected: SAS ETL Studio jobs; tables in metadata LIBNAME engine (MLE) libraries.

A table that is stored in a library that was assigned or preassigned using the MLE should not be specified as a target in a SAS ETL Studio job. If you use an MLE library table as a target in a job, the job might fail and the metadata for the job might become corrupted.

Impact of TEMP=YES Option for Tables in an SPD Server Library

Components affected: tables in a SAS Scalable Performance Data (SPD) Server library.

The properties window for an SPD Server library includes an Options tab. On the Options tab, there is an Advanced Options button. If you click theAdvanced Options button and then select the Server Connection Information tab, you can specify YES or NO in the Temp field. The Temp field specifies whether a temporary LIBNAME domain is created for the library. If you specify YES, any data objects, catalogs, or utility files created in the library are deleted when you end the SAS session. If you select YES in the Temp field, tables in the library will not be saved to a persistent location. Accordingly, you cannot use the View Data feature in SAS ETL Studio to view tables in the library.

Migrating from SAS/Warehouse Administrator to SAS ETL Studio

Components affected: SAS/Warehouse Administrator environments that are being migrated to SAS ETL Studio.

For information about migrating from SAS/Warehouse Administrator to SAS ETL Studio, see Migration: Converting from SAS/Warehouse Administrator to SAS ETL Studio, which is available at http://support.sas.com/rnd/migration/planning/files/etlstatement.html

New Schema Names Must Match the Names in the DBMS

Components affected: New Schema Wizard, properties window of a DBMS table.

When you are adding or editing a schema in a New Schema wizard or in the Physical Storage tab of the Properties window of a DBMS table, the name of the schema in the metadata must exactly match (including case) the name of the corresponding schema in the DBMS.

ODS Output from Stored Processes Generated by SAS ETL Studio

Components affected: stored processes generated from SAS ETL Studio that are executed in applications that use the SAS Output Delivery System (ODS) to format output.

If a stored process is used to create output that will be formatted by ODS, the code that creates the output must appear between the

   %stpbegin 

and

   %stpend 

delimiters. The following example illustrates the syntax:

   %stpbegin;   output_code   output_code   . . .   %stpend; 

Most SAS ETL Studio jobs are used to create or update data stores, not to create reports and other output that can be formatted with ODS. Accordingly, when SAS ETL Studio generates code for a stored process, the stored process does not include the

   %stpbegin 

and

   %stpend 

delimiters. However, a SAS ETL Studio job can be used to create a report or other output that can be formatted with ODS. The report transformations in the Process Library are used to create reports, for example.

If you generate a stored process for a SAS ETL Studio job and you want to execute that stored process in an application that uses ODS to format output, you must edit the stored process and insert the

   %stpbegin 

and

   %stpend 

delimiters around the block of code that creates the output.

Possibly Unusable DBMS Tables after Dropping or Re-creating

Components affected: properties window for a Loader transformation.

When SAS drops and re-creates a table in a DBMS, it can destroy key metadata that is necessary for operation. For example, the act of dropping and creating Siebel interface tables in Oracle results in tables that are unusable for running the Siebel process that uses those interface tables.

To prevent this from happening, select Truncate Table on the Load Technique tab before running the job.

Saving Metadata Changes to the Corresponding Physical Table

Components affected: properties window for a Loader transformation, physical tables that are updated by SAS ETL Studio jobs.

For jobs that have been run once and contain a Loader transformation, metadata changes to columns are saved in the physical target only when you select Drop Target in the Load Technique tab of the Loader transformation. Drop Target is not selected by default.

Signon Scripts for SAS/CONNECT Servers

Components affected: SAS ETL Studio jobs that submit generated code to remote computers.

SAS ETL 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 ETL Studio to generate the appropriate code for scripted signon to a SAS/CONNECT server, you must specify a valid user ID and password in the signon script.

SQL Join Transformation

Components affected: properties window for an SQL Join transformation.

Reordering Group by Rows or Columns

In the properties window for the SQL Join transformation, on the Group By tab, you can select and reorder only one row or column at time in the Column Name table.

Using Compound Expressions

In the properties window for the SQL Join transformation, you can enter expressions in the flowing tabs:

• Tables

• Mapping

• Where

• Having

If you enter an expression in which AND or OR are combined with any of the following functions, you must enclose those functions in parentheses.

• DATE()

• DATETIME()

• TIME()

• TODAY()

Here are some examples:

   Delivery_Date > (TODAY()) AND Order_Type = "AB"   (Delivery_Date > TODAY()) AND Order_Type = "AB" 

Submitting a Job From the Source Editor When Source Code Has Been Inadvertently Selected

Components affected: the Source Editor tab in the Process Designer window.

The Process Designer window includes a Source Editor tab. Use the Source Editor tab to view and update the SAS code for a selected job.

If you display the code for a job in the Source Editor tab, it is possible to inadvertently select some of the code in the tab. If you then submit the job for execution, only the selected code is submitted and the job will fail. One remedy is to switch to the Process Editor tab and resubmit the job.

Update Table Metadata Cannot Be Used on DBMS Tables That Have Case or Special Character Options Selected

Components affected: the Update Table Metadata feature; DBMS tables that have case or special character options selected on the Physical Storage tab of their property windows.

The Update Table Metadata feature cannot be used on DBMS tables that have case and special character options selected on the Physical Storage tab of their property windows. With such tables, the Update Table Metadata feature mistakenly puts SAS name literals around the table names saved in the metadata repository, which might cause any jobs containing the updated table to fail.

Verify Output from a Job That Updates a DBMS

Components affected: the View Data feature; jobs in which the target data is stored in a data base management system (DBMS).

Some DBMS's do not commit changes as soon as they are requested. Accordingly, if a SAS ETL Studio job updates a table in a DBMS and you try to verify the update by using the View Data feature, the changes might not show up immediately.

If you want SAS changes to a DBMS table to show up immediately, select YES in the Whether to COMMIT immediately after a transaction field in the metadata for the DBMS library that is used to access the DBMS table.

To select this option for a DBMS library, display the property window for the library, select Options, and then click Advanced Options. Click the Input/Output tab. In the Whether to COMMIT immediately after a transaction field, select YES and then click OK to save your changes.

---