How to drop a variable as long as it contains missing value?

[Ruth]

Hi, I just have a data set which contains many variables (>30). For this data set, any variables with missing values are problematic. So the task is to delete all those variables as long as they contain missing values. How can I do that? :smileyconfused:

[data_null__]

This code will create a macro variable that contains the name off the variables with missvalues on every observations.  You can use the macro variable in a DROP data set option or DROP statement.

data have;

   missing a b;

   input name$ a   b   c   d   e;

   cards;

x 2 . . . .

y . . 3 . .

z . a . . .

p . . . b .

q . . . . .

;;;;

   run;

proc print;

   run;

proc format;

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

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

   run;

ods select nlevels;

ods output nlevels=nlevels;

proc freq levels;

   format  _character_ $allmiss. _numeric_ allmiss.;

   run;

ods output close;

data nlevels; /*NNonMissLevels will not exist when there are no variables with missing values*/

   retain NNonMissLevels -1;

   set nlevels;

   run;

%let allMiss=;

proc sql noprint;

   select tableVar into :allmiss separated by ' '

   from Nlevels where NNonMissLevels = 0;

   quit;

   run;

%put allMiss=&allMiss;

[Ruth]

Hi data_null the code is pretty complex. I tested it in SAS but not seemingly worked. SAS said there is no nlevels etc. Is there any SAS function or simple step that deals with this? That is, delete the variables if they contain missing values. Thank you a lot.

[data_null__]

You could take a few minutes and lookup the bits of the code that you don't understand.  It seems like a straight forward use of SAS to me.

Or you could include a log excerpt to help debug the program. 

You will want to modify the PROC FREQ call to include a DATA= option.  That may be your problem, but then I don't know what you did.

[Doc_Duke]

Ruth,

also, check out this thread:

http://communities.sas.com/thread/30192?tstart=0

Doc Muhlbaier

Duke

[Ruth]

Hi Doc, the thread you gave to me is about dealing with rows. In my case, I am dealing with the columns (ie. delete the variable if it contains missing data). Cheers.

[art297 · Accepted solution]

Ruth,

This is a slight variant of the code that datanull offered.  Does it do what you want?

data have;

   input name$ a   b   c   d   e;

   cards;

x 2 . . . .

y 2 . 3 . .

z 2 5 . . .

p 1 . . 2 .

q 2 . . . .

;;;;

   run;

proc sql noprint;

  create table miss as

    select distinct

proc print;

   run;

proc format;

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

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

   run;

ods select nlevels;

ods output nlevels=nlevels;

proc freq levels;

   format  _character_ $allmiss. _numeric_ allmiss.;

   run;

ods output close;

proc sql noprint;

   select tableVar into :no_miss separated by ' '

   from Nlevels where NMissLevels = 0;

   quit;

   run;

data want;

  set have (keep=&no_miss.);

run;

Art

[Ruth]

Thanks, Art. It worked.:smileylaugh:

[Ksharp]

How about:

data have;
   input name$ a   b   c   d   e;
   cards;
x 2 . . . .
y 2 . 3 . .
z 2 5 . . .
p 1 . . 2 .
q 2 . . . .
;;;;
   run;
data _null_;
 set have end=last;
 array char_var{*} $ 32 _character_;
 array num_var{*} _numeric_;
 array char_var_name{200} $ 32;
 array num_var_name{200} $ 32;
 do i=1 to dim(char_var);
  if missing(char_var{i}) then char_var_name{i}=vname(char_var{i});
 end;
 do j=1 to dim(num_var);
  if missing(num_var{j}) then num_var_name{j}=vname(num_var{j});
 end;
 if last then call symputx('drop_var',catx(' ',of char_var_name{*} num_var_name{*}));
run;
%put &drop_var;
data want;
 set have(drop=&drop_var);
run;

Ksharp