topic To identify missing values in SAS Procedures

To identify missing values

Ruth — Thu, 30 Jun 2011 09:47:30 GMT

Hi,

I have a large dataset with 50 variables. My task is to identify cases with missing value in any of the 50 variables. It is not practical that I have to use WHERE statement to specify missing for each of the 50 variables.

Is there any special name list to perform this task? I tried to use _ALL_ , but not work.

Thanks!

To identify missing values

andreas_lds — Thu, 30 Jun 2011 11:25:48 GMT

Your can use the cmiss-function:

    data work.marked;
        set work.somedata;
        length hasMissings 3;

        hasMissings = 0;        
        hasMissings = cmiss(of _all_);
    run;

To identify missing values

Ruth — Thu, 30 Jun 2011 13:23:52 GMT

Great piece of knowledge. Thanks.

Ruth.

To identify missing values

DLing — Thu, 30 Jun 2011 15:33:25 GMT

cmiss is a sas 9.2 feature not available to earlier versions.

Re: To identify missing values

Florent — Fri, 01 Jul 2011 14:29:44 GMT

Hi Ruth,

Maybe the following macro could help on earlier versions of SAS.

Regards,

Florent

%MACRO SearchMissingValues(ds=);
%local lib table;
/* Retrieves the library and table (dataset) name from the parameter of the macro */
%let lib   = %upcase(%scan(&ds, 1, '.'));
%let table = %upcase(%scan(&ds, 2, '.'));
options nonotes;
/* Count the number of variables in the dataset provided as parameter of the macro */
proc sql noprint;
select count(name)
into :cnt
from dictionary.columns
where upcase(libname) = "&lib"
    and upcase(memname) = "&table";
quit;
%let cnt = &cnt; /* Removes the leading and trailing blanks */
/* Retrieves all the variables' names which are stored in the dataset provided as parameter of the macro */
proc sql noprint;
select name
into :var1-:var&cnt
from dictionary.columns
where upcase(libname) = "&lib"
    and upcase(memname) = "&table";
quit;

%IF &cnt > 0 %THEN %DO;
data work.&table (keep= missingVar:);
set &ds;

     %DO i=1 %TO &cnt;
          missingVar&i = missing(&&var&i);
     %END;
   run;

%DO i=1 %TO &cnt;
%let var&i = &&var&i ; /* Removes the leading and trailing blanks */
%put var&i = &&var&i;
%END;
%END;

%MEND SearchMissingValues;

To identify missing values

DF — Fri, 01 Jul 2011 15:34:44 GMT

For older SAS versions you could use arrays. Something like the below (I'm using SAS 9.1.3):

data somedata;

format a1-a50 best12.;

format b1-b50 $1.;

array a{*} a1-a50;

array b{*} $ b1-b50;

do j = 1 to 10000;

do i = 1 to 50;

n = ranuni(-1);

if n > 0.7 then a = n;

else a = .;

n = ranuni(-1);

if n > 0.7 then b = byte(127*ranuni(-1)+32);

else b = '';

end;

output;

end;

drop i j n;

run;

data work.marked;

set work.somedata;

length hasMissings 3;

hasMissings = 0;

array a(*) _numeric_;

array b(*) $ _character_;

do i = 1 to dim(a);

hasMissings = hasMissings + missing(a);

end;

do i = 1 to dim(b);

hasMissings = hasMissings + missing(b);

end;

drop i;

run;

To identify missing values

Ruth — Wed, 07 Mar 2012 21:49:40 GMT

I didn't get a clue about Florent's code. It just has nothing to do with "finding missing values".

To identify missing values

Florent — Thu, 08 Mar 2012 13:00:43 GMT

Certainly because the last data step was missing. I have edited my original post.