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.

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.

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;

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.

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.

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