BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AZIQ1
Quartz | Level 8

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

 

_____________________________________________________________

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
Reeza
Super User

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
Quartz | Level 8
Thank you so much, the last part - "Format Output" has 0 observations, is there something missing?
Reeza
Super User

@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
Quartz | Level 8
Thank you so much, it worked.
Point noted : Re: Posting

Best
AZIQ1
Quartz | Level 8
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
Super User

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

Ksharp
Super User

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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 14906 views
  • 1 like
  • 3 in conversation