topic Re: Select top 3 records for each id in SAS Programming

Select top 3 records for each id

SASPhile — Sat, 27 Oct 2012 14:43:04 GMT

there are 10 ids and scores,with varying records for each id. how to get top 3 records for each id based on score?

there is a function "TOP" in other SQL's, what is the equivalent in SAS?

Re: Select top 3 records for each id

FriedEgg — Sat, 27 Oct 2012 16:23:10 GMT

data want;

do _n_=1 by 1 until(last.id);

set have;

by id;

if _n_ <= 3 then output;

end;

run;

Re: Select top 3 records for each id

art297 — Sat, 27 Oct 2012 16:45:35 GMT

Of course that assumes that the data is pre-sorted and that ties aren't relevant.

Re: Select top 3 records for each id

Haikuo — Sun, 28 Oct 2012 19:41:42 GMT

If still choose SQL (SAS SQL in this case) and include ties:

data have;

input id $ value;

cards;

S12 -20

S12 20

S12 10

S12 20

S12 -50

S13 -40

S13 60

S13 -100

S14 10

S14 20

S14 0

S14 -30

S14 -20

;

proc sql;

select id,value from

(select distinct b.id,b.value, a.value as avalue from have a

left join have b

on a.id=b.id)

group by id,value

having sum(avalue>=value)<=3;

quit;

Haikuo

Re: Select top 3 records for each id

FriedEgg — Sun, 28 Oct 2012 20:24:17 GMT

Yes, it relies upon the data being presorted, unlike sql, however the TOP clause itself is indiscriminate about and type of rank or ties, so that shouldn't be an issue to repeat functionality.

Re: Select top 3 records for each id

art297 — Sun, 28 Oct 2012 22:30:58 GMT

I don't think that works as intended.

You still haven't marked this question as being answered, thus everyone will still keep trying to come up with solutions. I really think you are best off doing this in either a datastep or using a proc like proc rank. However, since you asked how you might be able to do it using proc sql, here is a proc sql solution that takes ties into consideration (i.e., if there is a tie for the 3rd position, it will provide all records that have a value greater than of equal to the tied value).

Unfortunately, it has to be modified to account for the specific ids of interest. I used Haikuo's example dataset, but added one record for testing purposes:

data have;

input id $ value;

cards;

S12 -20

S12 20

S12 10

S12 20

S12 -50

S13 -40

S13 15

S13 60

S13 -100

S14 10

S14 20

S14 0

S14 -30

S14 -20

;

proc sql;

create table temp as

select *

from have

where id="S12"

order by value desc

;

create table S12 as

select id, value as bottom_value

from temp (firstobs=3 obs=3)

;

create table temp as

select *

from have

where id="S13"

order by value desc

;

create table S13 as

select id, value as bottom_value

from temp (firstobs=3 obs=3)

;

create table temp as

select *

from have

where id="S14"

order by value desc

;

create table S14 as

select id, value as bottom_value

from temp (firstobs=3 obs=3)

;

create table criteria as

select * from S12

union all select * from S13

union all select * from S14

;

create table want (drop=count) as

select a.*,count(*) as count

from have a

left join criteria b

on a.id=b.id

group by a.id

having a.value>=b.bottom_value

order by id, value desc

;

quit;

Re: Select top 3 records for each id

FriedEgg — Sun, 28 Oct 2012 23:17:57 GMT

data have;

input id $ value @@;

cards;

S12 -20 S12 20 S12 10

S12 10 S12 20 S12 -50

S13 -40 S13 60 S13 -100

S14 10 S14 20 S14 0

S14 -30 S14 -20

;

proc means data=have nway noprint;

class id;

var value;

output out=top3arr(drop=_:) idgroup ( max(value) out[3] (value)=) /autolabel autoname;

run;

proc transpose data=top3arr out=top3list(drop=_: rename=(col1=value));

by id;

var value:;

run;

proc sql;

drop table top3arr;

quit;

ID	VALUE
S12	20
S12	20
S12	10
S13	60
S13	-40
S13	-100
S14	20
S14	10
S14	0

Re: Select top 3 records for each id

PGStats — Mon, 29 Oct 2012 01:39:57 GMT

With proc rank you get some control over the way ties are treated :

data have;
input id $ score @@;
datalines;
S12 -20 S12 20 S12 10
S12 10 S12 20 S12 -50
S13 -40 S13 60 S13 -100
S14 10 S14 20 S14 0
S14 -30 S14 -20
;

proc rank data=have descending ties=low /*ties=dense*/
out=want(where=(order<=3));
by id notsorted;
var score; ranks order;
run;

Re: Select top 3 records for each id

Haikuo — Mon, 29 Oct 2012 02:02:43 GMT

Agree with Art, SQL is not really optimal for this kind of task. Comparing to PG's clean-cut proc rank, Data step will have same amount of control, if not more:

data have;

input id $ score @@;

datalines;

S12 -20 S12 20 S12 10

S12 10 S12 20 S12 -50

S13 -40 S13 60 S13 -100

S14 10 S14 20 S14 0

S14 -30 S14 -20

;

proc sort data=have;

by id descending value;

run;

data want;

do _n_=1 by 1 until (last.id);

set have nobs=nobs;

by id descending value;

if first.id then _f=nobs;

_n+ifn(value=lag(value),0,1);

_n=ifn(first.id,1,_n);

_f=ifn(_n_=3,_n,_f) ;

if _n<=_f then output;

end;

drop _:;

run;

proc print;run;

Re: Select top 3 records for each id

alandool — Thu, 31 Aug 2017 19:05:13 GMT

Editor's Note: Thanks to all who contributed, showing that there are mulitple ways to get the same results in SAS.

Here's my take on this. A mix of proc sql and data step.

Alan

data have;
input id $ score @@;
datalines;
S12 -20 S12 20 S12 10
S12 10 S12 20 S12 -50
S13 -40 S13 60 S13 -100
S14 10 S14 20 S14 0
S14 -30 S14 -20
;

/* change as needed to bring back desired ranking */
%let start= 1;
%let end = 3;

/* create table containing distinct id and value -- needed b/c more than one id can have the same score */
proc sql;
create table dst_id_score as
select distinct id, score from have
order by id, score desc; /* keep this sort order for ranking */
* NOTE: Table WORK.DST_ID_SCORE created, with 12 rows and 2 columns. ;

/* assign ranking by id by score */
data temp;
set dst_id_score ;
by id;
if first.id then rnk=0;
rnk + 1;
run;
* NOTE: The data set WORK.TEMP has 12 observations and 3 variables. ;

/* go back to original file and bring back all data meeting desired ranking */
proc sql;
create table final_top_ranked as

select
a.id
, a.score
, b.rnk as rank
from have a inner join temp b on a.id = b.id and a.score = b.score
where rnk between &start and &end
order by id, rnk;
* NOTE: Table WORK.FINAL_TOP_RANKED created, with 11 rows and 3 columns. ;

Re: Select top 3 records for each id

neekum — Sat, 09 Apr 2016 03:56:32 GMT

Great solution. It is transparent, and that is critical for debugging and portability.

Re: Select top 3 records for each id

neekum — Sat, 09 Apr 2016 03:58:18 GMT

I meant, great solution by alandool. It totally matches my coding style, where I spell out each and every item explicitly for QA and later use. Kudos!