BookmarkSubscribeRSS Feed
UrvishShah
Fluorite | Level 6

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

19 REPLIES 19
art297
Opal | Level 21

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

Linlin
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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;

Linlin
Lapis Lazuli | Level 10

Thank you Tom!

MikeZdeb
Rhodochrosite | Level 12

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

art297
Opal | Level 21

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.

Linlin
Lapis Lazuli | Level 10

Hi Art,

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

art297
Opal | Level 21

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.

Linlin
Lapis Lazuli | Level 10

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;

MikeZdeb
Rhodochrosite | Level 12

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_

Linlin
Lapis Lazuli | Level 10

Thank you Mike!

Linlin
Lapis Lazuli | Level 10

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


MikeZdeb
Rhodochrosite | Level 12

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

Linlin
Lapis Lazuli | Level 10

Thank you Mike!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 19 replies
  • 1635 views
  • 1 like
  • 7 in conversation