topic Re: SAS drop down selection and aggregation in SAS Visual Analytics

SAS drop down selection and aggregation

vietlinh12hoa — Wed, 05 May 2021 17:26:54 GMT

Hi everyone,

I have the input table:

ID	Low Level	High Level	Cost
A	0	1	23
A	1	0	23
B	1	0	42
B	0	1	42
C	0	0	12
C	1	0	12
D	0	0	5

meaning that an ID can be in both Level.

What I would like to do is to have a drop down selection for Level.

If I select Low Level. Only low level average cost display (average cost = (23 + 42 + 12) / 3 =25.7, and there are 3 unique IDs

Only Low Level

(Low Level = 1 and High Level = 0)

Average Cost

Number of ID

25,7

If I select High Level. Only high level average cost display (average cost = (23 + 42) / 2 =32.5, and there are 2 unique IDs

Only High Level

(Low Level = 0 and High Level =1)

Average Cost

Number of ID

32,5

As default (no selection), it will take into account all level with unique ID. (average cost = (23 + 42 +12 + 5) / 2 =20.5, and all unique IDs are counted.

All (Unique ID)
Average Cost	Number of ID
20,5	4

How can we do that for this drop down aggregation?

Re: SAS drop down selection and aggregation

Sam_SAS — Wed, 05 May 2021 19:18:27 GMT

Hello,

Would it make sense to create a calculated column "Level" with an expression like this?

IF ( ( 'High Level'n = 1 ) AND ( 'Low Level'n = 0 ) )
RETURN 'High Level'
ELSE (
IF ( ( 'Low Level'n = 1) AND ( 'High Level'n = 0 ) )
RETURN 'Low Level'
ELSE 'Neither' )

Then you could assign this Level column to a button bar control.

I think that would achieve the result that you are looking for. An extra clause might be needed to handle a "Both" case where both values are 1. I don't know if that is possible in your data.

Let us know if that helps,

Sam

Re: SAS drop down selection and aggregation

vietlinh12hoa — Wed, 05 May 2021 19:27:26 GMT

Thanks for the answers. Ideally, an ID can't be either High or Low. So you mean now we have 4 drop down selection for Level Column ('HIgh', 'Low', 'Neither', ' ' ) (Empty blank for all).

But I wonder the rule for aggregation in VIYA will be a little bit different when when no selection (All is considered).

Re: SAS drop down selection and aggregation

Sam_SAS — Wed, 05 May 2021 19:37:53 GMT

I think that the expression I gave above should work. You will have a button bar like this:

[[High Level] [Low Level] [Neither]]

If the user does not make a selection on the button bar, then all values of the column will be displayed. There is no need for an additional "All" value in the calculated column.

Sam

Re: SAS drop down selection and aggregation

vietlinh12hoa — Wed, 05 May 2021 21:41:43 GMT

I see. but when there is no selection, the table should only display unique values, as seen in the last table only 4 unique IDs are aggregated.

Re: SAS drop down selection and aggregation

Sam_SAS — Thu, 06 May 2021 13:51:22 GMT

I am getting this output using your data:

I calculated the distinct count for ID. To eliminate "Neither" from the button bar, I just created a filter on the button bar to remove that value.

The Cost value when nothing is selected is the average for the full data set, rather than the average of the averages for the ID. I believe there is a workaround for this if you want the value to be aggregated that way, but I don't remember how to do it.

You could also use a drop-down list control instead of a button bar. When there are only two selections, I like button bars.

Is this closer to what you wanted?

Thanks,
Sam

Re: SAS drop down selection and aggregation

Sam_SAS — Thu, 06 May 2021 13:56:20 GMT

Replacing Cost with this calculation will give you 20.5:

AggregateTable(_Avg_, Table(_Avg_, Fixed('ID'n), 'Cost'n))

Re: SAS drop down selection and aggregation

Sam_SAS — Thu, 06 May 2021 13:59:26 GMT

So with that change and using a drop-down control we get this:

Re: SAS drop down selection and aggregation

vietlinh12hoa — Thu, 06 May 2021 15:03:14 GMT

That's great thank you very much for your help

Re: SAS drop down selection and aggregation

vietlinh12hoa — Thu, 06 May 2021 15:06:55 GMT

Thank you very much. It works well.

Re: SAS drop down selection and aggregation

Sam_SAS — Thu, 06 May 2021 15:11:25 GMT

Excellent! Thanks for letting us know