Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

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

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:


Accepted Solutions
Solution
‎07-11-2011 01:23 PM
PROC Star
Posts: 7,468

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

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,799

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

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;

Contributor
Posts: 52

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

Posted in reply to data_null__

Hi data_null Smiley Happy 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.

Respected Advisor
Posts: 3,799

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

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.

Trusted Advisor
Posts: 2,115

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

Ruth,

also, check out this thread:

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

Doc Muhlbaier

Duke

Contributor
Posts: 52

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

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.

Solution
‎07-11-2011 01:23 PM
PROC Star
Posts: 7,468

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

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

Contributor
Posts: 52

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

Thanks, Art. It worked.:smileylaugh:

Super User
Posts: 10,023

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 166 views
  • 7 likes
  • 5 in conversation