Count of Non Missing Values in SAS – Non Missing across rows and columns

Count of NON Missing values in SAS accomplished in roundabout way. Count of Non Missing values across rows in SAS is obtained using cmiss() function in roundabout way. Count of NON Missing values across columns in SAS is obtained using PROC FREQ in SAS.

  • Count of NON missing values in SAS
  • Count of row wise non missing values in SAS
  • Count of column wise non missing values in SAS

So we will be using EMP_DET Table in our example
Count of Non Missing Values in SAS – Non Missing across rows and columns 1

 

 

Row Wise Count of Non Missing Values in SAS:

In order to count row wise missing values we will be using cmiss() function which is shown below

Step 1:

We will be using symputx()  function get the total variables



proc contents data=EMP_DET out=cols noprint; 
run; 


data _null_; 
  set cols nobs=total; 
  call symputx('totvar', total); 
run; 

 

Step 2:

  • cmiss() function gets the row wise count of missing values
  • Total variables – count of missing values will give count of total row wise non missing values in SAS


data EMP_DET1; 
set EMP_DET; 
totalvar=&totvar; 
totmiss=cmiss(of Employee--state); 
totnonmiss=totalvar- cmiss(of Employee--state); 
run; 

So the resultant table with total variables , row wise total missing values and total non missing values will be

Count of Non Missing Values in SAS – Non Missing across rows and columns 2

 

 

Column wise of Count Non Missing Values in SAS:

In order to count the column wise missing values in SAS we will be using roundabout method which is explained below

Step 1:

Specify a format for the variables so that the missing values all have one value and the nonmissing values have another value.


proc format; 
   value $missfmt ' '='Missing' other='Not Missing'; 
   value missfmt .  ='Missing' other='Not Missing'; 
run;


Step 2:

  • PROC FREQ groups a variable’s values according to the formatted values.
  • Specify the MISSING AND MISSPRINT options on the TABLES statement.
  • Use the _CHAR_ and _NUMERIC_ keywords on the TABLES statement to specify that the FREQ procedure should compute statistics for all character or all numeric variables.

proc format; 
  value $missfmt ''='Missing' other= 'Not Missing'; 
  value missfmt . ='Missing' other = 'Not Missing'; 
  run; 
   
proc freq data=EMP_DET; 
format  _CHAR_ $missfmt.; 
table  _CHAR_ / missing missprint nocum nopercent; 
format _NUMERIC_  missfmt.; 

So the column wise non missing values for each column will be,

Count of Non Missing Values in SAS – Non Missing across rows and columns 3

 

                                                                                 

 

Author

  • Sridhar Venkatachalam

    With close to 10 years on Experience in data science and machine learning Have extensively worked on programming languages like R, Python (Pandas), SAS, Pyspark.

    View all posts