Expanding Data Queries in SAS Intelligent Decisioning: Using the IN operator with the WHERE clause

[#1]

A data query is a type of code file that uses SQL to return records from a database or SAS data set to be processed in a decision flow within SAS Intelligent Decisioning. Knowing how to efficiently write a query to return the precise records needed for your decision is a key step for ensuring success with the decisioning process.  The purpose of this blog is to expand on the basic WHERE clause to incorporate the IN operator which allows for a more robust use of the query code.

The Data Query

Data queries are created with SQL code. The SQL editor is accessed

creating a new code file, specifying the type as data query, then selecting SQL editor. The SAS Intelligent Decisioning interface provides syntax help at the top of the SQL editor highlighting a SELECT statement with a WHERE clause. Let's begin by examining an example using a simple WHERE clause.

In the following example code, the query returns information on each TSA-related damage or loss claim that resulted from incidents occurring at a single airport. The SELECT statement returns the values of the CLAIM_NUMBER, CLAIM_AMOUNT, ITEM_CATEGORY and AIRPORT_CODE columns from the EBDID.TSACLAIMS table and assigns them to the decision variables. The value of AIRPORT_CODE is assigned to PARM1 which serves as an input variable in the decision.

SELECT Claim_Number AS {:ClaimNum: string:13}, Claim_Amount AS {:Amt:decimal},
       Item_Category AS {:ItemCategory:string:149},  Airport_Code AS {:AirportCode:string:3}
FROM EBDID.TSACLAIMS
WHERE Airport_Code = {?:PARM1:string:3}

Now, let's modify the query to return records for TSA Claims that initiated from multiple airports. In the WHERE clause below, the IN operator replaces the equal sign and is followed by a list of input variables to the decision.  For this scenario, claim information is returned for all records where the airport code matches the value of any of the three input variables PARM1, PARM2, or PARM3.

 WHERE Airport_Code IN ({?:PARM1:string:3}, {?:PARM2:string:3}, {?:PARM3:string:3})

The Scenario Test

When editing code in the SQL editor, always click the Validate button to make sure the code compiles successfully. It is also best practice to test code files before adding them to a decision. SAS Intelligent Decisioning provides two types of tests for this purpose, basic and scenario. I'll use a scenario test. A scenario test allows you to enter specific input values and (optionally) the output values that you expect the test to generate. It identifies differences between the output that you expect to see and the actual output that is generated when the test is run.

Rather than compare the actual vs expected results, I'll use the scenario test to plug in airport codes values as input variables and evaluate the resulting table to ensure that only the claims records for the airports I specified are returned.

To create a new scenario test, click Scenarios from the data query's Scoring tab, then click New Test. A Scenario Test requires an output table location to store the resulting table and the input values you are testing.  The code in this example has three parameter variables listed after the IN operator, so I'll enter the airport codes: MIA, LAX and PHX. Since I am not concerned with comparing expected output, I will run the test without adding expected output values. 

 

The Results

Once the scenario test is complete, clicking the results icon brings up the results window. The Output section of the results window highlights any differences between the actual and expected results. I didn't supply any expected results, so that column is blank. The Actual Value column contains a data grid table with the results of the query.  Data queries produce data grid variables by default, this setting can be modified in the query's Properties tab, if desired.

Clicking the link in the Actual Value column opens the contents of the data grid table in a window. The data grid displays only the claim records from the Airport codes specified in the scenario test.  Mission accomplished. This data query is ready to be added to a decision flow.

Although adding an IN operator to the WHERE clause is a small modification to the code, it significantly increases our control over the data that is fed into our SAS Intelligent Decisioning decision flow. When we begin with a strong query, we can expect better results from our decision building process. For more information on data query files please visit the SAS Intelligent Decisioning User's Guide.