Count of missing and nonmissing values for each variable in a SAS data set4 min read

Count missing and Non-missing values for each variable – In SAS, we often need to get the count of missing and non-missing values in a SAS dataset. The code used in this example uses PROC FORMAT to create the format for character and numeric variables to be either “non-missing” or “missing” and then use that format with PROC FREQ.

I have used SASHELP.HEART dataset as an example.

We then get a compact table showing us the number of missing and non-missing for the variable type.

Below are the steps used to help you understand the working of this macro.

1. Create a user-defined format to group missing and non-missing values.

proc format;
value $missfmt ' '='Missing Values' other='Non-Missing';
value missfmt .='Missing Values' other='Non-Missing';
run;

;
2.  Now, let’s create a macro variable with the count of variables in the dataset.

%let dsid=%sysfunc(open(SASHELP.HEART));   /*Open the dataset*/
%let cntvar=%sysfunc(attrn(&dsid, nvars)); /* The value of cntvar is 17*/

3. Get the list of all variables with their type. I have used the PROC CONTENTS procedure to get the variable names and its type in a SAS dataset.

proc contents data=SASHELP.HEART varnum out=var(keep=name type) noprint;
run;

;
Below is the results of the output dataset generated using the PROC CONTENT Procedure.

Results of PROC CONTENTS
Results of PROC CONTENTS

4. Now, I can create macro variables for each value of the SAS dataset.

data _null_;
		set var;
		suffix=put(_n_, 5.);
		call symputx(cats('Name', suffix), Name);
		call symputx(cats('Type', suffix), Type);
run;

;
5. The next step is to find the count of missing and non-missing values using the PROC FREQ procedure. For simplicity, I have taken only one variable form the dataset.

Notice the /missing keyword. This tells SAS to generate the frequency count of missing values as well.

PROC FREQ

6. The next step is to apply the user-defined format to group the missing and non-missing values.

%macro test;
	proc freq data=SASHELP.HEART;
		tables AgeAtDeath /missing nopercent nocum nofreq nopercent 
			out=out1(drop=percent rename=(AgeAtDeath=value));
		format AgeAtDeath 
		%if type=2 %then
			%do;
				$missfmt. %end;
		%else
			%do;
				missfmt. %end;
		;
	run;

;
%mend;
%test;
Missing values

 

7. Enclose the above steps inside a macro and a do loop to get the data for each of the variables. The results will be as below.

PROC FREQ

7. Now, add the variable name as a value in each of the datasets so that we can combine all the datasets and create a single dataset.

data out&i;
set out&i;
varname="&&name&i";

8. The below step is used to combine all the datasets using the set statement.

data final;
set %do i=1 %to &cntvar;
out&i %end;
;
run;;

The combined dataset looks as below.Combined dataset

9. Now, reshape the combined dataset using PROC TRANSPOSE.

proc transpose data=final out=combine(drop=_:);
		by varname;
		id value;
		var count;
	run;

;
sas count missing values for each variable

???? here is the final code...

data hearts;
	set sashelp.heart;
run;

;
proc format;
value $missfmt ' '='Missing Values' other='Non-Missing';
value missfmt .='Missing Values' other='Non-Missing';
run;;
%macro varCounts (dsn=);
%let dsid=%sysfunc(open(&dsn));
%let cntvar=%sysfunc(attrn(&dsid, nvars));
%put &dsid;
%put &cntvar;
proc contents data=&dsn varnum out=var(keep=name type) noprint;
run;;
proc print data=var;
data _null_;
set var;
suffix=put(_n_, 5.);
call symputx(cats('Name', suffix), Name);
call symputx(cats('Type', suffix), Type);
run;;
%do i=1 %to &cntvar;
proc freq data=&dsn noprint;
tables &&name&i /missing nopercent nocum nofreq nopercent
out=out&i(drop=percent rename=(&&name&i=value));
format &&name&i
%if &&type&i=2 %then
%do;
$missfmt. %end;
%else
%do;
missfmt. %end;
;
run;;
data out&i;
set out&i;
varname="&&name&i";
%if &&type&i=1 %then
%do;
value1=put(value, missfmt.);
%end;
%else %if &&type&i=2 %then
%do;
value1=put(value, $missfmt.);
%end;
drop value;
rename value1=value;
run;;
%end;
data final;
set %do i=1 %to &cntvar;
out&i %end;
;
run;;
proc transpose data=final out=combine(drop=_:);
by varname;
id value;
var count;
run;;
proc print data=combine;
%mend;
%varCounts(dsn=hearts);
Download this SAS file.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
sasuser
sasuser
March 19, 2021 5:28 pm

Very Helpful!

2
0
Would love your thoughts, please comment.x
()
x
Scroll to Top