topic Re: How to delete rows with missing numbers in SAS Procedures

How to delete rows with missing numbers

Osebeyo — Fri, 08 Jul 2011 14:17:36 GMT

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?

How to delete rows with missing numbers

data_null__ — Fri, 08 Jul 2011 14:33:18 GMT

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

How to delete rows with missing numbers

rtritz — Fri, 08 Jul 2011 18:54:52 GMT

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?

How to delete rows with missing numbers

rtritz — Fri, 08 Jul 2011 19:23:17 GMT

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

How to delete rows with missing numbers

DBailey — Sat, 09 Jul 2011 14:36:19 GMT

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;

How to delete rows with missing numbers

Ksharp — Mon, 11 Jul 2011 05:55:26 GMT

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

Re: How to delete rows with missing numbers

kstatju — Thu, 03 Dec 2015 06:38:40 GMT

Hi Ksharp and altruists

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

Re: How to delete rows with missing numbers

bharathkumar — Thu, 03 Dec 2015 16:59:39 GMT

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;

Re: How to delete rows with missing numbers

bharathkumar — Thu, 03 Dec 2015 17:01:58 GMT

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;

Re: How to delete rows with missing numbers

pmpradhan — Mon, 10 Oct 2016 19:15:08 GMT

Hi Ksharp,

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

Thank you,

Prajwal

Re: How to delete rows with missing numbers

lalohg — Mon, 15 May 2017 13:34:14 GMT

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.

Re: How to delete rows with missing numbers

Rick_SAS — Mon, 15 May 2017 14:39:16 GMT

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

Re: How to delete rows with missing numbers

shahadatunina — Fri, 09 Feb 2018 17:59:31 GMT

thanks, very useful shortcut!!