proc sql: Does something similar to "Partition By" exist?

[deleted_user]

Hi all,
I have a question regarding proc sql sintax.
I need to find top 5 rows in a query which contains group by.
In particolar I need top 5 for each aggegation groups and not 5 rows at all.
In Oracle sintax I think I could use "Partition By" over a given rank in order to apply this wish.
Could you please suggest me a workaround to use in proc sql or a specific function if existing?

I really thank you a lot in advance for your support!

KR

Daniele.

[Doc_Duke]

I don't know a one-step approach, but I could do it in three.

First SORT by group, then use PROC RANK with a BY on the group to get the ranks, and then either a DATA step or PROC SQL to pull them off.

[ChrisNZ]

If you only want the top 5, the outobs= option is your friend.

If you have group by, you probably need 2 steps as in:

[pre]

proc sql; * find best selling products by region;
create view SUM as
select REGION, PRODUCT, sum(SALES) as SALES
from SASHELP.SHOES
group by REGION, PRODUCT
order by REGION, SALES desc ;
quit;


data TOP3; * keep the top 3 for each region;
set SUM;
by REGION ;
if first.REGION or lag(first.REGION) or lag2(first.REGION);
run;


data TOP3; * keep the top 3 for each region, alternative method;
set SUM;
by region ;
if first.REGION then N=0;
N+1;
if N <= 3;
drop N;
run;