topic Re: Calculating percent from counts in PROC SQL in SAS Programming

Calculating percent from counts in PROC SQL

vegan_renegade — Thu, 05 Jan 2023 18:50:59 GMT

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.

Re: Calculating percent from counts in PROC SQL

PaigeMiller — Thu, 05 Jan 2023 19:59:05 GMT

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;

Re: Calculating percent from counts in PROC SQL

Reeza — Thu, 05 Jan 2023 20:02:54 GMT

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.

Re: Calculating percent from counts in PROC SQL

Reeza — Thu, 05 Jan 2023 20:32:59 GMT

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.