Variables Containing all Missing Values

[Ksharp]

data work.ds1;              
input(z  x c) ($) d;
datalines;                 
qwe . yui .       
. . hjk .         
zxc . . .         
;
run;

%macro missing;
proc sql ;
 select count(*) into : count from ds1;

 select 'nmiss('||strip(name)||') as '||strip(name)
  from dictionary.columns
   where libname='WORK' and memname='DS1';
 select 'nmiss('||strip(name)||') as '||strip(name) into : name1 - : name&sqlobs.
  from dictionary.columns
   where libname='WORK' and memname='DS1';

create table temp as
 select %do i=1 %to &sqlobs.;
         &&name&i %if &i ne &sqlobs. %then %do;,%end;
        %end;
  from ds1 ;
quit;

data _null_;
 set temp;
 array _a{*} _numeric_;
 do i=1 to dim(_a);
  if _a{i}=&count then do; 
                          _x+1;
                          call symputx(cats('list',_x),vname(_a{i}));
                          call symputx('end',_x);
                       end;
 end;
run;  
data want;
 set ds1;
 keep %do j=1 %to &end ;
      &&list&j
      %end;  ;
run;
%mend missing;

%missing

Ksharp

[data_null__]

From the title of your post it sounds like you would like a list of variables names for variables with missing values on every observation.

data have;
   infile cards missover;
   missing a b; /* test special missing */
   input name$ a   b   c   d   e f$ i;
   cards;
 x 2 . . . .
 y . . 3 . .
 z . a . . .
 p . . . b .
 q . . . . .
;;;;
   run;
proc print;
   run;

/* For performance, we only care about missing */ 
proc format;
   value allmiss ._-.z=. other=1;
   value $allmiss ' '=' ' other='1';
   run;

ods select nlevels;
ods output nlevels=nlevels;
proc freq data=have nlevels;
   format  _character_ $allmiss. _numeric_ allmiss.;
   run;
ods output close;

data nlevels; 
   /*The variable NNonMissLevels will not exist when there are no variables with 
   missing values.  One of the many oddities of ODS OUTPUT*/  
   retain NNonMissLevels -1; 
   set nlevels;
   run;
proc print;
   run;

/* Put the list of variables into a macro variable */ 
%let allMiss=;
proc sql noprint;
   select tableVar into :allmiss separated by ' '
   from Nlevels where NNonMissLevels = 0;
   quit;
   run;
%put Variables with all missing values, ALLMISS=&allMiss;

[art297]

DN, What a NICE Christmas present!  Very much appreciated and happy holidays to you and yours.  Art

[Ksharp]

Null . But I found a problem in your code. If value of all the variables are alll non-missing ,your code will get wrong result.  Take a look.

You will get all the name of variables.

data work.have;              
input(z  x c) ($) d;
datalines;                 
qwe q yui 2       
qwe q hjk 1         
qwe q dsd 4         
;
run;
 
/* For performance, we only care about missing */ 
proc format;
   value allmiss ._-.z=. other=1;
   value $allmiss ' '=' ' other='1';
   run;
 
ods select nlevels;
ods output nlevels=nlevels;
proc freq data=have nlevels;
   format  _character_ $allmiss. _numeric_ allmiss.;
   run;
ods output close;
 
data nlevels; 
   /*The variable NNonMissLevels will not exist when there are no variables with 
   missing values.  One of the many oddities of ODS OUTPUT*/  
   retain NNonMissLevels 0; 
   set nlevels;
   run;

/* Put the list of variables into a macro variable */ 
%let allMiss=;
proc sql noprint;
   select tableVar into :allmiss separated by ' '
   from Nlevels where NNonMissLevels = 0;
   quit;
   run;
%put Variables with all missing values, ALLMISS=&allMiss;

Ksharp

[art297]

KSharp,

I still like the concept and think that if you exchange the retain from zero to dot, it will perform correctly.  I.e.:

data have;

   infile cards missover;

   missing a b; /* test special missing */

   input name$ a   b   c   d   e f$ i;

   cards;

x 2 . . . .

y . . 3 . .

z . a . . .

p . . . b .

q . . . . .

;;;;

   run;

/* For performance, we only care about missing */

proc format;

   value allmiss ._-.z=. other=1;

   value $allmiss ' '=' ' other='1';

   run;

ods select nlevels;

ods output nlevels=nlevels;

proc freq data=have nlevels;

   format  _character_ $allmiss. _numeric_ allmiss.;

   run;

ods output close;

data nlevels;

   /*The variable NNonMissLevels will not exist when there are no variables with

   missing values.  One of the many oddities of ODS OUTPUT*/ 

   retain NNonMissLevels .;

   set nlevels;

   run;

proc print;

   run;

/* Put the list of variables into a macro variable */

%let allMiss=;

proc sql noprint;

   select tableVar into :allmiss separated by ' '

   from Nlevels where NNonMissLevels = 0;

   quit;

   run;

%put Variables with all missing values, ALLMISS=&allMiss;