SAS Viya 3.4: Perform Data Monitoring Checks using SAS Intelligent Decisioning 5.3
Since the DataFlux Business Rule Manager in Data Management Studio is not planned to move to the Viya platform, I thought I would look at how we could do some similar data monitoring functionality in the current Viya 3.4 release using SAS Intelligent Decisioning 5.3. I will focus on how you can execute generic business rules similar to what you can do using row-based business rules in DataFlux Business Rule Manager. In this article, I will demonstrate how you can perform a data completeness check using a SAS Intelligent Decisioning filtering rule.
SAS Intelligent Decisioning 5.3 has two rule types – Assignment and Filtering. The Assignment rule type returns all rows and typically is used to assign a variable based on the condition(s) in the rule. The Filtering rule type filters and returns only rows that meet the condition(s). A filtering rule is most like executing a row-based business rule from DataFlux Business Rule Manager in a Monitor node within a data job in Data Management Studio. Therefore, I will use the Filtering rule type from SAS Intelligent Decisioning in my example.
For this demonstration on using a SAS Intelligent Decisioning filtering rule to mimic the behavior of a row-based business rule in DataFlux Business Rule Manager, I will use the example where I want to filter (monitor) for rows in a data set where values from two columns are missing. For example, if both the Phone and Email information are missing from a row in the data set, then that row should be returned.
Create Filtering Rule in SAS Intelligent Decisioning
To create the filtering rule, I access the SAS Intelligent Decisioning application by selecting 
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
I enter a Name for my rule set, select the Type of Filtering, enter a Description, and select a Location in which to save the rule. I select Save to create the rule set.
Just like in DataFlux Business Rule Manager, the first step to creating a Rule Set in SAS Intelligent Decisioning is to define it variables. On the Variables tab, the Add Variable button has a drop-down selection to let you add variable names from a Data source, a Decision, another Rule Set or you can create a Custom variable. Since I want to keep the variable names generic similar to what one would do in DataFlux Business Rule Manager, I select the Custom variable option.
I enter a Name and select a Data type for the variable. The possible selections for Data type are: Boolean, Character, Data grid, Date, Datetime, Decimal, and Integer. For my example, I create a variable called TextField1 and its data type is Character.
There are also optional options for the Variable that you can fill out for Description, Initial Value, and Length. I only enter a description for my variable and then select Add to create the variable for my rule set.
The variable is added to the list of Variables for the Rule Set as both an Input and Output variable.
Next, I repeat the above steps to create the other two variables needed for my rule set – TextField2 and PK. The PK variable will be mapped to the Primary Key value of the record where both the values of TextField1 and TextField2 are missing from the record in the data set.
After all the needed variables for the rule set are added, then select OK to close the Add Variables window.
The next step is to create the Rule Set itself on the Rule Set tab. Select the Add Rule button to create a rule.
This opens the rule definition window for the Filtering rule. A filtering rule is essentially an IF statement or a series of IF statements used to filter the input record set. It has a rule builder component that helps you build statements in the format:
IF <variable name> <operator> <value(s)>.
The builder has operators for: Is equal to, Is not equal to, Matches pattern, Is not in, Is in, and Exists in lookup table. Note: The Lookup tables are maintained within SAS Intelligent Decisioning. For more information on lookup tables, refer to the documentation.
To create my rule within the rule set, first I name my rule by selecting Default_rule_1 (above the IF statement) and entering a new name.
Since I'm looking for missing values in columns, the first part of my IF statement needs to be IF missing(TextField1). Missing is not one of the operators supplied by the rule builder, so I need to write a custom expression. To do this, I select 
I enter missing(TextField1) in the editor and select the Validate button to confirm that is a valid expression.
Note: In the left pane, you can double-click to select from the list of available Variables or Functions to use in the expression editor window.
I select Save to add the expression to the IF statement. Next, I select 
This automatically adds an AND condition to the rule.
I repeat the above steps to create the missing(TextField2) part of the rule.
This Rule Set now contains one rule that will filter out the records where both the TextField1 and TextField2 values are missing from the input data set. If I needed to add an additional rule to this set I could select 
Now that all the needed conditions for my example have been added, I select 
Test the Filtering Rule in SAS Intelligent Decisioning
The next part of the process is to test the newly created rule set. To do that, I select the Scoring tab and select New Test.
For a test, you can enter a Name, Description, Location, and select a Data source to map the variables used in the rule set. There is also an Advanced setting where you can change the Output data library where the results of the test are saved.
To select my input Data source, I click 
Note: This is a data set I created and imported to test my rule logic. If I'm testing Column1 and Column2 with my rule, then the rows where ID is B and E should be returned. If I'm testing Column2 and Column3 with my rule, then only the row with the ID of E should be returned. If I'm testing Column3 and Column4 with my rule, then no rows should be returned.
I select OK to confirm my input data source selection. Next, I select the Variables button to map the input data column names to the variable names used in the rule set. If the input data source had column names that matched the variable names used, they would have been automatically mapped.
I map my variables as shown below and select OK:
I select Save to save the test selections.
To run the test with the selected variable mappings, I check the test and select Run.
Once the test run is complete, I can click 
As expected, only the rows where the PK mapped field (ID) had the value of B and E are returned.
I repeat the above steps and create a test where TextField1 is mapped to Column2 and TextField2 is mapped to Column3 and confirm its results.
I repeat the above steps and create a test where TextField1 is mapped to Column3 and TextField2 is mapped to Column4 and confirm its results.
Publish the SAS Intelligent Decisioning Rule Set
Now that I've completed my testing of the rule set, I can publish it. I select the Publish button to open the Publish Rule Sets window.
I select the CAS publishing destination.
Note: Filtering rule sets cannot be published to the SAS Micro Analytic Service destinations.
Note: Your SAS Administrator maintains the publishing destinations. For more information, refer to the documentation.
I can enter a Published Name for the rule set, select whether or not to Replace if one already exists, and select whether or not I want to use Rule-Fired Tracking. After making these selections, I select the Publish button.
Note: There are restrictions on the published names which depends on its destination. Refer to the documentation for more information.
I confirm the successful publication and select Close.
On the Scoring tab for the rule set, I select the Publishing Validation section. A publishing validation test has been created by publishing the rule set.
I click the hyperlinked publishing validation test name to view its properties. Its input data source is the same one I used in the rule set tests. I select a Location for the test content.
I select Save to save the changes and close the properties. I check the publishing validation test and select Run.
There is an error running the test since a publishing validation test does not allow you to map the input fields to the variables. Therefore, if your publishing validation test table has column names that differ from your input variable names for your rule set, then the test will fail.
However, I'm not concerned about that since I know by performing my rule set tests that the rule set is working as expected.
Create Code to Execute SAS Intelligent Decisioning Rule
By running a Publishing Validation Test for a rule set, the code used to execute that rule set is generated. I can use that code to create a program to execute the rule set in SAS Studio. To view the code, I need to select
I select the Code tab to view the code that was submitted.
I copy the code from line 5 through line 22 and paste it into SAS Studio V. Note: I will write the table.promote portion of the code (lines 24- 26) later in SAS Studio.
Before those lines I add my CAS session statement, create a section to add variables I plan to use in the program, and assign all CAS libraries to the session.
Note: To keep the program generic I created variables for my input and output caslib and tables as well as the input variables needed for the rule set that is going to be executed.
Next, I add a section of code to delete the in-memory and physical output tables if they exist.
Next, I add a section of code to build the input data source table to execute the rule set against. I build a temporary table called table_check that will have the column names and data types expected by the rule set – PK, TextField1, TextField2.
Then, I add a proc cas; statement before the code I copied from SAS Intelligent Decisioning and change the hard-coded values to the variables I created earlier in the code. I also change the input data source table to use the temporary table_check table that I just created instead of TEST_MISSING_VALUES_RULE and change the output to write to a temporary table called temp_results.
Next, I add a section of code to write out the number of rule violations to the log.
Next, I add a section of code to build the output table if there are rule violations. This output table is then promoted as in-memory table and is also saved as a physical table. If there are no rule violations, then the output tables are not created.
Finally, I add code to terminate my CAS session.
Below is the full program:
cas mySession sessopts=(caslib="casuser" timeout=1800 locale="en_US");
/***** ASSIGN VARIABLES *****/
%let input_caslib=Public;
%let input_table=CONTACT_LIST;
%let input_text1=Address;
%let input_text2=Zip;
%let input_pk=ID;
%let output_caslib=Public;
%let output_table=&input_table._CHECK_RESULTS;
/****** END VARIABLE ASSIGNMENT *****/
/* assign all CAS libraries to session */
caslib _all_ assign;
proc cas;
/* Drop output tables (in-memory and physical), if they exist */
table.dropTable /
caslib="&output_caslib."
name="&output_table."
quiet=true;
/***** May need to change the deleteSource source name depending on output caslib type ******/
table.deleteSource /
caslib="&output_caslib."
source="&output_table..sashdat"
removeAccessControls=true
quiet=true;
/* Load data for Business Rule execution. Build table_check based on required inputs for rule */
fedsql.execdirect / method=true
query="create table casuser.table_check {options replace=true} as
select a.&input_text1. as TextField1, a.&input_text2. as TextField2, cast(a.&input_pk. as varchar) as PK from
&input_caslib..&input_table. as a";
run;
/* Business Rule Execution */
proc cas;
sessionProp.setSessOpt /
caslib="&output_caslib."; /* changed value to use variable */
table.tableExists result=e /
caslib="DM"
name="sas_model_table";
haveTable = dictionary(e, "exists");
if haveTable <= 0 then do;
table.loadTable /
caslib="DM"
path="sas_model_table.sashdat";
end;
ds2.runModel /
modelName="BothValuesMissing_Filtering1_0"
table={caslib="casuser", name="table_check"} /* changed values to table_check created in prior code block */
modelTable={caslib="DM", name="sas_model_table"}
casOut={caslib="casuser", name="temp_results"}; /* changed values to create temp_results */
/* Get Number of Violations */
simple.numRows result=nrows /
table={caslib="casuser", name="temp_results"};
print "**** NUMBER OF RULE VIOLATIONS: " nrows["numrows"];
/* Build output table if there are Rule Violations */
if nrows["numrows"] > 0 then do;
/* create output table based temp_results joined back to original input table */
fedsql.execdirect / method=true
query="create table &output_caslib..&output_table. {options replace=true} as
select * from &input_caslib..&input_table.
RIGHT JOIN casuser.temp_results ON cast(&input_caslib..&input_table..&input_PK. as varchar) = casuser.temp_results.PK";
/* drop rule input fields except for PK field from output table since they will be missing values based on rule check */
table.alterTable /
caslib="&output_caslib."
columns={{name="TextField1", DROP=TRUE}, {name="TextField2", DROP=TRUE}},
table="&output_table.";
/* promote output table to global in-memory table */
table.promote /
caslib="&output_caslib."
name="&output_table.";
/* save physical copy of output table */
table.save /
caslib="&output_caslib."
name="&output_table."
table={caslib="&output_caslib.", name="&output_table."}
replace=true;
end;
quit;
cas mySession terminate;
Run Tests using the Code
In my code I have set the input table to use the CONTACT_LIST table in the Public CAS library and the fields it is checking are Address and Zip. Here is what that table looks like.
Based on this input table I should have the rows where the ID is 3 and 6 written to my output table. I Submit the code and view the log. As expected, my number of rule violations was 2.
In SAS Data Explorer, I view the resulting output table to display those 2 records.
I now change my code, so my variable settings are using Phone and Email as the input fields for the rule check.
I Submit the code again and see that there are now no violations of the rule check which means an output table was not promoted.
In conclusion, this article shows you a way that you can perform row-based data monitoring checks on SAS Viya using SAS Intelligent Decisioning 5.3 filtering rules similar to what you can you do in Data Management Studio using DataFlux Business Rule Manager. By changing the variable settings at the beginning of the code block you can run this same rule check against many different data sets.
For more information, please refer to the SAS Intelligent Decisioning documentation.
