topic Re: Variables Containing all Missing Values in SAS Procedures

Variables Containing all Missing Values

UrvishShah — Thu, 22 Dec 2011 21:15:25 GMT

Hi All

I've one dataset below

data one;

input name $ name1 name2;

cards;

A A A

B B B

C 1 C

D 2 D

;

In above dataset one variable contain all the missing values (See the Output)

Obs name name1 name2

                                    1      A              .          .
                                    2      B              .          .
                                    3      C              1        .
                                    4      D              2        .

Now i want only variable name2 that contains all the missing values

It is very eassy if i've only 3 or 5 variables but if i've 5000 variables then

what should i do ?

Thanks

Variables Containing all Missing Values

art297 — Thu, 22 Dec 2011 22:30:50 GMT

There is a SAS technical note that deals precisely with this issue. Take a look at: http://support.sas.com/kb/24/612.html

Variables Containing all Missing Values

Linlin — Fri, 23 Dec 2011 00:59:38 GMT

Hi Art,

I looked at the link you provided. Can you tell me why the print out is different from what I thought it should be?

Thank you!

data work.ds1;

input(z x c) ($);

datalines;

qwe . yui

. . hjk

zxc . .

;

run;

datalook;

array char(*) $ z x c;

array c_allmiss (3) $ (3*'true');

set ds1 ;

do i=1 to 3;

if char(i) ne ' ' then c_allmiss(i)='false';

end;

run;

proc print;run;

Obs z x c c_allmiss1 c_allmiss2 c_allmiss3 i

1 qwe yui false true false 4

2 hjk false true false 4

3 zxc false true false 4

I thought the output should be:

Obs z x c c_allmiss1 c_allmiss2 c_allmiss3 i

1 qwe yui false true false 4

2 hjk True true false 4

3 zxc false true True 4

Re: Variables Containing all Missing Values

Tom — Fri, 23 Dec 2011 02:03:45 GMT

The array is only initialized on the first iteration of the data set. Add an else clause to set the value to true to get your expected output.

But if you really want to flag variables that are missing on all observations then you only one one output observation.

You need to add code to loop over all of the observations. In that case you do not want the else clause.

data want;
  array char(*) $ z x c;
  array c_allmiss (3) $ (3*'true');
  do until (eof);
    set ds1 end=eof;
    do i=1 to 3;
      if char(i) ne ' ' then c_allmiss(i)='false';
    end;
  end;
  put (c_allmiss: ) (=);
run;

Re: Variables Containing all Missing Values

Linlin — Fri, 23 Dec 2011 02:21:45 GMT

Thank you Tom!

Re: Variables Containing all Missing Values

Linlin — Fri, 23 Dec 2011 02:57:14 GMT

Hi,

My code works if all the missing variables are numeric.

data one;

infile datalines missover;

input name $ name1 name2 name3;

datalines;

A . . .

B . . .

C 1 . .

D 2 . .

;

proc transpose data=one out=two;

run;

data three;

set two;

nmiss=nmiss(of col:);

n=n(of col:);

if nmiss=n+nmiss then output;

run;

proc sql noprint;

select _name_ into :keepvar separated by ' '

from three;

quit;

data want;

set one;

keep &keepvar;

run;

proc print; run;

Re: Variables Containing all Missing Values

MikeZdeb — Fri, 23 Dec 2011 03:41:19 GMT

hi ... some suggestions (using your data set DS1) ...

data look;

set ds1 ;

array ch(3) z x c;

array c_allmiss(3) $5;

do _n_=1 to 3;

c_allmiss(_n_) = ifc(missing(ch(_n_)) , 'true' , 'false');

end;

run;

Obs z x c c_allmiss1 c_allmiss2 c_allmiss3

1 qwe yui false true false

2 hjk true true false

3 zxc false true true

ps good idea to avoid using SAS function names (you used CHAR) as array or variable names

Re: Variables Containing all Missing Values

MikeZdeb — Fri, 23 Dec 2011 04:12:37 GMT

hi ... your code will work for both numeric and character variables with a few changes ...

data one;

infile datalines missover;

input name $ name1 name2 name3 name4 $;

datalines;

A . . . .

B . . . .

C 1 . . .

D 2 . . .

;

proc transpose data=one out=two;

var _all_;

run;

data _null_;

length keep $500;

if 0 then set one nobs=nvar;

do until (done);

set two end=done;

if cmiss(of col:) eq nvar then keep=catx(' ',keep,_name_);

end;

call symputx('keep',keep);

run;

data want;

set one (keep=&keep);

run;

ps since the transpose turns all the observations into variables, the limit on observations is 32,766 since you'll end up with COL1-COL32766 plus _NAME_

Re: Variables Containing all Missing Values

Linlin — Fri, 23 Dec 2011 04:20:25 GMT

Thank you Mike!

Re: Variables Containing all Missing Values

Ksharp — Fri, 23 Dec 2011 04:50:25 GMT

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

Re: Variables Containing all Missing Values

Linlin — Fri, 23 Dec 2011 14:28:37 GMT

data one;

infile datalines missover;

input name $ name1 name2 name3 name4 $;

datalines;

A . . . .

B . . . .

C 1 . . .

D 2 . . .

;

options missing= ' ';

proc transpose data=one out=two;

var _all_;

run;

options missing='.';

data _null_;

length keep $500;

if 0 then set one nobs=nvar;

do until (done);

set two end=done;

if cmiss(of col:) eq nvar then keep=catx(' ',keep,_name_);

end;

call symputx('keep',keep);

run;

data want;

set one (keep=&keep);

run;

proc print data=want;run;

Linlin

Re: Variables Containing all Missing Values

MikeZdeb — Fri, 23 Dec 2011 21:19:00 GMT

hi ... sorry, forgot one little thing ... just add an OPTIONS statement prior to TRANSPOSE (and reset after TRANSPOSE) ...

options missing='';

proc transpose data=one out=two;

var _all_;

run;

options missing='.';

Re: Variables Containing all Missing Values

Linlin — Fri, 23 Dec 2011 22:59:29 GMT

Thank you Mike!

Re: Variables Containing all Missing Values

art297 — Fri, 23 Dec 2011 23:11:40 GMT

Linlin, Did you get the answer to the question you asked? The link I provided was to a SAS technical note. The logic was simply if any non-missing value was found for a column, any additional checks would be irrelevant as one would already know that at least one non-missing value existed.

Re: Variables Containing all Missing Values

Linlin — Fri, 23 Dec 2011 23:22:02 GMT

Hi Art,

Yes. I understand now. Merry Christmas and Happy New Year to you!

Re: Variables Containing all Missing Values

art297 — Fri, 23 Dec 2011 23:27:33 GMT

And to you, and everyone, as well. I probably shouldn't post this here, but if you are interested in some SAS-related holiday season stuff that has been posted on SAS-L over the years, take a look at:

http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1112d&L=sas-l&D=1&O=A&P=679

There are only two posts in the thread, but I think you might like both of them.

Re: Variables Containing all Missing Values

data_null__ — Sat, 24 Dec 2011 13:31:44 GMT

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;

Re: Variables Containing all Missing Values

art297 — Sat, 24 Dec 2011 16:05:34 GMT

DN, What a NICE Christmas present! Very much appreciated and happy holidays to you and yours. Art

Re: Variables Containing all Missing Values

Ksharp — Mon, 26 Dec 2011 04:01:41 GMT

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

Re: Variables Containing all Missing Values

art297 — Mon, 26 Dec 2011 04:24:07 GMT

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;