Deleting a category if it contains missing variables

[ilikesas]

Hi,

suppose that I have the following table:

Company	date	name	Value
A	20/10/2010	A1	12e
A	20/10/2010	A2
A	25/02/2012	A1	e4
A	25/02/2012	A2	h5
B	20/10/2010	B1	w3
B	20/10/2010	B2	wr

There is a missing value in the table, so I want to delete its row, as well as all the rows of the same Company-date combination, so the new table should be like this:

Company	date	name	value
A	25/02/2012	A1	e4
A	25/02/2012	A2	h5
B	20/10/2010	B1	w3
B	20/10/2012	B2	wr

Thank you!

[Tom]

* DOW loops ;

data want ;

  do until (last.date);

    set have ;

    by company date ;

    any = any or missing(value);

  end;

do until (last.date);

   set have ;

   by company date ;

  if not any then output;

end;

drop any ;

run;

* or Self merge ;

data want ;

  merge have have (in=in2 keep=company date value rename=(value=_value) where=(missing(_value))) ;

  by company date ;

  if in2 then delete ;

  drop _value;

run;

[Haikuo]

2XDOW/PROC SQL plus CMISS() should do.

data want;

do until (last.date);

set have;

by company date;

if cmiss(of _all_) >0 then flag=1;

end;

do until (last.date);

set have;

by company date;

if flag ne 1 then output;

end;

run;

If using Proc SQL, then you can't use _all_. You can either spell all of your variable explicitly or pull them out using dictionary.columns if you have a lot variables to save some typing.

Haikuo

[stat_sas · Accepted solution]

proc sql;

create table want as

select * from have

group by company, date

having sum(value='')=0;

quit;

[Patrick]

Or hash:

data want;

  set have;

  if _n_=1 then

    do;

      dcl hash h(dataset:'have(where=(missing(value)))');

      _rc=h.defineKey('company','date');

      _rc=h.defineDone();

      drop _rc;

    end;

  if h.check()=0 then delete;

run;

[ilikesas]

Please note that all the codes here give the desired result (I wish that I could mark them all as Correct or Helpful).

For a beginner like me the simplest and most intuitive code is the one by stat@sas, but I learned much from the other codes and therefore I thank all of you for helping!!!