Checking for missing data

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Checking for missing data

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!


Accepted Solutions
Solution
‎11-17-2017 03:05 PM
Super User
Posts: 12,750

Re: Checking for missing data

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;

View solution in original post


All Replies
Super Contributor
Super Contributor
Posts: 254

Re: Checking for missing data

[ Edited ]

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.

Super User
Posts: 22,591

Re: Checking for missing data

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

Solution
‎11-17-2017 03:05 PM
Super User
Posts: 12,750

Re: Checking for missing data

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;
Contributor
Posts: 56

Re: Checking for missing data

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!

Super User
Posts: 12,750

Re: Checking for missing data

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?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 182 views
  • 4 likes
  • 4 in conversation