We’re smarter together. Learn from this collection of community knowledge and add your expertise.

DataFlux Expression Engine Language (EEL) for SAS Programmers

by SAS Employee StephenFoerster on ‎03-22-2017 02:58 PM (702 Views)

When you hear the term EEL you might think sushi (yum!) but if you’re a base SAS programmer you should think Data Step because they are pretty similar. Sure, there are some differences but, they're more similar than not. So for all you base SAS guru’s out there, let's look at how you can leverage your data step skills to create some DataFlux EEL code….

 

Basic Example -- Simple Field Manipulation

 

First let’s look at a pretty basic data step. This example performs some basic if/then logic to create a new field, net_sales from the input SASHELP.SHOES table.

data shoes_out; set sashelp.shoes;
if sales > returns then net_sales = sales - returns;
else net_sales = .;

run;

So what would this look like in EEL? Well, honestly, it’s pretty much the same thing. Below is the equivalent EEL code. As you can see, you don’t need the semicolons ( ; ) and the dot/period (.) gets replaced with the word, null.

 

EEL_for_SAS1.JPG

 

The big trick/difference here is that, in DataFlux, you can’t introduce a new variable (net_sales in our example) on the fly. You always need to define it first (like using an ATTRIB statement in SAS). DataFlux has a special place for this, the “Pre-Processing” tab.

 

EEL_for_SAS2.JPG

 

Now if you’re really new to DataFlux and you’re wondering how we tie this logic to the input SASHELP.SHOES table in the first place, this is done by dragging in a “Data Source” object, configuring it to reference the dataset, and connecting it to an Expression node where we put our EEL code.

 

EEL_for_SAS3.JPG

Now that you understand the example, what have we learned? Data Step processes one record at a time, and EEL processes one record at a time. Data Step uses procedural programming statements like If/Then/Else, and EEL uses procedural programming statements like If/Then/Else…. At the end of the day, Data Step and EEL are pretty much the same thing and you can use your skills in one to work in the other.

 

Output = Pushrow()

 

Now that you've gotten the basics, what if you want to do something a little more complex? What if you don’t want to output one row for every input row? What if you want to create multiple records from one input record? Well in Data Step, we use the OUTPUT statement to do that. In EEL, we use the PUSHROW() statement. Let’s look at another example. Here we use the same input table, SHOES, as before but use multiple OUTPUT statements to create three output rows from each input row.

data shoes_out;
set sashelp.shoes;
length Metric_Name $32 Metric_Value 8;
drop sales inventory returns;
Metric_Name = 'Sales'; Metric_Value = Sales; Output;
Metric_Name = 'Inventory'; Metric_Value = Inventory; Output;
Metric_Name = 'Returns'; Metric_Value = Returns; Output;

run;

So (just like we asked before) what would this look like in EEL? Again, it looks pretty much the same. Like before, we dropped the semicolons and we defined our new variables in the Pre-Processing section (not shown). And, as discussed, we changed the OUTPUT statements to PUSHROW() statements.

 

EEL_for_SAS4.JPG

 

The only major difference in the EEL code is the need to put the statement, FALSE, at the end of the logic. This is required because EEL works differently than Data Step in this situation. In Data Step, when you issue any OUTPUT statements, SAS stops auto-outputting records. DataFlux, on the other hand, always creates one output record for every record automatically, regardless of any PUSHROW() statements. Using FALSE tells DataFlux not to auto-output a record. In our example, if you didn’t specify FALSE, you’d get 4 output records per input instead of the desired 3.

 

Let’s start coding some EEL!

 

So now you know enough to be dangerous and can probably go code yourself up some EEL, right? No doubt, but you’ll need some more resources first. After all, while the concepts (if/then/else, variable assignment/creation) are the same, some of the statements and functions to perform those concepts are different (e.g. Output vs Pushrow()). So, you'll need the DataFlux Expression Language Reference.

 

Good luck!

Contributors
Your turn
Sign In!

Want to write an article? Sign in with your profile.


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.