To identify missing values

[Ruth]

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!

[andreas_lds]

Your can use the cmiss-function:

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

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

[Ruth]

Great piece of knowledge. Thanks.

Ruth.

[DLing]

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

[DF]

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;

[Florent]

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;

[Ruth]

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

[Florent]

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