I want to check if all variables of a data set are missing except one, but without knowledge about the variable names.
Use the CMISS() function to count number of missing. You will need to know how many variables there are to get your test of "except one". Here is a trick to dynamically count the variables in a datastep by making two arrays. You will need to introduce an extra character variable to make sure it works for datasets with zero character variables.
data x;
input x1 x2 x3 x4 x5;
nmiss=cmiss(of _all_)-1;
array _n _numeric_;
array _c $1 _c_ _character_;
drop _c_ ;
nvars = dim(_n) + dim(_c) -2 ;
except1 = 1 = (nvars - nmiss);
datalines;
1 2 3 4 5
. 2 . 4 5
1 2 3 4 .
1 . . . .
. . . . .
;
It might be easier to just add an extra step that finds the number of variables and puts it into a macro variable instead.
data x;
input x1 x2 x3 x4 x5;
datalines;
1 2 3 4 5
. 2 . 4 5
1 2 3 4 .
1 . . . .
. . . . .
;
proc sql noprint;
select count(*) into :nvars trimmed
from dictionary.columns
where libname='WORK' and memname='X'
;
quit;
data want;
set x;
nmiss=cmiss(of _all_)-1;
except1 = 1 = (&nvars - nmiss);
run;
... View more
