How to save multiple output of proc freq in one dataset

[mirmousavi]

Hi everyone,

I try to count number of missing for each variable of my dataset with following codes.

proc format;

value $missfmt '-9'='Missing' '-8'='Missing' '-6'='Missing' '-7'='Missing' ''='Missing' ' '='Missing' other='Not Missing';

value missfmt   . ='Missing'   -9='Missing'   -8='Missing'   -6='Missing' -7='Missing'   other='Not Missing';

run;

 

data pii;

set pii.pii_trad_nochc;

format _NUMERIC_ missfmt. _CHAR_ $missfmt.;

run;

 

proc freq data=pii;

format _NUMERIC_ missfmt. _CHAR_ $missfmt.; /* apply format for the duration of this PROC */

table _all_ / nocum;

run;

I have 600 variables and the proc freq gives me the number of missing for all of them.  The question is how to save the output in one dataset?

Please advise,

Bibi

[Shmuel]

Add OUTPUT close to proc freq. See documentation.

[Rick_SAS]

Can you say more about what you need for output?

Is your goal to compute the count of missing values for each variable? If so, see the article "Count the number of missing values for each variable."

If you want one-tables for all variables (missing and nonmissing), you can use

ODS OUTPUT OneWayFreqs=FreqOut;

to get the output data set, like this:

proc format;
 value $missfmt ' '='Missing' other='Not Missing';
 value  missfmt  . ='Missing' other='Not Missing';
run;
 
proc freq data=sashelp.heart; 
format _CHAR_ $missfmt.; /* apply format for the duration of this PROC */
tables _CHAR_ / missing missprint nocum nopercent;
format _NUMERIC_ missfmt.;
tables _NUMERIC_ / missing missprint nocum nopercent;
ods output OneWayFreqs=FreqOut;
run;

proc print; run;

[ballardw]

BTW, you may find it simpler to use comma separated lists of values  to make such formats:

proc format;
value $missfmt 
'-9', '-8', '-6', '-7', '', ' '='Missing' 
other='Not Missing';

value missfmt 
. , -9, -8, -6, -7 ='Missing' 
other='Not Missing'
;

run;

[Reeza · Accepted solution]

*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=sashelp.class;
	table sex age;
run;

*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent cum:;
label variable='Variable' 
	variable_value='Variable Value';
run;

*Display;
proc print data=want(obs=20) label;
run;

This is a more complicated version here, that gives a nicer/clean report if needed. 

https://gist.github.com/statgeek/2de1faf1644dc8160fe721056202f111

[mirmousavi]

Thank you Reeza,

The GitHub link was perfect.  It worked out great for my purpose.