Using the SAS Embedded Process to Run Scoring Models

How to Run a Scoring Model with the SAS Embedded Process

The integration of the SAS Embedded Process and Teradata allows scoring code to run directly using the SAS Embedded Process on Teradata.
Note: The SAS Embedded Process might require a later release of Teradata than function-based scoring. Please refer to the system requirements documentation.
To run the scoring model using the SAS Embedded Process, follow these steps.
  1. Create a table to hold the scoring files.
    The %INDTD_CREATE_MODELTABLE macro creates a table that holds the scoring files for the model that is being published.
    For more information, see Creating a Model Table.
  2. Run the %INDTD_PUBLISH_MODEL to create the scoring files.
    The %INDTD_PUBLISH_MODEL macro uses some of the files that are created by the SAS Enterprise Miner Score Code Export node: the scoring model program (score.sas file), the properties file (score.xml file), and (if the training data includes SAS user-defined formats) a format catalog.
    The %INDTD_PUBLISH_MODEL macro performs the following tasks:
    • translates the scoring model into the sasscore_modelname.ds2 file that is used to run scoring inside the SAS Embedded Process
    • takes the format catalog, if available, and produces the sasscore_modelname_ufmt.xml file. This file contains user-defined formats for the scoring model that is being published.
    • uses the SAS/ACCESS Interface to Teradata to insert the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files into the model table that was created using the %INDTD_CREATE_MODELTABLE macro.
    For more information, see Running the %INDTD_PUBLISH_MODEL Macro and Viewing the Scoring Files.
  3. Execute the SAS_SCORE_EP stored procedure to run the scoring model.
    For more information, see SAS_SCORE_EP Stored Procedure.

Creating a Model Table

Overview

When using the SAS Embedded Process to publish a scoring model in Teradata, you must create a table to hold the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files. You must run the %INDTD_CREATE_MODELTABLE macro to create the table before you run the %INDTD_PUBLISH_MODEL macro.
You have to create the table only one time to hold a model’s scoring files.
The model table contains the following columns. The ModelName column is the table key. The table is referenced by the two-level name model-name.model-table-name.
Column Name
Description
Specification
ModelName
contains the name of the model
VARCHAR(128) CHARACTER SET UNICODE CASESPECIFIC
ModelDS2
contains the sasscore_modelname.ds2 file
BLOB(209708800)
ModelFormats
contains the sasscore_modelname_ufmt.xml file
BLOB(209708800)
ModelMetadata
reserved by SAS for future use
BLOB(4M)
ModelOwner
contains the name of the user who published the model
VARCHAR(128) CHARACTER SET UNICODE CASESPECIFIC
ModelUpdated
contains the date and time that the model was published
TIMESTAMP(6)

%INDTD_CREATE_MODELTABLE Run Process

To run the %INDTD_CREATE_MODELTABLE macro, complete the following steps:
  1. Start SAS 9.3 and submit the following commands in the Program Editor or Enhanced Editor:
    %indtdpm;
%let indconn = server="myserver" user="myuserid" password="xxxx"
    database="mydb";
    For more information, see %INDTDPM Macro and the INDCONN Macro Variable.
  2. Run the %INDTD_CREATE_MODELTABLE macro.
    For more information, see %INDTD_CREATE_MODELTABLE Macro Syntax.

%INDTD_CREATE_MODELTABLE Macro Syntax

%INDTD_CREATE_MODELTABLE
(<DATABASE=database-name>
<, MODELTABLE=model-table-name>
<, ACTION=CREATE | REPLACE | DROP>
);
Arguments
DATABASE=database-name
specifies the name of a Teradata database where the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files are held.
Default The database specified in the INDCONN macro variable or your current database
MODELTABLE=model-table-name
specifies the name of the table that holds the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files.
Default sas_model_table
Requirement The maximum table name length is 30 characters, and it must be a valid Teradata table name.
Interaction The table name that you specify for this macro must be the same table name that is used in the %INDTD_PUBLISH_MODEL macro.
See %INDTD_PUBLISH_MODEL Macro Syntax
ACTION = CREATE | REPLACE | DROP
specifies one of the following actions that the macro performs:
CREATE
creates a new table.
Tip If the table has been previously defined and you specify ACTION=CREATE, an error is issued.
REPLACE
overwrites the current table, if a table with the same name is already registered.
Tip If you specify ACTION = REPLACE, and the current table contains sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml files, the files are deleted and an empty table is re-created.
DROP
causes all models in this table to be dropped.
Default CREATE

SAS_SCORE_EP Stored Procedure

Overview of the SAS_SCORE_EP Stored Procedure

