Proc Freq Missing Values all Fields

[AZIQ1]

Original Post:

________________________________________

Hi,

I want to run a proc freq on all the fields (columns) and count the missing values. I have more than 100 fields - is there a way to use wildcard for all fields like *

Data:

Age   Race    Ethnicity   Gender

10      W              C              .

.          W              C             M

.          W              C              F

Data have:

The final table should look like this:

             Missing        Not Missing           Total

Age             2                      1                        3

Race           0                      3                        3

Ethnicity      0                      3                        3

Gender        1                      2                        3

Thank you

_Original question was solved - Thank you

_____________________________________________________________

[Reeza · Accepted solution]

You can use the shortcuts:

_all_ -> all variables

_character_ -> all character variables

_numeric_ -> all numeric variables

*create sample data to work with;
data class;
	set sashelp.class;

	if age=14 then
		call missing(height, weight, sex);

	if name='Alfred' then
		call missing(sex, age, height);
	label age="Fancy Age Label";
run;

*create format for missing;
proc format ;
	value $ missfmt ' '="Missing" other="Not Missing";
	value nmissfmt .="Missing" other="Not Missing";
run;

*Proc freq to count missing/non missing;
ods table onewayfreqs=temp;
proc freq data=class;
	table _all_ / missing;
	format _numeric_ nmissfmt. _character_ $missfmt.;
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;

[AZIQ1]

Thank you so much, the last part - "Format Output" has 0 observations, is there something missing?

[Reeza]

---

@AZIQ1 wrote:
Thank you so much, the last part - "Format Output" has 0 observations, is there something missing?

---

When you tried to use this with your data or the sample program? Please include more details in your posts, remember we can't see your computer or know what you're referring to unless you explicitly state it.

The program above works fine on my machine and generates the results:

509
510 *Format output;
511 data want;
512 length variable $32. variable_value $50.;
513 set temp;
514 Variable=scan(table, 2);
515 Variable_Value=strip(trim(vvaluex(variable)));
516 keep variable variable_value frequency percent cum:;
517 label variable='Variable' variable_value='Variable Value'
517! ;
518 run;

NOTE: There were 9 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.WANT has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

[AZIQ1]

Thank you so much, it worked.
Point noted : Re: Posting

Best

[AZIQ1]

Thank you again,
I want to stratify proc freq using by variable-
how can I format output so that I get the out put by my stratified vars. e.g by year and class.

[Reeza]

Post a new question and post what you've tried so far.

[Ksharp]

data have;
input Age   (Race    Ethnicity   Gender) ($);
cards;
10      W              C              .
.          W              C             M
.          W              C              F
;
run;
proc sql;
select 'Age    ',nmiss(age) as missing,n(age) as Not_Missing,count(*) as total from have
union
select 'Race    ',nmiss(Race) as missing,n(Race) as Not_Missing,count(*) as total from have
union
select 'Ethnicity    ',nmiss(Ethnicity) as missing,n(Ethnicity) as Not_Missing,count(*) as total from have
;
quit;