SAS Viya: Group-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
Group-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step (this post)
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

Group-based Monitoring Rule using PROC EEL

For this demonstration on using PROC EEL to mimic the behavior of a group-based business rule in DataFlux Business Rule Manager, I will use the example of monitoring for employees who exceed their sales target. In my example, if an employee has an order total of more than 50000 on the supplied input set, then they have exceeded their sales target and are eligible for a bonus. (Sometimes data monitoring can be positive!)

Below is the Orders input table, I use for my rule check.

Select any image to see a larger version.
Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.

Here is the PROC EEL code to perform the check on whether the employee exceeded their sales target and therefore should receive a bonus. Note: For a grouping in EEL, the input data must be sorted by the column(s) you are grouping. In my case, I am grouping by employee_id since I need to add up the order totals for each employee.

/* Input data must be sorted prior to performing grouping in EEL */
proc sort data=SASDM.ORDERS out=input_sorted ;
   by employee_id ;


proc eel data=input_sorted out=SASDM.EMPLOYEES_EXCEEDED_TARGET (keep=employee_id total_order_amount);
  grouping employee_id ;
  submit ;
  

  pregroup:
  integer total_order_amount
  total_order_amount=0


  main:
  total_order_amount=total_order_amount+order_total
  return false

  postgroup:
  logmessage("Total order amount for Employee id " & employee_id & " is " & total_order_amount)
  // check for exceeding target order amount
  if total_order_amount > 50000
     pushrow()

  
endsubmit ;
run ;

/* Delete temporary sorted table */
proc datasets library=work ;
	delete input_sorted ;
run ;

Make note of the following in the code:

keep option on the proc eel statement to specify the column names to keep in the output
return false statement in the main section to prevent the individual rows from the input from being returned in the output
pushrow() statement in the postgroup section to return a summary row to the output if the target amount of 50000 is exceeded

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

Employees 3 and 9 have exceeded the target of 50,000 and should receive a bonus.

Group-based Monitoring Rule in a Custom Step

Instead of hard-coding the input table, the employee_id column, order_amount column, the sales target, and the output table, I decided to create a custom step where the user can select all 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
Column selector for employee_id field
Column select for sales_amount field
Sales target amount
Output table.

In my design, I set the target total sales amount to a default of 50,000, but that can be changed by the user when they use the custom step.

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

{
	"showPageContentOnly": true,
	"pages": [
		{
			"id": "page1",
			"type": "page",
			"label": "Grouping Rule to Find Employees Who Exceeded Their Target",
			"children": [
				{
					"id": "inTable",
					"type": "inputtable",
					"label": "Select the input table",
					"required": true,
					"placeholder": "",
					"visible": ""
				},
				{
					"id": "employee_id",
					"type": "columnselector",
					"label": "Select the Employee ID field",
					"order": false,
					"columntype": "a",
					"max": 1,
					"min": 1,
					"visible": "",
					"table": "inTable"
				},
				{
					"id": "sales_amount",
					"type": "columnselector",
					"label": "Select the Sales Amount field",
					"order": false,
					"columntype": "a",
					"max": 1,
					"min": 1,
					"visible": "",
					"table": "inTable"
				},
				{
					"id": "target",
					"type": "numstepper",
					"label": "Specify the total sales target amount",
					"required": true,
					"integer": true,
					"min": null,
					"max": null,
					"stepsize": 5000
				},
				{
					"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 a group-based rule check to monitor whether or not employees exceeded their specified sales target.\n\n\nVersion 1.0 (12APR2023)",
					"visible": ""
				}
			]
		}
	],
	"values": {
		"inTable": {
			"library": "",
			"table": ""
		},
		"employee_id": [],
		"sales_amount": [],
		"target": 50000,
		"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 GetVar, SetVar, and FieldValue from the expression language to retrieve the macro variable values for the selected columns.

/* Data needs to be sorted prior to grouping EEL statement */
proc sort data=&inTable out=inTable_sorted ;
  by &employee_id_1_name ;  


proc eel data=inTable_sorted out=&outTable (keep=&employee_id_1_name total_order_amount);
  grouping &employee_id_1_name ; 
  submit ;

  pre:
  /* Loop thru to find the selected fields */
  hidden integer i
  begin
  	for i = 1 to FieldCount()
		begin
			if FieldName(i)==getvar("employee_id_1_name")
       			setvar("Employee_idx",i)
			if FieldName(i)==getvar("sales_amount_1_name")
       			setvar("SalesAmt_idx",i)
		end
	end
  
  
  hidden integer targetAmount
  targetAmount = getvar("target")
  

  pregroup:
  integer total_order_amount
  total_order_amount=0
  

  main:
  total_order_amount=total_order_amount+FieldValue(getvar("SalesAmt_idx"))
  return false


  postgroup: 
  logmessage("Total order amount for " & getvar("employee_id_1_name") & " " & FieldValue(getvar("Employee_idx")) & " is " & total_order_amount) 
  // check for exceeding target order amount
  if total_order_amount > targetAmount
     pushrow()  

  
endsubmit ;
run ;


/* Delete temporary sorted table */
proc datasets library=work ;
	delete inTable_sorted ;
run ;

Running the Custom Step

When I run the custom step, I use the same input table as my example above and map the EMPLOYEE_ID column for the employee_id column and the ORDER_TOTAL column for the sales_amount column. I leave the target total sales amount at the default of 50,000.

My results are the same – Employee IDs 3 and 9 are returned for exceeding the target of 50,000.

Since I have set this as a custom step where the inputs for the check are generic, I can easily change any of the information. I change the target sales amount to 100,000.

In this case, only Employee ID 3 is returned for exceeding that new sales target.

Summary

In conclusion, you can use PROC EEL combined with Custom Steps to perform group-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 my colleagues:

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.