SAS Viya 3.5: SAS Intelligent Decisioning – Working with Data Query Results
In SAS Intelligent Decisioning on SAS Viya 3.5, you can use Data Queries as part of your decision flow. A data query allows you to use SQL to return records from a database or SAS data set. Any decisions that involve a data query file can only be published to the SAS Micro Analytic Service (MAS). In a previous article I reviewed the data query configuration for MAS. In this article, I review how to work with the results returned by a data query.
Data Query Results
A data query is a type of Code file you create in Intelligent Decisioning and use within a decision flow.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
A data query returns its output as a data grid. A data grid is essentially a table. Each row that is evaluated by the data query file creates a separate row in the data grid with the output columns containing the results from the query. The following statement is automatically added when you create a data query file using the SQL editor:
Including this statement generates variables for the returnCode, rowCount as well as the data grid object (dgo) variable that contains the query output. You can then use the variables of returnCode and/or rowCount in your decision flow logic.
Example Data Query with Returned Results
Let's look at an example of a data query that returns 0 or 1 row for each query call. The input data for my query is movie category names.
A query is performed on the film_category table to return the category_id associated with the supplied category name.
Below is the SQL Query code for the Movie_Categories data query:
/* include sqlReturnInfo */
SELECT "category_id" AS {:returnCategory_id:integer}
FROM "mypg"."category" WHERE "name" = {?:inputCategory:string:25}
When the data query file is used in a decision flow the inputs and outputs need to be mapped.
Input Variables for the data query:
Output Variables for the data query:
Note: rowCount and returnCode are included as output variables since I kept the /* include sqlReturnInfo */ statement in my data query code.
The results of this query are:
Notice that the data grid is in a JSON format. I select the hyperlink for the Movie_Categories_out data grid to view its specific results.
In this case the query returns only record per call.
Example of Filtering on sqlReturnInfo Output Variable
Using the rowCount variable, I can filter the data grid based on the rows that contain query results.
The data grid rows with no results returned from the query (Movie_Categories_rowCount_out = 0) go down the Yes branch and data grid rows with results returned from the query (Movie_Categories_rowCount_out <> 0) go down the No branch of the decision flow.
Example of Rule Using Data Grid Returned from Data Query
On the No branch, I write a rule to assign the decision variable queryCategory_id the value returned in the data grid variable returnCategory_id.
In the decision, I need to map the inputs and outputs when calling the rule.
Input Variables for the rule set:
Note: I need to select the Scores rows in this data grid option to reference the data grid variable returnCategory_id value for the rule.
Output Variables for the rule set:
Example using Data Grid Functions
Now let's look at an example where a data query returns multiple rows for each query call. The input data for this query is the category_id returned from the previous data query and was assigned to a decision variable called queryCategory_id in the previous rule. The query returns the film's id, title, and rating that correspond the supplied category_id.
Below is the SQL Query code for the Movie_Ratings data query:
/* include sqlReturnInfo */
SELECT FC."film_id" AS {:returnFilm_id:integer}, F."title" AS {:film_title:string:255}, F."rating" AS {:film_rating:string:5}
FROM "mypg"."film_category" AS FC
JOIN "mypg"."film" AS F ON (FC."film_id" = F."film_id")
WHERE FC."category_id" = {?:queryCategory_id:integer}
Input Variables for the data query:
Output Variables for the data query:
Note: rowCount and returnCode are included as output variables since I kept the /* include sqlReturnInfo */ statement in my data query code.
The results of this query are:
The Movie_Ratings_rowCount_out variable indicates that multiple rows are returned for each call to the query. I select the hyperlink for the Movie_Ratings_out data grid for one of the calls to view its specific results.
Now, I want to write a rule to determine if the film is kid-friendly based on the film's rating. If the Film has a rating of G or PG, then it is kid-friendly, if it has a rating of PG-13, then it may be kid-friendly, else it is not kid-friendly. Below is the rule for this logic:
On the Variables tab for the decision flow, I need to edit the Movies_rating_out data grid and the film_KidFriendly variable since the data grid has multiple rows and I want this variable to have a different value depending on the data in a particular row in the data grid. Note: The film_KidFriendly data grid variable will be mapped to the output_Kid_Friendly variable in the rule.
Input Variables for the rule set:
Note: I need to select the Scores rows in this data grid option to reference the data grid variable film_rating value for the rule.
Output Variables for the rule set:
The execution of the rule results in the Movie_Rating_out data grid being updated with the appropriate FILM_KIDFRIENDLY value based on its FILM_RATING.
Finally, I write some DS2 code to get the film_KidFriendly counts for each inputCategory. To do this, I use the data grid function DATAGRID_MATCHCOUNT. For the complete listing of data grid functions, refer to the documentation. Below is the DS2 code:
package "${PACKAGE_NAME}" /inline;
method execute(in_out package datagrid Movie_Ratings_out, in_out double YES_count, in_out double MAYBE_count, in_out double NO_count);
"YES_count" = DATAGRID_MATCHCOUNT(Movie_Ratings_out,'film_KidFriendly','==','YES');
"MAYBE_count" = DATAGRID_MATCHCOUNT(Movie_Ratings_out,'film_KidFriendly','==','MAYBE');
"NO_count" = DATAGRID_MATCHCOUNT(Movie_Ratings_out,'film_KidFriendly','==','NO');
end;
endpackage;
Note: The package "${PACKAGE_NAME}" /inline; statement must be on line 1 of the code file. The {PACKAGE_NAME} is replaced with a package name that SAS Intelligent Decisioning uses to maintain the relationship between the code file and the decisions that use it.
Here are the results of executing this DS2 code:
Summary
Below is a picture of the entire decision flow process used as an example in this article.
The exported document for this example decision flow and its components is attached to this article.
For more information on working with data query results, refer to the documentation on data query files and the documentation for data grids.
