Fluorite | Level 6

## Calculate proportions and sort by percentages

``````data industrycon_;
set sa.bank_201907	(keep =	mainbank
mainbankid
L1STATE
naics_sect_cd
naics_sector
balgrs
)
;
if balgrs=0 then delete;
run;

proc sql;
create table indcon_
as
select
distinct mainbank,
mainbankid as mainbankid,
L1STATE as bankstate,
sum(balgrs) as balgrs1_
from industrycon_
group by mainbank, mainbankid;
quit;

proc sql;
create table indconman
as
select
distinct mainbank,
mainbankid as mainbankid,
L1STATE as bankstate,
sum(balgrs) as balgrs2_
from industrycon_
where naics_sector="Manufacturing"
group by mainbank, mainbankid;
quit;

proc sql outobs=20;
create table testing as
select a.mainbank,a.bankstate,a.balgrs1_,
b.balgrs2_/a.balgrs1_ as manu_pct format=percent7.1
from indcon_ as a
left join indconman as b
on a.mainbank=b.mainbank;
quit;``````

Hi - I am trying to create a table of the 20 'mainbank's that have the highest 'balgrs' in 'Manufacturing'. I think I am close. But, there are a few things wrong with my output:

1. I am having multiple entries of the same mainbank. I want it one row per mainbank.

2. The manu_pct output has crazy high percentages, which is impossible because the 'balgrs' of 'Manufacturing' can't be higher than the 'balgrs' of the entire 'mainbank'.

3. How can I tell the output to only give me the 20 banks with the highest proportions of 'balgrs' by 'mainbank'?

Super User

## Re: Calculate proportions and sort by percentages

Please provide some example input data and what the result for that input data should look like.

You might also define exactly what the denominator and numerator should be when creating the proportions or percentages.

And do you actually need a dataset, for further manipulation, or a report for people to read?

Consider this as one example of a report that sums a variable by region  and determines the percentage of that sum as the percent of the whole.

You should have the sashelp.shoes data set available.

```proc tabulate data=sashelp.shoes;
class region ;
var sales;
table region all='All Regions',
sales* (sum pctsum);
run;```

This procedure actually will create output data sets as well that can be processed though some folks don't like them.

Discussion stats