SAS Viya: Row-based Data Monitoring Using PROC EEL in SAS Code and Custom Steps

In a previous post, I reviewed using PROC EEL in SAS Studio powered by SAS Viya. In this four-part 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 (this post)
SAS Viya: Group-based Data Monitoring Using PROC EEL in SAS Code and Custom Steps
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

Row-based Monitoring Rule using PROC EEL

For this demonstration on using PROC EEL 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 Address and Postal (Zip) Code information are missing from a row in the data set, then that row should be returned. (I wrote a similar post several years ago using a SAS Intelligent Decisioning rule).

Below is the 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 to perform the check on whether both the Address and Zip fields are missing from a row in the input data. This code returns only the rows where the rule check is violated (i.e., return true).

/* Row-rule to check for 2 fields missing values */
 
proc eel data=SASDM.CONTACT_LIST_WITH_EMAIL out=test1;
  submit;
  if Address=='' and Zip==''
    return true
  return false
endsubmit;
 
proc print data = test1 noobs; TITLE "EEL output"; run;
 
run;

Here are the results when I run this code against the input table.

Note only rows 3 and 6 are returned. They are the only rows in the input table where both Adddress and Zip are blank.

Using Return Status in PROC EEL

This next example is the same as the previous one except for using the return status option. I use the same input and note the code is the same; however, there is now the addition of returnstatus=RuleCheckFailed as part of the options for the PROC EEL statement.

/* Row-rule to check for 2 fields missing values */
 
proc eel data=SASDM.CONTACT_LIST_WITH_EMAIL out=test2 returnstatus=RuleCheckFailed;
  submit;
  if Address=='' and Zip==''
    return true
  return false
endsubmit;
 
proc print data = test2 noobs; TITLE "EEL output"; run;
 
run;

This time the results return every row and flag that rows 3 and 6 violate the row rule check indicated by a 1 in the RuleCheckFailed column which is the name of the return status column in the code.

Row-based Monitoring Rule in a Custom Step

Instead of hard-coding the input table, its two columns to check for blanks, and the output table, I decided to create a custom step where the user can select the input and output tables and the two columns. 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
Two columns to check for blanks
Output table.

For the Options page, I add a check box control to create a Return Status field. If that option is checked, then the New Column control is displayed to set the name of the return status column.

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

{
	"showPageContentOnly": true,
	"pages": [
		{
			"id": "page1",
			"type": "page",
			"label": "Row Rule Check for Both Fields Blank",
			"children": [
				{
					"id": "inTable",
					"type": "inputtable",
					"label": "Select the input table",
					"required": true,
					"placeholder": "",
					"visible": ""
				},
				{
					"id": "monitorColumns",
					"type": "columnselector",
					"label": "Select the two columns to check for blanks",
					"order": false,
					"columntype": "a",
					"max": 2,
					"min": 2,
					"visible": "",
					"table": "inTable"
				},
				{
					"id": "outTable",
					"type": "outputtable",
					"label": "Select the output table",
					"required": true,
					"placeholder": "",
					"visible": ""
				}
			]
		},
		{
			"id": "page2",
			"type": "page",
			"label": "Options",
			"children": [
				{
					"id": "returnStatus",
					"type": "checkbox",
					"label": "Create Return Status Field",
					"visible": ""
				},
				{
					"id": "returnStatusField",
					"type": "newcolumn",
					"label": "Return Status Field Name",
					"required": true,
					"placeholder": "",
					"hideproperties": false,
					"readonly": false,
					"visible": "$returnStatus"
				}
			]
		},
		{
			"id": "page3",
			"type": "page",
			"label": "About",
			"children": [
				{
					"id": "aboutText",
					"type": "text",
					"text": "This custom step performs a row-based rule check to monitor whether or not the two selected fields from the input table are both blank.\n\n\nVersion 1.0 (12APR2023)",
					"visible": ""
				}
			]
		}
	],
	"values": {
		"inTable": {
			"library": "",
			"table": ""
		},
		"monitorColumns": [],
		"outTable": {
			"library": "",
			"table": ""
		},
		"returnStatus": false,
		"returnStatusField": {
			"value": "RuleCheckFailed",
			"label": "Return Status Value",
			"type": "n"
		}
	}
}

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 VarEval and GetVar from the expression language to retrieve the macro variable values for the selected columns.

/* Row-rule to check for 2 fields missing values */
 
/* get returnStatus info */
%let ReturnStatusText= ;
%if &returnStatus=1 %then %do ;
   %let ReturnStatusText=%nrquote(returnstatus=&returnStatusField_name) ;
%end ;
 
 
proc eel data=&inTable out=&outTable &ReturnStatusText ;
submit;
/* Rule check for both fields missing */
if VarEval(getvar("monitorColumns_1_name"))=='' AND VarEval(getvar("monitorColumns_2_name"))==''
     return true
return false
endsubmit;
run;

Running the Custom Step

When I run the custom step, I use the same input table as my examples above and map the Address and Zip columns as the fields to monitor for blanks.

My results are the same – records 3 and 6 violate my rule check.

Next, I add the option for return status.

Again, I get the same results as the return status example above – all records are returned, but records 3 and 6 are flagged for violating my rule check.

However, since I have set this as a custom step where the inputs for the blank fields check are generic, I can easily change the fields that are checked to Phone and Email. I also remove the option for creating a return status flag.

In this case, there are no violations to my rule check, so my output table is empty.

Summary

In conclusion, you can use PROC EEL combined with Custom Steps to perform row-based 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 series
Andrew Shakinovsky for taking the time to let me talk through my ideas for this series enabling me to confirm that the concept was possible!

Find more articles from SAS Global Enablement and Learning here.