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
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 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;
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
;
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.