SAS drop down selection and aggregation

[vietlinh12hoa]

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	3

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	2

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?

[Sam_SAS]

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

[vietlinh12hoa]

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). 

[Sam_SAS]

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

[vietlinh12hoa]

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.

[Sam_SAS]

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

[Sam_SAS]

Replacing Cost with this calculation will give you 20.5:

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

[Sam_SAS · Accepted solution]

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

[vietlinh12hoa]

That's great thank you very much for your help

[vietlinh12hoa]

Thank you very much. It works well.

[Sam_SAS]

Excellent! Thanks for letting us know