Count of missing and nonmissing values for each variable in a SAS data set

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 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.

We 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 to help you understand the working of this macro.

Step 1. Create a user-defined format for the group missing and non-missing values.

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

Step 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*/

Step 3. Get the list of all variables with their type. I have used the PROC CONTENTS procedure to get the variable names and their 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.

Step 4. Now, we 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;

Step 5. The next step is to find the count of missing and non-missing values using the PROC FREQ procedure.

For simplicity, we have taken only one variable from the dataset.

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

Step 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;

Step 7. Enclose the above steps inside a macro, and a do loop to get the variables’ data. The results will be as below.

Step 8. Now, add the variable name as a value in each dataset to combine and create a single dataset.

data out&i; 
set out&i;
length varname $32.; 
varname="&&name&i";

Step 9. The below step combines 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.

Step 10. Now, reshape the combined dataset using PROC TRANSPOSE.

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

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.