04-24-2018 10:25 AM
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?
04-24-2018 10:30 AM
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.
04-24-2018 10:49 AM - edited 04-24-2018 01:07 PM
Here's a way:
proc summary data=have nway;
class entity refno;
output out=want (keep=entity refno amount) max=;
There are ways to do this where you run PROC SORT first, but you then need a DATA step to select the desired observations.