Expression Builder

Enable Implicit Pass-Through Processing for User-Defined Functions

Changing the User-Defined Functions Storage Directory

Overview

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. For example, the following display shows an expression used in a WHERE clause in an SQL query.

Expression Builder Window

The Expression Builder is displayed from tabs in the property windows of many SAS Data Integration Studio transformations. It is used to add or update expressions in SAS, SQL, or MDX. The expression can transform columns, provide conditional processing, calculate new values, and assign new values. The expressions specify the following elements, among others:

column names
SAS functions
constants (fixed values)
sequences of operands (something to be operated on like a column name or a constant) and operators, which form a set of instructions to produce a value

An expression can be as simple as a constant or a column name, or an expression can contain multiple operations connected by logical operators. For example, an expression to define how the values for the column COMMISSION are calculated can be amount * .01. An example of conditional processing to subset data can be

amount
                        > 10000 and region = 'NE'

. Other examples are an expression to convert a character date into a SAS date or an expression to concatenated columns.

The Functions tab of the Expression Builder enables you to select SAS functions, formats, and other components and add them to an expression. Documentation for a selected function is displayed to the right of the function. For details about SAS expressions, see SAS Language Reference: Concepts.

The Expression Builder supports the following specialized function types:

Database Functions
User-Defined Functions

Database Functions

The Functions tab in the Expression Builder window contains a list of common functions that you can use in expressions. It also contains specialized functions for the following relational databases:

DB2 10.1 Functions
DB2 8.1 Functions
FedServer TKSQL Functions
Greenplum Functions
MySQL
ODBC
Oracle
Netezza Functions
Postgres Functions
SQL Server
Teradata
Vertica Functions

The following display shows a portion of the functions available for DB2UDB 8.1 in the Expression Builder window:

DB2UDB 8.1 Functions

The functions that are marked with * (such as ABS*) can be pushed down for native processing in the database. For information about these native functions, see the documentation for the databases.

User-Defined Functions

Overview

You can import user-defined functions for models registered through Model Manager for DB2, Teradata, and Netezza databases. You can also import native user-defined functions from Oracle, DB2, and Teradata databases. After you import these user-defined functions, you can find them in the Functions tab of the Expression Builder window.

This feature supports standard DBMS user-defined functions and Enterprise Miner publishing Mining Analytics user-defined functions, standard user-defined functions and Enterprise Miner publishing Mining Analytics user-defined functions, and single-click mapping for column inserts. With single-click mapping, you can select a user-defined function in the Function tab and double-click a value in the Data Sources tab to insert it in the expression. For example, the expression ABS(CONTINENTS_NONAMERICAS.Area) draws ABS from a selected value on the Function tab and CONTINENTS_NONAMERICAS.Area) from the double-clicked value Area on the Data Sources tab.

Importing User-Defined Functions

Perform the following steps to import user-defined functions:

Open the Import User Defined Functions window from the Tools menu in SAS Data Integration Studio.
Enter the name of a container in the Container field or select a container from the drop-down list.
Select the library for the container. The available function sets are displayed in the Available field.
Move one or more functions sets to the Selected field.
Click Preview to review the functions contained in the selected functions sets.
Click Import to create an XML file named UserDefinedFunctions.xml. This file makes the imported user-defined functions available for use in the Expression Builder window.
Click Close to close the window when you have finished importing user-defined functions.

Note that if one or more function sets share names with previously imported function sets, you are warned that the previous function sets exist. If you choose to proceed with the import, the new function sets replace the identically named function sets.

Note: You can use the user-defined functions without modification if you enable explicit pass-through. To enable pass-through, select Yes in the Use the optimized pass-through facility for SQL statements field. The field is located in the Options tab in the SQL Join transformation. If you need to use user-defined functions in a job that uses implicit pass through, perform the steps in Enable Implicit Pass-Through Processing for User-Defined Functions.

Note: If you import user-defined functions from an Oracle library, the SAS metadata for that library should specify a SCHEMA of SYS, or should specify no values for SCHEMA. Otherwise, the import will fail. To update the SCHEMA value in the SAS metadata for an Oracle library, open the properties window for that library in SAS Integration Studio and click the Data Server tab.

Enable Implicit Pass-Through Processing for User-Defined Functions

If you want to process user-defined functions using implicit pass-through, you must link your DBMS user-defined functions to SAS functions. These SAS functions must have the same names and same returned values as the user-defined functions. You can define the SAS functions with PROC FCMP.

Perform the following steps:

Create the DBMS user-defined function on the database server. For example, you could create the following Oracle function:

Create FUNCTION ora_udf(in_var IN NUMBER)
      RETURN NUMBER
   IS
   RESULT_VAR NUMBER;
   BEGIN
      RESULT_VAR := 5;
      RETURN (RESULT_VAR);
    END;

Launch SAS Data Integration Studio and create a DBMS library that connects to the database server. Then, import the DBMS user-defined function into the library through the process described in Importing User-Defined Functions. For example, you could create an Oracle library that contains ora_udf.
Open a job with appropriate data and registrations. In this case, the job contains a table with a numeric column and the SQL Join transformation. Right-click the temporary output table and make sure that the Create as view check box is deselected. Also, make sure that the temporary output table is redirected to the Oracle library that contains the DBMS user-defined function. For information about redirection, see Redirecting Temporary Output Tables.
Create a data set to store the new DBMS function. To do this, open the Precode and Postcode tab in the properties for the job. Then insert code that creates a SAS function and connects it to the user-defined function that you created for the DBMS. The sample job contains the following code:
```
data work.newfunc;
       SASFUNCNAME = "ORA_UDF";
       SASFUNCNAMELEN = 7;
       DBMSFUNCNAME = "ORA_UDF";
       DBMSFUNCNAMELEN = 7;
       FUNCTION_CATEGORY = "CONSTANT";
       FUNC_USAGE_CONTEXT = "WHERE_ORDERBY";
       FUNCTION_RETURNTYP = "NUMERIC";
       FUNCTION_NUM_ARGS =1;
       CONVERT_ARGS = 0;
       ENGINEINDEX = 0;
       output;
    run;

    OPTIONS CMPLIB=work.newfuncs;
    PROC FCMP OUTLIB=work.newfuncs.ORA_UDF;
       FUNCTION ORA_UDF(a);
       RETURN(5);
       ENDSUB;
     RUN;
```
Note that this SAS function has the same name and returned values as the DBMS user-defined function.
Set the options necessary to add the SAS function to the existing in-memory SAS function list. To open the Other Options tab for the DBMS library, select PropertiesOptionsAdvanced Options. These options are set in the Options to be appended field . The following options are set for the sample job:
```
sql_functions="EXTERNAL_APPEND=work.newfunc" sql_functions_copy=saslog
```

After these steps are completed, you can process user-defined functions in jobs that use implicit pass-through.

Changing the User-Defined Functions Storage Directory

You can configure a system property with the name UserDefinedFunctionsPath. Use this property to specify the file system path of the location of the user-defined function.xml file. You can specify this property in the distudio.ini file in the SAS Data Integration Studio installation directory. Use the following syntax:

JavaArgs_<#>=-DUserDefinedFunctionsPath="<path>"

Be sure to give the argument a unique number and add the filename to the end of the path.