SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Deleting a category if it contains missing variables

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

Deleting a category if it contains missing variables

Hi,

suppose that I have the following table:

CompanydatenameValue
A20/10/2010A112e
A20/10/2010A2
A25/02/2012A1e4
A25/02/2012A2h5
B20/10/2010B1w3
B20/10/2010B2wr

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:


Companydatenamevalue
A25/02/2012A1e4
A25/02/2012A2h5
B20/10/2010B1w3
B20/10/2012B2wr



Thank you!


Accepted Solutions
Solution
‎07-18-2015 09:13 PM
Trusted Advisor
Posts: 1,204

Re: Deleting a category if it contains missing variables

proc sql;

create table want as

select * from have

group by company, date

having sum(value='')=0;

quit;

View solution in original post


All Replies
Super User
Super User
Posts: 6,498

Re: Deleting a category if it contains missing variables

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

Respected Advisor
Posts: 3,124

Re: Deleting a category if it contains missing variables

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

Solution
‎07-18-2015 09:13 PM
Trusted Advisor
Posts: 1,204

Re: Deleting a category if it contains missing variables

proc sql;

create table want as

select * from have

group by company, date

having sum(value='')=0;

quit;

Respected Advisor
Posts: 3,887

Re: Deleting a category if it contains missing variables

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;

Super Contributor
Posts: 413

Re: Deleting a category if it contains missing variables

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 483 views
  • 6 likes
  • 5 in conversation