LOD Function in SAS

[emilyaaa]

Dear All,

I want to implement a calculated item from aggregation of an aggregation. In Tableau I would use LOD function, example:

I want to flag those user with weighted average price over the Role_Bandmax to be 'too_expensive', otherwise just as same as Tier_Role column.

In the table, for TOM, his average price is $174.29, it is over his Role_Bandmax  110, so TOM should be flagged as  'too_expensiv'

 For JERRY, his  average price is $108,27, it is lower than his Role_Bandmax, 130, so JERRY should be flagged as same as his Tier_Role, that is 'tier2'

Transaction_ID	User_ID	User_Role	Role_Bandmax	Tier_Role	price	amount	weighted avg price	new_Flag
1	TOM	developer	110	tier1	100	1	174,2857143	too_expensive
2	TOM	developer	110	tier1	90	2	174,2857143	too_expensive
3	TOM	developer	110	tier1	120	3	174,2857143	too_expensive
4	TOM	developer	110	tier1	300	2	174,2857143	too_expensive
5	TOM	developer	110	tier1	200	5	174,2857143	too_expensive
6	TOM	developer	110	tier1	200	1	174,2857143	too_expensive
7	JERRY	Admin	130	tier2	110	3	108,2666667	tier2
8	JERRY	Admin	130	tier2	120	4	108,2666667	tier2
9	JERRY	Admin	130	tier2	140	5	108,2666667	tier2
10	JERRY	Admin	130	tier2	120	6	108,2666667	tier2
11	JERRY	Admin	130	tier2	110	7	108,2666667	tier2
12	JERRY	Admin	130	tier2	130	8	108,2666667	tier2
13	JERRY	Admin	130	tier2	110	9	108,2666667	tier2
14	JERRY	Admin	130	tier2	90	10	108,2666667	tier2
15	JERRY	Admin	130	tier2	90	11	108,2666667	tier2
16	JERRY	Admin	130	tier2	100	12	108,2666667	tier2

Thanks in advance!

[Madelyn_SAS]

This is posted to the SAS Visual Analytics community but it does not seem like a VA issue. Which SAS product are you using? For example, SAS Studio? 

[emilyaaa]

Hi,

Thx for reply!
I use SAS viya for visual Analytics
As I can do such calculation in other visual Analytics tool, so i would ask how to implement it in SAS way