topic Re: Deleting a category if it contains missing variables in SAS Data Management

Deleting a category if it contains missing variables

ilikesas — Sat, 18 Jul 2015 22:19:47 GMT

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!

Re: Deleting a category if it contains missing variables

Tom — Sat, 18 Jul 2015 23:37:16 GMT

* 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;

Re: Deleting a category if it contains missing variables

Haikuo — Sat, 18 Jul 2015 23:42:11 GMT

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

Re: Deleting a category if it contains missing variables

stat_sas — Sun, 19 Jul 2015 01:13:27 GMT

proc sql;

create table want as

select * from have

group by company, date

having sum(value='')=0;

quit;

Re: Deleting a category if it contains missing variables

Patrick — Sun, 19 Jul 2015 01:24:47 GMT

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;

Re: Deleting a category if it contains missing variables

ilikesas — Sun, 19 Jul 2015 16:53:22 GMT

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!!!