topic Re: Top N rows per by-group in SAS Programming

Top N rows per by-group

jdmarino — Wed, 12 Feb 2014 22:10:22 GMT

I don't know how to pull out the top N rows per by-group. I'm a long-time SQL querier, and this kind of thing, where N is specified, is not something SQL is good at. For example, I'd like to pull out the rows of the top 2 returns per sector. This needs to work on a large dataset (10M+ rows) where I need the top 100 values in a handful of categories. All good ideas are welcome.

data Stocks;

input Ticker :$3. Sector :$4. Return;

datalines;

ABC Tech 0.10

DEF Tech 0.30

GHI Tech 0.01

JKL Tech 0.12

YZA Food 0.01

VWX Food 0.09

STU Food 0.08

PQR Food 0.05

MNO Food 0.02

BCD Booz 0.10

EFG Booz 0.11

GMC Cars 0.01

;

desired output: a dataset with the rows which are the top 2 returns in each sector even if there are fewer than N rows in the sector

DEF Tech 0.30

JKL Tech 0.12

VWX Food 0.09

STU Food 0.08

BCD Booz 0.10

EFG Booz 0.11

GMC Cars 0.01

Re: Top N rows per by-group

data_null__ — Wed, 12 Feb 2014 23:00:23 GMT

What is the rule for ties?

Is your 10+ million rows sorted or indexed. Maybe on Sector?

Re: Top N rows per by-group

Jagadishkatam — Thu, 13 Feb 2014 06:08:31 GMT

Please try the below code by proc sql

proc sql;

create table want as select distinct a.ticker, a.sector, a.return from stocks as a left join stocks as b on (a.sector=b.sector and a.return le b.return)

group by a.sector,a.return having count(*) <=2 order by a.sector, a.return desc;

quit;

Thanks,

Jag

Re: Top N rows per by-group

jdmarino — Thu, 13 Feb 2014 12:34:59 GMT

The rule for ties: do what is easiest/most obvious. If that means including all rows that are tied so that the total count per group > N, that's OK.

The data can be sorted or indexed.

FYI, I'm pretty sure I can do this in a data-step, but that seems a little "grow you own". I'm guessing there is a proc or feature I've never heard of that will do it for me. Hence this post.

Re: Top N rows per by-group

jdmarino — Thu, 13 Feb 2014 12:55:33 GMT

You know, I have used this technique before, and it does work on my sample data. I'll try it on my big dataset, but the intermediate data may become too large for my machine.

Update: after an hour, it's still running! No wonder I put that technique out of my brain.

Message was edited by: John Marino

Re: Top N rows per by-group

data_null__ — Thu, 13 Feb 2014 13:19:56 GMT

PROC UNIVARIATE has two options one or both should meet your needs.

NEXTROBS=

NEXTRVAL=

NEXT=Number of extream

You will ID statement too.

Message was edited by: data _null_

Re: Top N rows per by-group

jdmarino — Thu, 13 Feb 2014 13:59:40 GMT

I can't find those options mentioned for proc univariate. I have SAS 9.3; could those be new options?

Re: Top N rows per by-group

data_null__ — Thu, 13 Feb 2014 14:09:36 GMT

They are PROC statement options.

Re: Top N rows per by-group

Jagadishkatam — Thu, 13 Feb 2014 14:34:42 GMT

Since from your post i see you are interested in proc steps, i used PROC RANK and it is generating the desired output.

proc sort data= Stocks;

by sector;

run;

proc rank data=Stocks out=r_test descending;

by Sector;

var Return;

ranks r_Return;

run;

proc sort data=r_test;

by sector r_Return ;

where r_Return <=2;

run;

Please try the code. Hope you like it

Thanks,

Jag

Re: Top N rows per by-group

jdmarino — Thu, 13 Feb 2014 14:46:20 GMT

http://support.sas.com/cdlsearch?charset=iso-8859-1&nh=25&ct=80000&qt=nextobs++spr%3A%229.3qq_%22&sasreleasepart=9.3qq_&s1=3&searchterm=nextobs&sasprodpart=&searchContains=

I searched for "nextobs" and found nothing. But that's ok: Jag's solution seems right.

Re: Top N rows per by-group

jdmarino — Thu, 13 Feb 2014 15:13:31 GMT

This is the right way to do it. Took 18 seconds on 500k rows. I don't know why this did not occur to me; I use proc rank pretty often. Viva la Community!

Re: Top N rows per by-group

data_null__ — Thu, 13 Feb 2014 15:33:46 GMT

typo it is NEXTROBS. Guess you didn't think to search for PROC UNIVARIATE.

Re: Top N rows per by-group

data_null__ — Thu, 13 Feb 2014 15:43:26 GMT

Can you move that WHERE statement to the PROC RANK step as a data set option on the OUT= dataset?

Re: Top N rows per by-group

Jagadishkatam — Fri, 14 Feb 2014 00:55:56 GMT

thank you data_null_, we can also use where option in the same proc rank as below

proc sort data= Stocks;

by sector;

run;

proc rank data=Stocks out=r_test(where=(r_Return <=2)) descending;

by Sector;

var Return;

ranks r_Return;

run;

proc sort data=r_test;

by sector r_Return ;

run;

Thanks,

Jag