How to delete rows with missing numbers

[Osebeyo]

Hello,

I have a table with loads of missing values and I will like to delete the rows with the missing values.

The table is in this form

obs     Clicks     Activity     Impression

1          2               7                100

2          -                6                150

3          3               9                170

4          6               2                 -

I will like to delete row 2 and 4, because they have a missing number.

Anyone?

[data_null__]

Using the SAS Online Help decide if NMISS or CMISS is a function that will help you.

[rtritz]

Hello Osebeyo,
A  possible solution using proc sql is given below.
data missing;    
input
 clicks activity impression;
obs = _n_;
datalines;  
2 7 100
. 6 150
3 9 170
6 2 .
;
run;
proc sql;
create table xrow as
select clicks, activity, impression
from missing
where sum(clicks=., activity=., impression=.)=0
order by obs;
quit;
HTH,
Rich 
Osebeyo wrote:
Hello,
I have a table with loads of missing values and I will like to delete the rows with the missing values.
The table is in this form
obs     Clicks     Activity     Impression
1          2               7                100
2          -                6                150
3          3               9                170
4          6               2                 -
I will like to delete row 2 and 4, because they have a missing number.
Anyone?

[rtritz]

Hi again,

Actually, that where clause will work in a data step as well.

data

xrow (drop=obs);

set missing;

by obs;

where sum(clicks=.)=0;

run;

Rich

[DBailey]

Perhaps a more straightforward approach:

proc sql;

delete from tablea

where clicks is missing or activity is missing or impression is missing;

quit;

another alternative:

proc sql;

create table tableb as

select * from tablea

where clicks is not missing and activity is not missing and impression is not missing;

quit;

[Ksharp · Accepted solution]

Just as _null_ said ,use cmiss(), especially for lots of variables.

data temp;
input clicks activity impression;
datalines;
2 7 100
. 6 150
3 9 170
6 2 .
;
run;
data want;
 set temp;
 if cmiss(of _all_) then delete;
run;

Ksharp

[kstatju]

Hi Ksharp and altruists

What about in PROC SQL if there are lots of variables to delete all missing row in any column?

[bharathkumar]

Hello,

In sql we can delete the observations haivng missing values. this solution works for the Numeric variables are numeric only.. 

data temp;
input a b c;
datalines;
2 7 100
. 6 150
3 9 170
6 2 .
;
run;
proc sql;

create table test(drop=d) as 
select *,a+b+c as d from temp having d is not missing;
quit;

[pmpradhan]

Hi Ksharp,

As a begineer in sas, I'm seeing _all_ quite frequently. what does "_all_" mean/do?

Thank you,

Prajwal

[lalohg]

Hi KSharp,

Thanks for all your helps. Suppose I have an ID for each row. How can I modify the code so I can get a data set with only the id and the missing entries of all variables?

thanks

EH.

[shahadatunina]

thanks, very useful shortcut!!

[bharathkumar]

Hello,

Cmiss() option works from sas 9.2 onwards. Missing options works in earlier versions also 

data temp;
input clicks activity impression;
datalines;
2 7 100
. 6 150
3 9 170
6 2 .
;
run;

proc contents data=temp out=t(keep=name) noprint;
run;
proc sql noprint;
select name into :var_list separated by ' ' from t;
select count(1) into :do_end from t;
drop table t;
quit;
%macro t;
data out;
set temp;
%do i=1 %to &do_end.;
if missing(%scan(&var_list.,&i.,' '))=1 then delete;
%end;
run;
%mend;

%t;

[Rick_SAS]

For an example and discussion, see "Complete cases: How to perform listwise deletion in SAS."