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
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;
DN, What a NICE Christmas present! Very much appreciated and happy holidays to you and yours. Art
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
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;
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.