topic Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank). in SAS Programming

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

SASPhile — Mon, 27 Feb 2012 16:34:26 GMT

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

contract Rankgroup CC Rank

1001 Neoplasm 1 CC-10 4

1001 Neoplasm 1 CC-9 2

1001 Neoplasm 1 CC-7 3

1001 Neoplasm 2 CC-10 1

1001 Neoplasm 2 CC-7 3

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

Hima — Mon, 27 Feb 2012 16:42:25 GMT

data test;
input contract Rankgroup $5-15 CC $ Rank ;
datalines;
1001 Neoplasm 1 CC-10 4
1001 Neoplasm 1 CC-9 2
1001 Neoplasm 1 CC-7 3
1001 Neoplasm 2 CC-10 1
1001 Neoplasm 2 CC-10 1
1001 Neoplasm 2 CC-7 3

;
RUN;
PROC PRINT; RUN;

PROC SQL;
SELECT * FROM test WHERE RANK IN (SELECT MIN(RANK) FROM tesT);
QUIT;

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

art297 — Mon, 27 Feb 2012 16:43:59 GMT

Or, with just one pass through the data:

proc sql;

create table want as

select *

from have

group by rankgroup

having rank=min(rank)

;

quit;

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

KarlK — Mon, 27 Feb 2012 16:54:19 GMT

Not arguing, Art, just taking a learning opportunity. I get the "query requires remerging" message from your query, so Proc Sql technically is reading the data twice, no?

Also, SasPhile, Art's elegant query reproduces the duplicate record you have in your original dataset. If you don't want that, you can add "distinct" as in "select distinct *. . .".

Karl

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

art297 — Mon, 27 Feb 2012 17:15:17 GMT

Karl, I agree! Conversely, Hima's code has an additional problem, in that a group by clause would have to be added in order for the OP to obtain the desired result.

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

Astounding — Mon, 27 Feb 2012 17:26:05 GMT

SASPhile,

Other posters have correctly pointed out ... you have to decide what should happen when the same Rank appears multiple times within the same grouping.

I'm just asking about something else here ... Did you really mean to select the highest rank for each Rankgroup? Or should it be the combination of contract and Rankgroup together that determines the grouping?

Good luck.

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

Keith — Tue, 28 Feb 2012 12:12:40 GMT

If you only want one of the highest rank records returned, then proc summary does this very easily.

proc summary data=test nway;

output out=top_rank (drop=_:) minid(rank(_all_))=;

run;

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

SASPhile — Tue, 28 Feb 2012 20:44:26 GMT

Astounding,

select the highest rank for each Rankgroup.

Select only the highest-ranked in each ranking group using the Rank column (1 is the highest possible rank).

Astounding — Tue, 28 Feb 2012 21:23:17 GMT

SasPhile,

In that case, you have solutions posted by others that should work.

If you want to select just one record per Rankgroup (even if many are tied for the highest Rank), try Keith's below. If you want to select all records from a Rankgroup that share the highest Rank, try one of the SQL solutions above.

If you posted a solution that I misinterpreted, please say so. But I don't think DISTINCT(*) would limit the selection to one observation per Rankgroup if the variable CC might be different.

Good luck.