DATA Step, Macro, Functions and more

Proc Freq Missing Values all Fields

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

Proc Freq Missing Values all Fields

[ Edited ]

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

 

_____________________________________________________________

 


Accepted Solutions
Solution
‎08-14-2017 11:13 AM
Super User
Posts: 22,844

Re: Proc Freq Missing Values all Fields

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;

View solution in original post


All Replies
Solution
‎08-14-2017 11:13 AM
Super User
Posts: 22,844

Re: Proc Freq Missing Values all Fields

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;
Frequent Contributor
Posts: 81

Re: Proc Freq Missing Values all Fields

Thank you so much, the last part - "Format Output" has 0 observations, is there something missing?
Super User
Posts: 22,844

Re: Proc Freq Missing Values all Fields


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

 

Frequent Contributor
Posts: 81

Re: Proc Freq Missing Values all Fields

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

Best
Frequent Contributor
Posts: 81

Re: Proc Freq Missing Values all Fields

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.
Super User
Posts: 22,844

Re: Proc Freq Missing Values all Fields

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

Super User
Posts: 10,611

Re: Proc Freq Missing Values all Fields


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;


☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 278 views
  • 0 likes
  • 3 in conversation