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

[#1]

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:

• SAS Demo - Create a Custom Step in SAS Studio on SAS Viya (YouTube)
• My First SAS Studio Flow Custom Step
• SAS Documentation on Custom Steps in SAS Studio.

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.