BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ruth
Fluorite | Level 6

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:

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

8 REPLIES 8
data_null__
Jade | Level 19

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
Fluorite | Level 6

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.

data_null__
Jade | Level 19

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
Rhodochrosite | Level 12

Ruth,

also, check out this thread:

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

Doc Muhlbaier

Duke

Ruth
Fluorite | Level 6

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
Opal | Level 21

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
Fluorite | Level 6

Thanks, Art. It worked.:smileylaugh:

Ksharp
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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