Compare rows in a dataset

[AshokD]

Hi All,

I have sample data below :- 

Account_No   Limit     Score

========     =====    ====

11111              1000     14.34

11111               2000     11.29

22222             1000     18.23

22222              2000     21.01

33333              3000     12.22

33333              1000     12.22 

Need to compare the score column values when we have multiple entries for the same account number and keep:-

#1) the lowest score value row for the account number

#2) If the score value is same , then I need to pick up highest limit value row.

Output data:-

Account_No   Limit     Score

========     =====    ====

11111               2000     11.29

22222             1000     18.23

33333              3000     12.22

Can someone suggest me a logic to achieve this ?

Thanks,

Ashok 

[novinosrin]

proc sort data=your_dataset out=sorted_dsn;

by account_no score;

run;



data want_dsn;

 set sorted_dsn;

by account_no;

if first.account_no;

run;

[PGStats]

@novinosrin , add descending limit to the sort criteria to fulfill OP second requirement.

[novinosrin]

Sir @PGStats  Thank you as I failed to notice the 2nd one and went for lunch. Well, @Astounding  has answered it. 

[Astounding · Accepted solution]

I think a slight variation on the suggested program would work:

proc sort data=have;
   by account_no score descending limit;
run;

data want;
   set have;
   by account_no;
   if first.account_no;
run;

[AshokD]

Thanks. It makes sense, let me try it out.