Selecting 3rd highest value

[rahulk21]

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

[rahulk21]

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

[SuryaKiran]

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;

[kiranv_]

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

;



 

[SAS_inquisitive]

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;
	

[Reeza]

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

---