topic How to include zero frequencies (proc sql, proc freq) in SAS Programming

How to include zero frequencies (proc sql, proc freq)

xxformat_com — Sat, 03 Dec 2022 19:03:56 GMT

The following proc sql will allow to have a zero frequency for females aged 16.

proc sql;
    create table cnt as   
        select distinct sex, age, count(*) as cnt
        from sashelp.class
        group by sex, age;
    
    create table ref as 
        select distinct a.sex, b.age, 0 as cnt
        from sashelp.class a, sashelp.class b;
    
    create table sex_age_cnt as
        select a.sex, a.age, coalesce(b.cnt,a.cnt) as cnt
        from ref a
        left join 
             cnt b
        on a.sex=b.sex and
           a.age=b.age;
quit;

I can also get the result using proc means.

proc means data=sashelp.class completetypes;
    class sex age;
run;

Is there any smarter way to get the same result using proc sql/proc freq?

Re: How to include zero frequencies (proc sql, proc freq)

FreelanceReinh — Sun, 04 Dec 2022 00:00:30 GMT

PROC FREQ with the SPARSE option

proc freq data=sashelp.class;
tables sex*age / sparse list out=want;
run;

is shorter than PROC SQL:

proc sql;
create table want2 as
select sex, age, count(a.sex) as cnt from 
(select distinct b.sex, c.age from sashelp.class b, sashelp.class c)
natural left join
sashelp.class a
group by 1,2;
quit;

Re: How to include zero frequencies (proc sql, proc freq)

xxformat_com — Sun, 04 Dec 2022 09:24:46 GMT

Thanks. I forgot about the *.

For those reading this post, notice that with ods output, the sparse option is not needed.