SAS sql . How to use ranks sas sql?

Question

I have a table of people and their expenses from different sources.

name    expenseid   amount
mike    1           100
mike    2           200
nick    3           100
mike    4           500
peter   5           300
nick    6           150
…       …           …

For each person I need to get the TOP 10 most expensive transactions . Here is what I tried .

proc sql;
select name, expenseid, amount 
from table2
qualify row_number over(partition by expenseid order by amount desc) < 11 
group by name;
quit;

But the row_number is not recognized by Sas. How can I improve it ?

Answer 1

It's not supported by PROC SQL. There is a function to do it in SQL, but I don't think it is officially supported (ie someone in R&D threw it in). Because of that, I would use a more traditional SAS approach.

PROC SORT data=table2;
by name descending amount;
run;

data table2(drop=count);
set table2;
by name;
retain count;
if first.name then
   count = 0;
count = count + 1;
if count < 11;
run;