topic How to loop through columns of dataset and test for missing values in SAS Programming

How to loop through columns of dataset and test for missing values

UdayGuntupalli — Thu, 06 Dec 2018 21:37:31 GMT

Hello All,
I would like the community's help on looping around the columns of a data set, check if a column has at least one row with a missing value.

data values;
   input @1 var1 3. @5 var2 3.;
   if missing(var1) then
      do;
         put 'Variable 1 is Missing.';
      end;
   else if missing(var2) then
      do;
         put 'Variable 2 is Missing.';
      end;
   datalines;
127
988 195
;
run;

This is the manual way I can see from Missing() documentation and it would work for small datasets. But I would like the ability to do this over large data sets easily.

Re: How to loop through columns of dataset and test for missing values

mkeintz — Thu, 06 Dec 2018 21:49:38 GMT

You can loop through columns by specifying an ARRAY structure (typically a one-dimensional array), where there is one array element for each variables of interest:

data values;
   input @1 var1 3. @5 var2 3.;
  array v {2} var1 var2;
  do i=1 to 2;
     if missing(v{I}) then put 'Variable ' I 'is Missing.'  v{I}=;
  end;
datalines;
127
988 195
;
run;

Re: How to loop through columns of dataset and test for missing values

UdayGuntupalli — Thu, 06 Dec 2018 22:05:17 GMT

@mkeintz,
This looks great. However, I would like to request some extra details around the array declaration for my learning:
1. Why are you manually declaring column names after the declaration of the array ?

data values;
   input @1 var1 3. @5 var2 3.; 
  array v {2} var1 var2; * Manually declaring columns here ?; 
  do i=1 to 2;
     if missing(v{I}) then put 'Variable ' I 'is Missing.'  v{I}=;
  end;
datalines;
127
988 195
;
run;

2. Is there a way to automatically get all the column names and use it in the loop ?

Re: How to loop through columns of dataset and test for missing values

r_behata — Thu, 06 Dec 2018 22:08:55 GMT

Slightly modified version

data values;
	input @1 var1 3. @5 var2 3.;
	array var[*] var:;
	do i=1 to dim(var);
		if missing(var[i]) then do;
			put "Variable "  i "is missing";
		end;
	end;
drop i ;
datalines;
127
988 195
;
run;

Re: How to loop through columns of dataset and test for missing values

mkeintz — Thu, 06 Dec 2018 22:18:26 GMT

@UdayGuntupalli wrote:

This looks great. However, I would like to request some extra details around the array declaration for my learning:
1. Why are you manually declaring column names after the declaration of the array ?

Because

The array statement needs a way to have correspondence between the array element and the reference variable. A specific listg of variable names in one way to do this, and you only showed 2 variables.
You didn't specify whether there are other variables not to include in the array.

@UdayGuntupalli@ also wrote:

2. Is there a way to automatically get all the column names and use it in the loop ?

By "all the columns names" do you mean all the numeric variables? If yes, then use
array v {*} _numeric_ ;
and
do i=1 to dim(v);

Re: How to loop through columns of dataset and test for missing values

Ksharp — Fri, 07 Dec 2018 12:46:53 GMT

ods select none;
ods output nlevels=want;
proc freq data=have nlevels;
table _all_;
run;
ods select all;
proc sql noprint;
select TableVar into : missing_variables separated by ' '
from want
where NNonMissLevels=0;
quit;
%put Missing variables are: &missing_variables ;