The SAS_SCORE_EP stored procedure is the interface for running the scoring model inside Teradata with the SAS Embedded Process. The SAS_SCORE_EP stored procedure uses the files that are stored in the model table. The stored procedure parameters enable you to control the name and location of the output table, how much data is returned, and how it is returned.
The SAS_SCORE_EP stored procedure is installed in the SAS_SYSFNLIB database. To run the stored procedure, you must have the following permissions:
  • EXECUTE PROCEDURE permission on the SAS_SYSFNLIB database
  • EXECUTE FUNCTION permission on the SAS_SYSFNLIB database
  • EXECUTE FUNCTION ON SYSLIB.MonitorVirtualConfig permission on the SYSLIB.MonitorVirtualConfig function
For more information, see Teradata Permissions.

Running the SAS_SCORE_EP Stored Procedure

You can run the SAS_SCORE_EP stored procedure using explicit pass-through and PROC SQL or you can use other Teradata query tools.
Note: Before running the SAS_SCORE_EP stored procedure, you must create the model table with the %INDTD_CREATE_MODELTABLE macro and then you must publish the files to the model table with the %INDTD_PUBLISH_MODEL macro.
Here is an example using PROC SQL. 
proc sql;
   connect to teradata (user=userid password=xxxx server=myserver mode=Teradata);
   execute 
      (CALL SAS_SYSFNLIB.SAS_SCORE_EP
         (
            'MODELTABLE="grotto”.”sas_publish_model”',
            'MODELNAME=Intr_Tree',
            'INQUERY=SELECT * from ”grotto”.”score_input_table” WHERE x1 < 1.0',
            'OUTTABLE=”grotto”.”sas_score_out1”',
            'OUTKEY=id',
            'OPTIONS=’  /* can be blank or NULL if no options are needed */
         )
      ) by teradata;
   disconnect from teradata;
quit;
Note: You must specify MODE=TERADATA in your connection string.
For more information about the stored procedure parameters, see SAS_SCORE_EP Stored Procedure Syntax.

SAS_SCORE_EP Stored Procedure Syntax

