hi, i am new in SAS VA , i have a table like this
id revenue
1 10
2 50
3 4
4 15
5 12
6 25
the user need to choose a rank to see the first N id (eg first 3 id with max revenue: 2, 4 and 6) and have the rate between (50+15+25)/(10+50+4+15+12+25)= 0,77....
There are a couple ways to get what you want, but I think the most flexible way is to create a parameter 'topN_cutoff' and a calculation 'topN_revenue' like:
if 'revenue'n > 'topN_cutoff'
return 'revenue'n
else 0
Then you can create an aggregated calculation like this:
sum [_ForAll_] ('topN_revenue'n) / sum [_ForAll_] ( 'revenue'n )
The only sticky point is that you need to know what the cutoff revenue is beforehand. For any given data, it is easy to find it out with a list table and a rank applied for top 3 (or whatever rank). But I don't think VA has an operator you can use to determine it automatically. The closest would be the Percentile operator which gives you the revenue value in the data for a specified percentile, but that won't correspond to a specific numeric rank (unless, again, you already know the data and can just determine what percentile corresponds with top N).
There are a couple ways to get what you want, but I think the most flexible way is to create a parameter 'topN_cutoff' and a calculation 'topN_revenue' like:
if 'revenue'n > 'topN_cutoff'
return 'revenue'n
else 0
Then you can create an aggregated calculation like this:
sum [_ForAll_] ('topN_revenue'n) / sum [_ForAll_] ( 'revenue'n )
The only sticky point is that you need to know what the cutoff revenue is beforehand. For any given data, it is easy to find it out with a list table and a rank applied for top 3 (or whatever rank). But I don't think VA has an operator you can use to determine it automatically. The closest would be the Percentile operator which gives you the revenue value in the data for a specified percentile, but that won't correspond to a specific numeric rank (unless, again, you already know the data and can just determine what percentile corresponds with top N).
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.