SAS Proc Freq - frequency of each category for multiple variables

Question

How can I produce a table that has this kind of info for multiple variables:

VARIABLE    COUNT   PERCENT
U           51  94.4444
Y            3  5.5556

This is what SAS spits out into the listing output for all variables when I run this program:

ods output nlevels=nlevels1 OneWayFreqs=freq1 ;

proc freq data=sample nlevels ;

   tables _character_ / out=outfreq1;

run;

In the outfreq1 table there is the info for just the last variable in the data set (table shown above) but not for all for the variables. In the nlevels1 table there is info of how many categories each variable has but no frequency data.

What I want though is to output the frequency info for all the variables. Does anybody know a way to do this without a macro/loop?

Joe Joe · Accepted Answer · 2015-10-14T20:37:25

You basically have two options, which are sort-of-similar in the kinds of problems you'll have with them: use PROC TABULATE, which more naturally deals with multiple table output, or use the onewayfreqs output that you already call for.

The problem with doing that is that variables may be of different types, so it doesn't have one column with all of that information - it has a pair of columns for each variable, which obviously gets a bit ... messy. Even if your variables are all the same type, SAS can't assume that as a general rule, so it won't produce a nice neat thing for you.

What you can do, though, particularly if you are able to use the formatted values (either due to wanting to, or due to them being identical!), is coalesce them into one result.

For example, given your freq1 dataset from the above:

data freq1_out;
  set freq1;
  value = coalesce(of f_:);
  keep table value frequency percent;
run;

That combines the F_ variables into one variable (as always only one is ever populated). If you can't use the F_ variables and need the original ones, you will have to make your own variable list using a macro variable list (or some other method, or just type the names all out) to use coalesce.

Finally, you could probably use PROC SQL to produce a fairly similar table, although I probably wouldn't do it without using the macro language. UNION ALL is a handy tool here; basically you have separate subqueries for each variable with a group by that variable, so

proc sql;
  create table my_freqs as
    select 'HEIGHT' as var, height, count(1) as count
    from sashelp.class
    group by 1,height
  union all
    select 'WEIGHT' as var, weight, count(1) as count
    from sashelp.class
    group by 1,weight
  union all
    select 'AGE' as var, age, count(1) as count
    from sashelp.class
    group by 1,age
  ;
quit;

That of course can be trivially macrotized to something like

proc sql;
  create table my_freqs as
    %freq(table=sashelp.class,var=height)
  union all
    %freq(table=sashelp.class,var=weight)
  union all
    %freq(table=sashelp.class,var=age)
   ;
quit;

or even further either with a list processing or a macro loop.

SAS Proc Freq - frequency of each category for multiple variables

1 Answers