SAS Viya: Using PROC EEL in SAS Studio

1 Like

For people familiar with coding using the DataFlux/SAS Expression Engine Language (EEL) in Data Management Studio or SAS Event Stream Processing (ESP), you can now use it in SAS Studio in SAS Viya as of the 2022.12 (December 2022) stable release. There are three modes of operation for PROC EEL: Single Expression Mode, Rule Mode, and Discovery Mode. Something to note is that PROC EEL runs in Compute (not CAS) meaning that if your input table is in CAS the data is brought down to Compute for processing.

Single Expression Mode

The single expression mode consists of two types – the simple expression and the grouping expression.

Simple Expression

The simple expression can have EEL code in three sections: Pre, Main, and Post.

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

Note: The numbers represent the order the code is executed.

For this example, my input is below.

The following is the PROC EEL code to indicate that the Country needs standardization if its length is not equal to 3 and it is not in uppercase.

proc eel data=SASDM.country_contacts out=country_stnd ;
  submit ;
 
  pre:
  string(5) Country_Needs_Standardization
 
  main:
  if len(Country)==3 and Country==upper(Country)
      Country_Needs_Standardization = "FALSE"
  else Country_Needs_Standardization = "TRUE"
 
 
endsubmit ;
run ;

Note: For this example, I did not need to use any post expression code.

Here are the results of running that code against my input.

Grouping Expression

The grouping expression can have EEL code in five sections: Pre, Pre-group, Main, Post-group, and Post. You must also specify the column(s) to group by. The data must be pre-sorted by those columns(s).

Note: The numbers represent the order the code is executed.

For this example, I am using the same Country_Contacts input table as I did in the previous example. However, this time I am grouping the data by Country. Note the table has already been pre-sorted by Country as is required for PROC EEL grouping. In the code I am adding up all the ages for each country and writing that total age for each country to a text file.

proc eel data=SASDM.country_contacts ;
  grouping country ;
  submit ;
 
  pre:
  file f
 
  if fileexists("/tmp/country_age.txt")
    deletefile("/tmp/country_age.txt")
 
  f.open("/tmp/country_age.txt", "w")
  f.writeline("Country,Total_Age")
 
 
  pregroup:
  hidden integer total_age
  total_age=0
 
 
  main:
  total_age=total_age+age
 
 
  postgroup:
  logmessage("Total age for " & country & " is " & total_age)
  f.writeline(country & "," & total_age)
 
 
  post:
  f.close()
 
endsubmit ;
run ;

Here are the results of running that code against my input.

Rule Mode

Rule mode allows you to specify a rule data set to perform multiple rule checks on the same input data set. The rules table is specified using the RULES argument. The rules table must contain two columns – for the rule_name and one for the rule itself. A primary key field must be designated for your input table using the INPUTKEY argument.

For this example, my input is below.

The following is the PROC EEL code to perform multiple rule checks against my input table.

/* Multiple rules for missing information */
data ruledata ;
   infile datalines dsd delimiter=',' ;
   input 
   rule_name : $255.
   rule : $32767.
      ;      
      datalines;
 "Address MISSING","Address==''"
 "Zip MISSING","Zip==''"
 "Phone and Email MISSING","Phone=='' AND Email==''"
;
 
 
proc eel data=SASDM.SAMPLE_CONTACTS rules=ruledata out=missing inputkey=ID ;
run ;

Below is the resulting rule table:

If a record violates one of the rules its primary key field is written to the output as well as the rule name that was violated. Note: A single record could violate more than one rule. Here are the results of running that code against my input.

Discovery Mode

For discovery mode, no input table is used. Instead, it analyzes the specified rules table provided in the RULES argument and returns the variables that are needed in an input table to perform the rule checks.

The following is the PROC EEL code to return the columns needed to perform rule checks from the RULEDATA table in the previous example.

/* Multiple rules for missing information */
data ruledata ;
   infile datalines dsd delimiter=',' ;
   input 
   rule_name : $255.
   rule : $32767.
      ;      
      datalines;
 "Address MISSING","Address==''"
 "Zip MISSING","Zip==''"
 "Phone and Email MISSING","Phone=='' AND Email==''"
;
 
 
proc eel rules=ruledata out=discovery ;
run ;

Here are the results of running that code.

Upcoming Blog Series on using PROC EEL for Data Monitoring

For the Data Management Studio users who are familiar with Business Rule Manager and data monitoring, you will recall that a business rule is one of three types: Row, Group, or Set. Also, those business rules are simply EEL code behind the scenes.

Therefore, in a four-part blog series, I cover examples of those three data monitoring rule types and an example with multiple rules by using PROC EEL in both SAS code and Custom Steps in SAS Studio powered by SAS Viya.

and Upcoming:

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

Summary

In conclusion, you can now use Expression Engine Language code in SAS Studio powered by SAS Viya by utilizing PROC EEL. For more information, 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 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.