Help using Base SAS procedures

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

Reply
N/A
Posts: 0

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

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.
Trusted Advisor
Posts: 2,116

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

Posted in reply to deleted_user
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.
PROC Star
Posts: 1,760

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

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;
Ask a Question
Discussion stats
  • 2 replies
  • 1881 views
  • 0 likes
  • 3 in conversation