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.
Very Helpful!