creating output from proc freq in SAS

Question

I am running the following SAS code in SAS Enterprise Guide 6.1 to get some summary stats on null/not null for all the variables in a table. This is producing the desired info via the 'results' tab, which creates a separate table for each result showing null/not null frequencies and percentages.

What I'd like to do is put the results into an output dataset with all the variables and stats in a single table.

proc format;
    value $missfmt ' '='Missing' other='Not Missing';
    value missfmt   . ='Missing' other='Not Missing';
run;
proc freq data=mydatatable;
    format _CHAR_ $missfmt.;
    tables _CHAR_ / out=work.out1 missing missprint nocum;
    format _NUMERIC_ missfmt.;
    tables _NUMERIC_ / out=work.out2 missing missprint nocum;
run;

out1 and out2 are being generated into tables like this:

FieldName | Count | Percent
Not Missing | Not Missing | Not Missing

But are only populated with one variable each, and the frequency counts are not being shown.

The table I'm trying to create as output would be:

field | Missing | Not Missing | % Missing
FieldName1 | 100 | 100 | 50
FieldName2 | 3 | 97 | 3

This may be helpful, if you add the formats to the proc freq + table _all_;, it will generate the desired table. gist.github.com/statgeek/e0903d269d4a71316a4e — Reeza

Joe Joe · Accepted Answer · 2017-03-08T14:57:54

The tables statement output options only apply to the last table requested. _CHAR_ resolves to (all character variables), but they're single tables, so you only get the last one requested.

You can get this one of two ways. Either use PROC TABULATE, which more readily deals with lists of variables; or use ODS OUTPUT to grab the proc freq output. Both output styles will take some work likely to get into exactly the structure you want.

ods output onewayfreqs=myfreqs;  *use `ODS TRACE` to find this name if you do not know it;
proc freq data=sashelp.class;
  tables _character_;
  tables _numeric_;
run;
ods output close;

creating output from proc freq in SAS

1 Answers