topic Selecting Largest Value By Multiple Groups in SAS Enterprise Guide

Selecting Largest Value By Multiple Groups

paulrockliffe — Tue, 24 Apr 2018 14:25:11 GMT

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!

Re: Selecting Largest Value By Multiple Groups

RW9 — Tue, 24 Apr 2018 14:30:30 GMT

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.

Re: Selecting Largest Value By Multiple Groups

Astounding — Tue, 24 Apr 2018 17:07:16 GMT

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.