BookmarkSubscribeRSS Feed
Ksharp
Super User
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__
Jade | Level 19

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
Opal | Level 21

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

Ksharp
Super User

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
Opal | Level 21

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 19 replies
  • 3720 views
  • 1 like
  • 7 in conversation