Help using Base SAS procedures

Variables Containing all Missing Values

Reply
Regular Contributor
Posts: 195

Variables Containing all Missing Values

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

PROC Star
Posts: 7,363

Variables Containing all Missing Values

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

Super Contributor
Posts: 1,636

Variables Containing all Missing Values

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

Super User
Super User
Posts: 6,500

Re: Variables Containing all Missing Values

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;

Super Contributor
Posts: 1,636

Re: Variables Containing all Missing Values

Thank you Tom!

Valued Guide
Posts: 765

Re: Variables Containing all Missing Values

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

PROC Star
Posts: 7,363

Re: Variables Containing all Missing Values

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.

Super Contributor
Posts: 1,636

Re: Variables Containing all Missing Values

Hi Art,

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

PROC Star
Posts: 7,363

Re: Variables Containing all Missing Values

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.

Super Contributor
Posts: 1,636

Re: Variables Containing all Missing Values

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 colSmiley Happy;

  n=n(of colSmiley Happy;

  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;

Valued Guide
Posts: 765

Re: Variables Containing all Missing Values

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 colSmiley Happy 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_

Super Contributor
Posts: 1,636

Re: Variables Containing all Missing Values

Thank you Mike!

Super Contributor
Posts: 1,636

Re: Variables Containing all Missing Values

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 colSmiley Happy 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


Valued Guide
Posts: 765

Re: Variables Containing all Missing Values

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='.';

Super Contributor
Posts: 1,636

Re: Variables Containing all Missing Values

Thank you Mike!

Ask a Question
Discussion stats
  • 19 replies
  • 663 views
  • 1 like
  • 7 in conversation