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!
proc sql;
create table want as
select * from have
group by company, date
having sum(value='')=0;
quit;
* 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;
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
proc sql;
create table want as
select * from have
group by company, date
having sum(value='')=0;
quit;
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;
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!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.