topic Re: Proc Freq Missing Values all Fields in SAS Programming

Proc Freq Missing Values all Fields

AZIQ1 — Wed, 23 Aug 2017 15:39:31 GMT

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

_____________________________________________________________

Re: Proc Freq Missing Values all Fields

Reeza — Mon, 14 Aug 2017 14:43:12 GMT

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;

Re: Proc Freq Missing Values all Fields

AZIQ1 — Mon, 14 Aug 2017 15:01:26 GMT

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

Re: Proc Freq Missing Values all Fields

Reeza — Mon, 14 Aug 2017 15:07:47 GMT

@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

Re: Proc Freq Missing Values all Fields

AZIQ1 — Mon, 14 Aug 2017 15:13:12 GMT

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

Best

Re: Proc Freq Missing Values all Fields

Ksharp — Tue, 15 Aug 2017 14:27:51 GMT


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;

Re: Proc Freq Missing Values all Fields

AZIQ1 — Tue, 22 Aug 2017 19:19:41 GMT

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.

Re: Proc Freq Missing Values all Fields

Reeza — Wed, 23 Aug 2017 14:48:16 GMT

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