Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

SAS Visual Analytics: Missing values affecting Aggregations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

SAS Visual Analytics: Missing values affecting Aggregations

I may or may not have found a bug in SAS VA.

 

I have columns that are coming from Oracle and colums that are calulated at the Data Query level.

 

Here are the calculated columns:

 

Column a: CASE WHEN  (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Comp Time Earned', 'Comp Time Earned II')) THEN  O_OVT_COMPTIME_FV.HOURS_EARNED  END

 

Column b:CASE WHEN  (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Overtime', 'Overtime SD', 'OT ADJ', 'OT SD ADJ')) THEN  O_OVT_COMPTIME_FV.HOURS_EARNED END

 

Column c: CASE WHEN  (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Regular', 'REG ADJ', 'REG SD ADJ', 'Regular SD')) THEN  O_OVT_COMPTIME_FV.HOURS_EARNED  END

 

 

 

 

On the Report, I have created 3 aggregate fields based on each of these columns, the aggregate fields are the following:

 

Sum [_ByGroup_] ('Column a'n)

Sum [_ByGroup_] ('Column b'n)

Sum [_ByGroup_] ('Column c'n)

 

 

I have a Table object(same thing happens in a crosstab) that is supposed to display these aggregate values by the group and if all 3 columns mentioned above have data, then the row is displayed with no problem.

 

However, I have noticed that records that have missing values in, say Column a, wont show up in the table object(or the crosstab) once I add Column a to the object. If i remove column a from the table object, the row shows up again.

 

Something about that SUM is causing the record to dissapear from the table object, and no, i dont have any filters on that particular object.

 

I tried doing an IF check in that aggregate field, but it will not let me check "Column a" against missing or zero, it forces me to use "Sum [_ByGroup_] ('Column a'n)" in the comparison.

 

I have also tried adding an Else 0 to all the column calculations in the data query but that doesnt help either.

 

Any ideas?


Accepted Solutions
Solution
‎11-02-2016 02:59 PM
Occasional Contributor
Posts: 16

Re: SAS Visual Analytics: Missing values affecting Aggregations

I feel like I am talking to myself since no one responded but basically what I am trying to do, which is filter a table object by an aggregated value, is not possible in SAS VA 7.1, but good news is it IS possible in 7.3 judging by this document: http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#p0989k7vl59ugbn1183c1...

So i will mark this ticket as solved and work on getting our environment upgraded.

View solution in original post


All Replies
Occasional Contributor
Posts: 16

Re: SAS Visual Analytics: Missing values affecting Aggregations

Update: I decided to move those columns and calculate/generate them at the database level. Still same issue.
Solution
‎11-02-2016 02:59 PM
Occasional Contributor
Posts: 16

Re: SAS Visual Analytics: Missing values affecting Aggregations

I feel like I am talking to myself since no one responded but basically what I am trying to do, which is filter a table object by an aggregated value, is not possible in SAS VA 7.1, but good news is it IS possible in 7.3 judging by this document: http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#p0989k7vl59ugbn1183c1...

So i will mark this ticket as solved and work on getting our environment upgraded.
Post a Question
Discussion Stats
  • 2 replies
  • 368 views
  • 0 likes
  • 1 in conversation