Using Calculated Data Items in SAS Visual Analytics

2 Likes

The ability to create new calculations for your reports is a fundamental requirement for any good report building tool. SAS Visual Analytics has an “easy button” to create common calculations called quick calculations and also a manual way to define the expression for both calculated data items and aggregated measures.

Calculated Data Item versus Aggregated Measure

Before I start showing you screenshots, let’s first understand the difference between calculated data items versus aggregated measures.

Calculated Data Item	Aggregated Measure
The expression is evaluated for each row of unaggregated data.	The expression is evaluated on aggregated data.
Expression is evaluated for each row in the source data, before aggregations are performed.	Aggregations are evaluated by context: By Group – calculates aggregation for each subset of the data item For All – calculates aggregation for the entire data item (after filtering)

The key point to take away is that for a Calculated Data Item the calculation is performed at the row level, then aggregated and for Aggregated Measure it defines how to perform the aggregation within the expression.

The easiest way to demonstrate this is using ratios. We have all learned the rule that you cannot just straight sum ratios, you must first sum the numerators then sum the denominators and then perform the division.

In this example, the ratio expression for Unit Yield is shown in the below screenshot for both the Calculated Data Item and the Aggregated Measure. For ease of readability, I have zoomed in on the expression. I have applied a percent format that you can see in the List Table objects below.

Calculated Data Item Expression

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

Aggregated Measure Expression

In the Detailed Data List Table, both expressions evaluate correctly. We can see the difference in how the expressions evaluate at the aggregated data level.

In the Aggregated Data List Table, we can see that the Calculated Data Item expression is evaluated for each row of data then aggregated. In this case, I left the default aggregation to SUM and we can easily see that this is not the correct evaluation for this expression. In the Aggregated Measure expression, the aggregation is done as part of the evaluation. The aggregation SUM _ByGroup_ is performed for the numerator subtraction, then the denominator, then the ratio is evaluated. We can see that for the Aggregated Measure we get the correct evaluation when the data is aggregated.

Now that we understand the differences between a Calculated Data Item and Aggregated Measure, the question as to when to use one versus the other will depend on the type of report object you use, what level of detail will be displayed, and if the expression requires an aggregated measure operator.

Quick Calculations

Quick Calculations are the “easy button” for creating commonly used Aggregated Measures. For additional detail see the SAS Documentation Working with Quick Calculations in a Report.

To create a Quick Calculation, right-click on any data item and select New calculation....

There are different types of quick calculations for the types of data items.

Date and Datetime Data Items

The Min and Max aggregated measures for Date/Datetime data items were introduced in the SAS Viya LTS 2021.2 (November 2021) release.

Category Data Items

Measure Data Items

If the data source has date/datetime data items, then the measure data items will have periodic quick calculations available.

If the data source does not have date/datetime data items, then these will be the available quick calculations.

Once you select a Quick Calculation it automatically gets grouped with the Aggregated Measures in the Data pane and you can right-click to edit if desired.

The Quick Calculation can be used like any other Aggregated Measure in report objects. If you are using any of the periodic aggregated operators, then the report object must contain the same date/datetime data item in the role assignment.

Using the Expression Editor

If you want to manually build an expression for either a Calculated Data Item or Aggregated Measure you will use the Expression Editor from the + New data item menu and select Calculated item.

Here are a few tips when using the Expression Editor. First select your desired result type. If you know you want to build an Aggregated Measure then select that. If you know you want to build a Calculated Data Item then select the type of result you want date, datetime, character or numeric.

It is best to first select the result type so that the error messages generated are with respect to your target expression. If you start adding Operators to the expression first, then the result type will change automatically, and you may get hung up depending on the order in which you add your operators.

The Visual mode, as seen below, is great for seeing groups of expressions and what variables may be missing.

The Text mode, as seen below, can make it more difficult to spot the missing variables but is much easier when wanting to copy-paste.

Defining the expression

Use the Operators tab to build your expression. Like many things, it’s all personal preference as to the order in which you’d like to build your expression, but I like to build the structure first before I add Data Items.

Use this excellent Reference: Operators for Data Expressions for more details about the available Operators.

Here is a demonstration of how to build an Aggregated Measure. If you wanted to build only a Calculated Data Item then you would skip over adding the Aggregated Operators.

(view in My Videos)

I have a separate post about how to Build a date in SAS Visual Analytics: Article | YouTube.

Summary

You should now have a solid understanding of how SAS Visual Analytics uses the terms Calculated Data Items versus Aggregated Measures. I’ve given you an example to show how these data items vary in the way they are evaluated during aggregation.

I’ve also showed you how to leverage the “easy button” for Quick Calculations and how to build your own custom calculations using the Expression Editor.

I have additional examples solving business problems and working with the different types of data items such as dates and character data items.

Additional Resources

Find more articles from SAS Global Enablement and Learning here.