topic Re: Selecting 3rd highest value in SAS Programming

Selecting 3rd highest value

rahulk21 — Sun, 22 Apr 2018 13:29:28 GMT

Account Date Amount

A 01/03 100

A 01/03 200

A 01/03 300

A 01/03 400

A 01/03 50

A 01/05 200

A 01/05 400

A 01/05 500

A 01/05 600

B 01/06 700

B 01/06 800

B 01/06 100

B 01/06 60

OUTPUT:

A 01/03 200

A 01/05 400

B 01/06 100

Basically, want 3rd highest value for each unique combination of account and date

Want 3rd highest value for each unique combination of account and date

rahulk21 — Sun, 22 Apr 2018 13:25:25 GMT

Account Date Amount

A 01/03 100

A 01/03 200

A 01/03 300

A 01/03 400

A 01/03 50

A 01/05 200

A 01/05 400

A 01/05 500

A 01/05 600

B 01/06 700

B 01/06 800

B 01/06 100

B 01/06 60

OUTPUT:

A 01/03 200

A 01/05 400

B 01/06 100

Basically, want 3rd highest value for each unique combination of account and date

Re: Selecting 3rd highest value

SuryaKiran — Sun, 22 Apr 2018 14:26:37 GMT

PROC SORT DATA=Have;
by Account Date Amount;
run;
DATA WANT;
SET Have;
by  Account Date;
IF First.Date then count=1;
else count+1;
IF COUNT=3;
run;

Re: Selecting 3rd highest value

kiranv_ — Sun, 22 Apr 2018 15:10:13 GMT

2 more ways to do. As @novinosrin points out your one of 3rd highest value does not match with values you show as 3rd highest

First is proc rank and proc sql;


proc rank data=have out=want ties=dense ;	
   by account date;
	
   var amount;
   ranks amount_rank;
run;
    
 data want_final(drop = amount_rank);
 set want(where =(amount_rank = 3));
 run;

by proc sql





proc sql;
create table want(drop = rank) as 
select a.*,
 (select count(distinct amount) from have b
 where a.account = b.account
 and a.date=b.date
 and a.amount>=b.amount) as rank
 from have a
 where calculated rank = 3
 ;

/* dataset creation*/

data have;
input Account $ Date:mmddyy10. Amount;
format Date:mmddyy10.;
datalines;
A 01/03/2018 100

A 01/03/2018 200

A 01/03/2018 300

A 01/03/2018 400

A 01/03/2018 50

A 01/05/2018 200

A 01/05/2018 400

A 01/05/2018 500

A 01/05/2018 600

B 01/06/2018 700

B 01/06/2018 800

B 01/06/2018 100

B 01/06/2018 60

;

Re: Selecting 3rd highest value

SAS_inquisitive — Sun, 22 Apr 2018 16:18:41 GMT

proc sort data = have out = have_;
	by account date descending amount;
run;

data want;
	set have_;
	by account date descending amount;
	if first.date then seq = 0;
	seq + 1;
	if seq = 3 then output;
	drop seq;
run;

Re: Selecting 3rd highest value

Reeza — Sun, 22 Apr 2018 16:47:34 GMT

Can you have ties and if you can, how do you want to deal with them?

@rahulk21 wrote:

Account Date Amount

A 01/03 100

A 01/03 200

A 01/03 300

A 01/03 400

A 01/03 50

A 01/05 200

A 01/05 400

A 01/05 500

A 01/05 600

B 01/06 700

B 01/06 800

B 01/06 100

B 01/06 60

OUTPUT:

A 01/03 200

A 01/05 400

B 01/06 100

Basically, want 3rd highest value for each unique combination of account and date