Calculating percent from counts in PROC SQL

[vegan_renegade]

I'm running the following to get the number of males and females

/*Number of males and females*/
proc sql;
create table number_birth_sex as
select birth_gender 'Birth Gender', count(birth_gender) as Count
from tb_yearly_report1
group by birth_gender;
quit;

and I get this 

birth_gender	Count
Female	11
Male	27

What I want to do is add another variable called "Percent" that calculates percent of Females (11/38) and Males (27/38) from the total. So ideally like this:

birth_gender	Count	Percent
Female	11	28.9%
Male	27	71.1%

I can do it with a data step but figured there's a way I can do it within this same section of code.

[PaigeMiller]

proc sql;
    create table number_birth_sex as
    select birth_gender label='Birth Gender', count(birth_gender) as Count,
        calculated count / sum(calculated count) as percent format=percent8.2
    from tb_yearly_report1
    group by birth_gender;
quit;

or probably better and easier, use PROC FREQ

proc freq data=tb_yearly_report1;
    tables birth_gender;
run;

[Reeza]

When I run the SQL here I get an error that this type of nested calculations are not supported. I didn't think this was possible without a second pass or explicitly specifying the unique values to be counted.

[Reeza · Accepted solution]

You need two passes of the data with SQL. One to first calculate the total and a second to calculate the percentage. 

PROC FREQ is more efficient.

proc freq data=tb_yearly_report1;
table birth_gender / out=number_birth_sex outpct;
run;

A SQL solution would look something like this: 

proc sql;
    create table number_birth_sex as
    select sex 'Birth Gender', count(sex) as Count,
        calculated count / total as percent format=percent8.2
    from (select *, count(*) as total from sashelp.class)
    group by sex;
quit;

---

@vegan_renegade wrote:

I'm running the following to get the number of males and females

/*Number of males and females*/
proc sql;
create table number_birth_sex as
select birth_gender 'Birth Gender', count(birth_gender) as Count
from tb_yearly_report1
group by birth_gender;
quit;

and I get this 

birth_gender	Count
Female	11
Male	27

What I want to do is add another variable called "Percent" that calculates percent of Females (11/38) and Males (27/38) from the total. So ideally like this:

birth_gender	Count	Percent
Female	11	28.9%
Male	27	71.1%

I can do it with a data step but figured there's a way I can do it within this same section of code.

---