topic Re: Checking for missing data in SAS Programming

Checking for missing data

michan22 — Thu, 16 Nov 2017 22:38:31 GMT

Hi everyone!

I have a dataset with 400-500 variables and I would like to check for missing data and generate a line list for observations with missing data.

so far I have:

data _NULL_;

set dataset1;

if numvar=.

then put id=;

run;

However, in addition to the ID, I would also like SAS to print the variables with the missing value for an observation, since I would like to know what variables are actually missing. Is it possible to do that?

Thank you for any advice in advance!

Re: Checking for missing data

HB — Thu, 16 Nov 2017 23:34:45 GMT

With data like

data one;
input one two three;
datalines;
9	2	6
4	.	3
4	9	3
.	10	.
0	3	1
6	.	9
4	7	9
3	8	1
9	1	5
0	1	0
;

You can do this:

data two;
	set one;
	array myvariables one -- three; 
	length names $50;
	names = '';
	do over myvariables; 
		if cmiss(myvariables) then names = catx(', ', names, vname(myvariables));
	end;
run;

And get this:

one	two	three	names
9	2	6
4		3	two
4	9	3
	10		one, three
0	3	1
6		9	two
4	7	9
3	8	1
9	1	5
0	1	0

Maybe that gives you a place to start.

Edit.

Assuming an ID column I didn't use, then adding

	if not cmiss(names);
	drop one -- three;

on the end of that may be exactly what you want.

Re: Checking for missing data

Reeza — Fri, 17 Nov 2017 01:05:30 GMT

Are the variables all the same type, if so what type?

If you're using EG, I suggest the Characterize data task, its quite useful.

Here's a worked example for generating the number of missing/non missing but I'm not sure thats what you're looking for....

https://gist.github.com/statgeek/2de1faf1644dc8160fe721056202f111

Re: Checking for missing data

ballardw — Fri, 17 Nov 2017 16:10:44 GMT

Here is a modification of @HB's approach:

Using arrays would require two arrays, one for character and another for numeric variables as an array may not mix types.

Also since you have largish variable number of variables trying to guess an appropriate size for a single variable to hold all of the names ends up requiring a variable of n*32 + (n-1) characters.

Since I am assuming that you actually want to read the result then using file print to sent the output to results seems like a practical thing though if you have large numbers of missing variables you may get line wrapping.

The if statement involving cmiss(of ...) are to restrict output only to the records with any missing

data one;
input id one two three c1 $;
datalines;
1 9	2	6  abc
2 4	.	3  cds
3 4	9	3  .
4 .	10	.  a
5 0	3	1  b
6 6	.	9  cd
7 4	7	9  e
8 3	8	1  f
9 9	1	5  z
10 0	1	0  .
;
run;

data _null_;
	set one;
   file print;
	array _charvar _character_;
   array _numvar _numeric_;
   if cmiss(of _charvar(*))>0 or cmiss(of _numvar(*)) > 0 then do;
      put "Id: " id @; 
   	do _i_ = 1 to dim(_charvar); 
   		if missing(_charvar[_i_]) then do;
            _varname_= vname(_charvar[_i_]);
            put +1 _varname_ @;
         end;
   	end;
   	do _i_ = 1 to dim(_numvar); 
   		if missing(_numvar[_i_]) then do;
            _varname_= vname(_numvar[_i_]);
            put +1 _varname_ @;
         end;
   	end;
      put;
   end;
run;

Re: Checking for missing data

michan22 — Fri, 17 Nov 2017 20:09:37 GMT

This works thank you!!

I am not very good with arrays and there are still a lot for me to learn, I really appreciate your wonderful advice to solve my problem!

Re: Checking for missing data

ballardw — Fri, 17 Nov 2017 22:51:12 GMT

Here is small suggestion that may help with learning one bit about array (or at least the index portion).

Create a set with no character variables and test the code I provided.

Then ask why do I not get an error trying to use an array of character variables or do I?