topic How to drop a variable as long as it contains missing value? in SAS Procedures

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

Ruth — Mon, 11 Jul 2011 14:02:27 GMT

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:

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

data_null__ — Mon, 11 Jul 2011 16:23:28 GMT

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;

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

Doc_Duke — Mon, 11 Jul 2011 16:28:37 GMT

Ruth,

also, check out this thread:

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

Doc Muhlbaier

Duke

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

Ruth — Mon, 11 Jul 2011 16:46:51 GMT

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.

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

Ruth — Mon, 11 Jul 2011 16:49:05 GMT

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.

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

data_null__ — Mon, 11 Jul 2011 17:04:33 GMT

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.

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

art297 — Mon, 11 Jul 2011 17:23:56 GMT

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

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

Ruth — Mon, 11 Jul 2011 17:52:14 GMT

Thanks, Art. It worked.:smileylaugh:

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

Ksharp — Tue, 12 Jul 2011 07:57:15 GMT

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