BookmarkSubscribeRSS Feed
Ruth
Fluorite | Level 6

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!

7 REPLIES 7
andreas_lds
Jade | Level 19

Your can use the cmiss-function:

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

        hasMissings = 0;        
        hasMissings = cmiss(of _all_);
    run;
Ruth
Fluorite | Level 6

Great piece of knowledge. Thanks.

Ruth.

DLing
Obsidian | Level 7

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

DF
Fluorite | Level 6 DF
Fluorite | Level 6

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
Quartz | Level 8

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
Fluorite | Level 6

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

Florent
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1064 views
  • 4 likes
  • 5 in conversation