SAS Viya: Multi-Rule Data Monitoring Using PROC EEL in SAS Code and Custom Steps

In a previous blog, I reviewed using PROC EEL in SAS Studio powered by SAS Viya. In this four-part blog series, I cover examples of the three data monitoring rule types (row, group, and set) and an example with multiple rules by using PROC EEL in both SAS code and Custom Steps in SAS Studio.

Row-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step
Group-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step
Set-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step
Multiple Row-based Data Monitoring Rules using PROC EEL in SAS Code and a Custom Step (this blog)

Multi-Rule Monitoring using PROC EEL

For this demonstration on using PROC EEL for multi-rule I am going to check some columns on a table for missing values.

Below is the Contact List with Email input table, I use for my rule check.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

Here is the PROC EEL code that builds the rule table and performs the check for the Address, Zip, Phone, and Email columns for missing values. Note: I specified the primary key column for the input table using the inputkey option.

Here are the results when I run this code against the input table. The output is written to the table called MISSING as specified in the code.

The table lists the primary key (ID) and the rule_name that was violated for that record. A record could fail more than rule check (e.g., IDs 3 and 6).

Multi-Rule Monitoring in a Custom Step

Instead of hard-coding the input table, the primary key column, the columns to check for blanks, and the output table, I decided to create a custom step where the user can select that information. For more details on creating custom steps refer to the following:

Creating the Custom Step

On the main page of my custom step, I add controls for the selection of the following:

Input table
Primary key column
Output table.

Note: The columns for the rule table are determined in the Program code for the custom step. It will create a rule to check all columns except the primary key column for missing values (blanks).

Below is the JSON for the Prompt UI design for this custom step.

{
	"showPageContentOnly": true,
	"pages": [
		{
			"id": "page1",
			"type": "page",
			"label": "Multiple Rules - Check all fields for missing values",
			"children": [
				{
					"id": "inTable",
					"type": "inputtable",
					"label": "Select the input table",
					"required": true,
					"placeholder": "",
					"visible": ""
				},
				{
					"id": "primaryKey",
					"type": "columnselector",
					"label": "Select the primay key field",
					"order": false,
					"columntype": "a",
					"max": 1,
					"min": 1,
					"visible": "",
					"table": "inTable"
				},
				{
					"id": "outTable",
					"type": "outputtable",
					"label": "Select the output table",
					"required": true,
					"placeholder": "",
					"visible": ""
				}
			]
		},
		{
			"id": "page2",
			"type": "page",
			"label": "About",
			"children": [
				{
					"id": "aboutText",
					"type": "text",
					"text": "This custom step performs multiple rule checks.  It checks every column of the selected input table (except for the primary column) for blank values.\n\n\nVersion 1.0 (12APR2023)",
					"visible": ""
				}
			]
		}
	],
	"values": {
		"inTable": {
			"library": "",
			"table": ""
		},
		"primaryKey": [],
		"outTable": {
			"library": "",
			"table": ""
		}
	}
}

Here is the Program code to perform the row rule check using PROC EEL. Note: It is similar to the code in my above examples but uses the functions FieldCount and FieldName from the expression language to retrieve the field count and field name values for the columns on the input table.

/* Multiple Row-rules for missing information */
 
/* Build Rules File to check for missing data from all fields from the input table except the one indicated as the primary key */
proc eel data=&inTable out=ruledata (keep=rule_name rule);
  submit ;
 
  pre:
  string(255) rule_name
  string(32767) rule
 
  /* Loop thru to get all the fields from the input table */
  hidden integer i
  for i = 1 to FieldCount()
    begin
	  if FieldName(i)!=getvar("primaryKey_1_name")
        begin
           rule_name=FieldName(i) & " MISSING" 
           rule=FieldName(i) &"==''"
           pushrow()
        end
    end
 
 
  main:
  /* do not return any row info from the input table */
  return false
 
endsubmit ;
run ;
 
 
proc eel data=&inTable rules=ruledata out=&outTable inputkey=&primaryKey ;
quit ;

Running the Custom Step

When I run the custom step, I use the same input table as my example above and select the primary key column as ID and set the output table.

The RULEDATA table is created with a rule check for MISSING for each column of the input table except the specified primary key column.

My results are the same with the exception that I have now run the check for all columns on the table except the primary key column.

Since I have set this as a custom step where the inputs for the set rule check are generic, I can easily change the table to check for missing values. I have changed the input table to ORDERS and its primary key field is ORDER_ID.

In this case, the RULEDATA table now has the rule checks based on this input table’s columns.

And the MISSING_CHECK table has its results.

No rows were written to the output table indicating that none of the columns on the specified input table (ORDERS) are missing any values.

Summary

In conclusion, you can use PROC EEL combined with Custom Steps to perform multi-rule monitoring of your data where the inputs for the rule check are not hard-coded into the program. For more information on Expression Engine Language coding, please refer to the following:

PROC EEL Statement documentation
Expression Language: Reference Guide
Using PROC EEL video by Arnold Toporowski

Acknowledgements

Special thanks to:

Mickey Schauf for providing the inspiration to create this blog series
Andrew Shakinovsky for taking the time to let me talk through my ideas for this blog series enabling me to confirm that the concept was possible!

Find more articles from SAS Global Enablement and Learning here.