topic Re: proc sql: Does something similar to "Partition By" exist? in SAS Procedures

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

deleted_user — Sat, 10 Oct 2009 13:31:13 GMT

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.

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

Doc_Duke — Mon, 12 Oct 2009 02:02:33 GMT

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.

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

ChrisNZ — Wed, 14 Oct 2009 03:07:48 GMT

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;