SAS Visual Analytics: Sum Missing Values
At first glance, you might be wondering if you read this correctly, because any good SAS coder worth their salt, knows that the SUM function handles missing values, well it ignores them when using the SUM function and sums the nonmissing values. But what about when you use addition? SAS Visual Analytics actually has similar behavior to SAS code for both sum and addition but one is applied automatically and the other is manual. Let’s look at how you can get the values you want without having to go back to the raw data.
SAS Code
Here is a SAS code example using the SUM Function and basic addition.
To get the total expenses, we are summing or adding the four underlying expenses: capital, material, operational, and staffing. Notice that for the two Product Lines, Promotional and Stuffed Animal, there are missing capital expenses.
Zeros and missings are not the same and should not be interchanged in the raw data. Please take a look at Charu Shankar’s blog: Is something missing or is it a ZERO? As you’ll read, the most impacted computation is when you average the data.
When you sum or add the data, SAS gives you two types of behavior. The SUM Function will return the result while the addition expression will return missing if any of the operands have a missing value.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
SAS Visual Analytics
Let’s take a look at what happens in SAS Visual Analytics. Ultimately, the rule of thumb is that any automatically totaled data performed by the object, uses a SUM Function-like expression. It isn’t the exact same, since SAS Visual Analytics is using the CAS (Cloud Analytic Services) engine and uses CAS actions, but the result is the same.
Here is the sum of Expenses (capital) using the automatic Option for the List table and Crosstab objects. The aggregation used in the Bar chart defaults to sum. Each of these use the CAS summary action. The automatic totaling we are seeing here is using the aggregation type defined for the measure data item Expenses (capital) which is SUM.
This is not the same as the expression where we are summing all of the different expenses like above, unfortunately, this type of expression is not available in SAS Visual Analytics.
Now let’s take a look at the addition expression, like the code line highlighted below. This is an expression we can reproduce in SAS Visual Analytics and we get the same results as we would when coding.
In Visual Analytics, here is the new calculated data item expression.
And here are the results in a List table compared to the SAS Studio code results. We can see we get the same results.
Now let’s look how we can reproduce the Sum column in SAS Visual Analytics.
We are not going to change the underlying raw data where if the value is missing, change it to a zero. But we can use the IF… ELSE operator and the Missing function to check to see if the value is missing, if it is then use a zero in the addition expression, else use the actual value.
Now we get the sum of missings just like we did with the Sum function in SAS code but we are really using the addition operator.
Now you can preserve the integrity of your source data’s missing values and still get the sum of values in your report.
Learn More
New Expression Editor:
- SAS Visual Analytics: A look at the new Expression Editor (Part 1)
- SAS Visual Analytics: A look at the new Expression Editor (Part 2)
Find more articles from SAS Global Enablement and Learning here.
