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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.