BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eslna
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
eslna
Obsidian | Level 7
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

2 REPLIES 2
eslna
Obsidian | Level 7
Update: I decided to move those columns and calculate/generate them at the database level. Still same issue.
eslna
Obsidian | Level 7
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 2867 views
  • 0 likes
  • 1 in conversation