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!
Your can use the cmiss-function:
data work.marked; set work.somedata; length hasMissings 3; hasMissings = 0; hasMissings = cmiss(of _all_); run;
Great piece of knowledge. Thanks.
Ruth.
cmiss is a sas 9.2 feature not available to earlier versions.
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;
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;
I didn't get a clue about Florent's code. It just has nothing to do with "finding missing values".
Certainly because the last data step was missing. I have edited my original post.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.