Below are two possibilities. One uses more SQL, since that's what you asked for. The other uses transpose and a data step. I did not identify the top two in SQL, which might be possible using 'monotonic' after getting the right sort order, but doing this entirely in SQL feels forced and unnecessary. I also did not replicate your final sort order, but you could do that in SQL with a boolean condition. I'll definitely be curious to see other solutions. data have;
input Location $ Name $ Bank $ Amount;
datalines;
DEL A0BC PNB 5000
DEL A1BC SBI 6000
DEL A2BC PNB 4000
DEL A3BC SBI 2000
DEL A4BC PNB 6000
DEL A5BC SBI 7000
MUM A4BC SBI 5000
MUM A5BC SBI 7000
MUM A6BC PNB 8000
MUM A7BC PNB 9000
MUM A8BC SBI 8000
MUM A9BC PNB 5000
BAN A8BC SBI 4000
BAN A9BC PNB 3000
BAN A10BC PNB 7000
BAN A11BC SBI 6000
BAN A12BC PNB 5000
BAN A13BC SBI 8000
;
proc sort data = have;
by location bank descending amount ;
run;
data top2;
set have;
by location bank descending amount;
if first.bank then rank = 1;
else rank + 1;
if rank < 3 then output;
run;
proc sql;
CREATE TABLE want AS
SELECT location, rank,
sum(amount * (bank = 'PNB')) AS pnb,
sum(amount * (bank = 'SBI')) AS sbi,
CALCULATED pnb / sum(amount) AS pct_pnb format percent8.,
CALCULATED sbi / sum(amount) AS pct_sbi format percent8.
FROM top2
GROUP BY location, rank;
quit;
proc sort data = top2;
by location rank;
run;
proc transpose data = top2 out = top2trans;
by location rank;
id bank;
var amount;
run;
data want_alt;
set top2trans;
pct_pnb = pnb / sum(pnb, sbi);
pct_sbi = sbi / sum(pnb, sbi);
format pct_pnb pct_sbi percent8.;
run;
... View more
