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.
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.
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.