SAS_SYSFNLIB.SAS_SCORE_EP
('MODELTABLE="database"."model-table-name" ',
'MODELNAME=model-name',
'INQUERY=SELECT ...',
'OUTTABLE=< "output-database-name". >"output-table-name" ',
'OUTKEY=column<..., column,> | NO PRIMARY INDEX,
NULL | 'OPTIONS=' | 'OPTIONS=option; <...; option;>'
);
Parameters
database
specifies the name of the database that contains the model table.
Default Database in the current session
Requirement The database must be the same as the one specified in the %INDTD_PUBLISH_MODEL macro’s DATABASE argument.
model-table-name
specifies the name of the model table where the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files were published with the %INDTD_CREATE_MODELTABLE macro.
model-name
specifies the name of the model.
SELECT ...
specifies a SELECT statement that defines the inputs to the SAS Embedded Process.
Range The INQUERY= parameter string can be up to 30,000 characters long.
Restriction The maximum number of characters in the query is 30,000.
Requirements If the query is greater than 1,000 characters, the INQUERY= parameter must be the first parameter listed in the stored procedure.
The SELECT statement must be syntactically correct SQL.
Interaction A query can reference tables or views, except if you specify the DIRECT option. If you specify the DIRECT option, the query can reference only tables.
Tip To pass single quotation marks around character literals, use two adjacent single quotation marks. This is an example.
'INQUERY=select * from my_input_tbl where name like ''%Jones%'''
< "output-database-name". >"output-table-name"
specifies the location of the scoring model output table.
output-database-name
specifies the database where the table is created or currently exists.
Default Current database
output-table-name
specifies the name of the table to be created or the table that currently exists.
Requirement The output table can already exist. If the output table already exists, scored rows are inserted into the table along with any existing rows. If the output table already exists, the output columns must match the existing table’s columns for the insert to succeed.
Interaction The output table can be a temporary table by adding VOLATILE=YES in the OPTIONS parameter. The temporary table can be used only for the duration of the SQL session where it is created.
column
specifies the column or columns that are used as the primary index for the output table.
Requirements The column must exist in the output table.
If there are multiple primary index columns, the column names must be separated by commas.
NO PRIMARY INDEX
specifies that there is no primary index for the output table and that output rows are placed on the same Teradata Access Module Processor (AMP) that ran the scoring code for the corresponding input row.
NULL | 'OPTIONS='
specifies that no options are used.
Tip You can use either 'OPTIONS=' or NULL to indicate that no options are used.
Example These two code lines are identical.
call sas_sysfnlib.sas_score_ep ('modeltable=...', modelname=...', 
'inquery=...', 'outtable=scored_output1', 'outkey=no primary index', 
null);
call sas_sysfnlib.sas_score_ep ('modeltable=...', modelname=...', 
'inquery=...', 'outtable=scored_output1', 'outkey=no primary index',
'options=');
option
specifies additional options for the stored procedure. option can be any of the following values:
CONTRACT=YES | NO
specifies whether to write the output metadata to the table specified in the OUTTABLE= parameter.
Default NO
Interaction If you specify CONTRACT=YES, the OUTKEY= parameter is ignored.
Tip The output is written to the table in the form of one row per output value with the sequence, name, data type, and length for each output.
DIRECT=YES | NO
specifies whether direct retrieve optimization is enabled.
Default NO
Interaction This option affects the stored procedure SQL generation.
Tip The direct retrieve optimization improves performance in the case where the input to the SAS Embedded Process is a table and the input query is SELECT * FROM table. When DIRECT=YES, the INQUERY= parameter is only the table name. No SELECT statement is needed.
DS2_KEEP=column-name<...column-name>
specifies the column or columns that are passed to the SAS_SCORE_EP procedure and are applied as a dynamic KEEP= statement in the sasscore_modelname.ds2 file.
Requirement If more than one column is specified, column names must be separated with spaces.
Interaction Specify CONTRACT=YES to preview the available output columns without executing the model.
ENCODING= LATIN | UNICODE
specifies the character data encoding for internationalization.
Default LATIN
See SAS National Language Support (NLS): Reference Guide
EPTRACE=YES
specifies that journal messages are written to the journal table.
HASHBY=column-name<..., column-name>
specifies one or more columns to use for the HASH BY clause.
Requirement If more than one column is specified, column names must be separated with commas.
Interaction This option affects the stored procedure SQL generation.
Note Data is redistributed by hash code to the TERADATA AMPs based on this column or columns although there is no implied ordering to the groups.
JOURNALTABLE=journal-table-name
specifies the name of a table that the stored procedure creates. This table holds any journal messages and notes from the SAS journal facility that are produced when executing the store procedure.
Requirement The name must follow Teradata naming conventions for table names.
Note Use a SELECT statement to retrieve the journal messages from the table after the stored procedure call is complete.
LOCALE=sas-locale
specifies set of attributes in the SAS session that reflect the language, local conventions, and culture for a geographical region.
Requirement sas-locale must be one of the five-character Posix values, for example fr_FR.
See SAS National Language Support (NLS): Reference Guide
ORDERBY=column-name<..., column-name>
specifies one or more columns to use for the LOCAL ORDER BY (BY groups) clause.
Requirement If more than one column is specified, column names must be separated with commas.
Interaction This option affects the stored procedure SQL generation.
SELECT_LIST=column-name<..., column-name>
specifies the column or columns that are used in the SQL that is generated by the SAS_SCORE_EP stored procedure.
Default * (asterisk) which indicates all columns
Requirement If more than one column is specified, column names must be separated with commas.
SQLTRACE=table-name
specifies the name of a table to hold the generated SQL code.
Tip This table is useful for stored procedure debugging or to reference later if you want to customize the SQL code that is used to call the SAS Embedded Process.
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, it must be the last parameter.
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 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

  • No specific parameter order is required, but if the INQUERY parameter string is greater than 1,000 characters, it must be the first parameter. 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%''',

Viewing the 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 Scoring File Examples for an example of each of these files.
Although you cannot view the scoring files directly, there are four ways to see the models whose files are created:
  • Look at the SampleSQL.txt file that is produced when the %INDTD_PUBLISH_MODEL macro is successfully run. This file can be found in the output directory (OUTDIR argument) that you specify in the %INDTD_PUBLISH_MODEL macro.
    The SampleSQL.txt file contains basic code that, with modifications, can be used to run your score code inside Teradata.
  • Look at the SAS log. A message that indicates whether the scoring files are successfully or not successfully created is printed to the SAS log.
  • 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%';
    The model name, user ID, and date and time 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. The following commands can be used to perform those actions.
Command
Action performed 
CALL DBCEXTENSION.SERVER CONTROL ('STATUS', :A);
Provides the status of the SAS Embedded Process. 
CALL DBCEXTENSION.SERVER CONTROL ('DISABLE', :A);
Stops new queries from being started. Queries that are currently running continue to run until they are complete. 
CALL DBCEXTENSION.SERVER CONTROL ('ENABLE', :A);
Enables waiting and new queries to start running.
Copyright © SAS Institute Inc. All rights reserved.