topic Re: Compare rows in a dataset in New SAS User

Compare rows in a dataset

AshokD — Wed, 05 Feb 2020 18:24:20 GMT

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

Re: Compare rows in a dataset

novinosrin — Wed, 05 Feb 2020 18:27:32 GMT

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;

Re: Compare rows in a dataset

PGStats — Wed, 05 Feb 2020 18:33:54 GMT

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

Re: Compare rows in a dataset

Astounding — Wed, 05 Feb 2020 18:35:20 GMT

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;

Re: Compare rows in a dataset

novinosrin — Wed, 05 Feb 2020 18:40:03 GMT

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

Re: Compare rows in a dataset

AshokD — Wed, 05 Feb 2020 18:46:06 GMT

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