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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.