UNIQUE=YES | NO

specifies whether the primary index of the output table is unique.

Default NO VOLATILE=YES | NO

specifies whether the output table is created as a temporary table.

Default NO

Interaction This option affects the stored procedure SQL generation.

Range The OPTIONS= parameter string can be from 0–20,000 characters long.

Requirements Each option must end with a semicolon, including the last option in the list.

If the OPTIONS= parameter string is greater than 1,000 characters, the OPTIONS= parameter must be the last one.

Note option can be blank or NULL if no options are needed.

Tip Options that are not recognized as directives to the stored procedure are passed to the SAS Embedded Process as Query Band name-value pairs. If the SAS Embedded Process does not recognize them, they are ignored. Up to ten user-defined Query Band name-value

Using the SAS Embedded Process to Run Scoring Models 161

pairs can be specified in addition to the options listed here that are Query Band name-value pairs. The maximum length of the query band is 2048 characters. User-defined Query Band information is logged in Teradata Database Query Log (DBQL) that makes it useful for workload analysis and reporting.

Tips for Using the SAS_SCORE_EP Stored Procedure

• The SAS Embedded Process for Teradata supports only ISO-8859-1 (Latin-1) encoding for table metadata. Examples of table metadata include table and column names.

• No specific parameter order is required. However, the INQUERY parameter must be the first parameter if its string is greater than 1,000 characters. Similarly, if the OPTIONS parameter string is greater than 1,000 characters, it must be the last parameter.

• Database object names (for example tables and columns) must be enclosed in double quotation marks if they are Teradata reserved words. Otherwise, quotation marks are optional.

• Tables can be qualified with a database name. If a table name is not qualified with a database name, the table name is resolved based on the default database for your session.

• All parameters are passed as strings to the SAS_SCORE_EP stored procedure, so they must be enclosed in single quotation marks. To pass a single quotation mark as part of the SQL within a parameter, use two adjacent single quotation marks as shown in the following example:

'INQUERY=select * from my_input_tbl where name like ''%Jones%''',

Teradata Scoring Files

When using the SAS Embedded Process, the %INDTD_PUBLISH_MODEL macro produces two scoring files for each model:

• sasscore_modelname.ds2. This file contains code that is executed by the SAS_SCORE_EP stored procedure.

• sasscore_modelname_ufmt.xml. This file contains user-defined formats for the scoring model that is being published. This file is used by the SAS_SCORE_EP stored procedure.

These files are published to the model table that you specify in the

%INDTD_PUBLISH_MODEL macro. See Appendix 1, “Scoring File Examples,” on page 285 for an example of each of these files.

A message that indicates whether the scoring files are successfully or not successfully created is printed to the SAS log.

Although you cannot view the scoring files directly, there are two ways to see the models whose files are created:

• Log on to the database using BTEQ and submit an SQL statement. The following example assumes that the model table where the scoring files were published is register and the model name is reg1.

bteq .logon myserver/myuserid,mypassword

select modelname, modelowner, modelupdated from register where modelname like '%reg1%';

162 Chapter 12 • SAS Scoring Accelerator for Teradata

The model name, user ID, and date and time that the model files were published are listed.

• From SAS, use SQL procedure code that produces output in the LST file. The following example assumes that the model name that you used to create the scoring files is reg.

proc sql noerrorstop;

connect to teradata (user=username password=xxxx server=myserver);

select * from connection to teradata

(select modelname,modelowner,modelupdated from sasmodeltablename

where modelname like '%reg%');

disconnect teradata;

quit;

You can also use the SASTRACE and SASTRACELOC system options to generate tracing information. For more information about these system options, see the SAS System Options: Reference.

Controlling the SAS Embedded Process

The SAS Embedded Process starts when a query is submitted. It continues to run until it is manually stopped or the database is shut down.

You can check the status of the SAS Embedded Process or disable it so that no new queries can be started. Use the following commands to perform those actions.

Action Performed Command

Provides the status of the SAS

Embedded Process. CALL DBCEXTENSION.SERVERCONTROL ('status', :A); ^* CALL DBCEXTENSION.SERVERCONTROL ('SAS', 'status', :A); ^**

CALL SQLJ.SERVERCONTROL ('SAS', 'status', :A); ^***

Stops new queries from being started.

Queries that are currently running continue to run until they are complete.

CALL DBCEXTENSION.SERVERCONTROL ('disable', :A); ^* CALL DBCEXTENSION.SERVERCONTROL ('SAS', 'disable', :A); ^**

CALL SQLJ.SERVERCONTROL ('SAS', 'disable', :A); ^***

Enables new queries to start running. CALL DBCEXTENSION.SERVERCONTROL ('enable', :A);^* CALL DBCEXTENSION.SERVERCONTROL ('SAS', 'enable', :A);^**

CALL SQLJ.SERVERCONTROL ('SAS', 'enable', :A); ^***

* For Teradata 13.10 and 14.00 only. Note that the Cmd parameter (for example, 'status', must be lowercase.

** For Teradata 14.10 only. Note that the Languagename parameter, 'SAS', is required and must be uppercase. The Cmd parameter (for example, 'status'), must be lowercase.

*** For Teradata 15 only. Note that the Languagename parameter, 'SAS', is required and must be uppercase. The Cmd parameter (for example, 'status'), must be lowercase.

Using the SAS Embedded Process to Run Scoring Models 163