Desktop productivity for business analysts and programmers

Selecting Largest Value By Multiple Groups

Reply
Contributor
Posts: 56

Selecting Largest Value By Multiple Groups

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!

Super User
Super User
Posts: 9,227

Re: Selecting Largest Value By Multiple Groups

Posted in reply to paulrockliffe

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.

Super User
Posts: 6,544

Re: Selecting Largest Value By Multiple Groups

[ Edited ]
Posted in reply to paulrockliffe

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.

Ask a Question
Discussion stats
  • 2 replies
  • 126 views
  • 2 likes
  • 3 in conversation