Selecting Largest Value By Multiple Groups

[paulrockliffe]

Hello,

I have a table containing various Entities, each entity can have up to 5 reference numbers and each reference number can have an unlimited number of amounts associated with it.  So 3 columns, Entity, Reference, Amount

I want to extract a table that contains one result for each Entity and Reference combination and the largest Amount. 

I've done this before using the Sort task, but I can't get it to work where I'm grouping by more than one column in the Sort.

Does anyone have any top tips?

Thanks!

[RW9]

Post example test data in the form of a datastep, and what the output from that should be.  I cant guess from that why:

proc sort data=have out=want nodupkey;
  by entity reference descending amount;
run;

Would not work.

[Astounding]

Here's a way:

proc summary data=have nway;

class entity refno;

var amount;

output out=want (keep=entity refno amount) max=;

run;

There are ways to do this where you run PROC SORT first, but you then need a DATA step to select the desired observations.