sas va calculate rate between first N revenue and total revenue

[rufmau68]

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

[MikeJenista · Accepted solution]

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