DATA Step, Macro, Functions and more

Selecting 3rd highest value

Reply
New Contributor
Posts: 2

Selecting 3rd highest value

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

 

 

Super Contributor
Posts: 472

Re: Selecting 3rd highest value

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;
Thanks,
Suryakiran
New Contributor
Posts: 2

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

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

 

 

PROC Star
Posts: 500

Re: Selecting 3rd highest value

[ Edited ]

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

;



 

 

Super Contributor
Posts: 284

Re: Selecting 3rd highest value

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;
	
Super User
Posts: 22,857

Re: Selecting 3rd highest value

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

 

 


 

Ask a Question
Discussion stats
  • 5 replies
  • 157 views
  • 1 like
  • 5 in conversation