Hi,
This is kind of long but basically it is repeated code. Someone might have more compact code.
data abc;
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
quit;
/* this brings first max row*/
proc sql;
create table firstmaxrowtable as
select location, bank,
max(case when bank ='PNB' or bank = 'SBI' then amount else 0 end ) as amount from abc
group by 1, 2;
quit;
/*transpose first max row data*/
proc sql;
create table firstmaxrowtransposetab as
select location,
max(case when bank ='PNB' then amount else 0 end ) as PNB,
max(case when bank ='SBI' then amount else 0 end ) as SBI from firstmaxrowtable
group by 1;
quit;
/*Second max row*/
proc sql;
create table secondmaxrowtable as
select a.location, a.bank,
max(case when a.bank ='PNB' or a.bank = 'SBI' then a.amount else 0 end ) as amount
from abc a
inner join
firstmaxrowtable b
on a.bank = b.bank
and a.location= b.location
and a.amount<>b.amount
group by a.location, a.bank;
quit;
/*transpose second max row data*/
proc sql;
create table secondmaxrowtransposetab as
select location,
max(case when bank ='PNB' then amount else 0 end ) as PNB,
max(case when bank ='SBI' then amount else 0 end ) as SBI from secondmaxrowtable
group by 1;
quit;
/*appending first and second max row tables and percentage calculation */
proc sql;
create table finaltable as
select a.*,
PNB/(PNB+SBI) as PercentPNB label='%PNB' format =percent10.,
SBI/(PNB+SBI) as PercentSBI label='%SBI' format = percent10.
from firstmaxrowtransposetab a
union all
select b.*,
PNB/(PNB+SBI) as PercentPNB label='%PNB' format =percent10.,
SBI/(PNB+SBI) as PercentSBI label='%SBI' format = percent10.
from secondmaxrowtransposetab b
order by location, PNB, SBI;
quit;
proc sql;
select * from finaltable;
quit;
... View more