Help using Base SAS procedures

How to delete rows with missing numbers

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

How to delete rows with missing numbers

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?


Accepted Solutions
Solution
‎07-11-2011 01:55 AM
Super User
Posts: 9,681

How to delete rows with missing numbers

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,777

How to delete rows with missing numbers

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

Contributor
Posts: 23

How to delete rows with missing numbers

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?

Contributor
Posts: 23

How to delete rows with missing numbers

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

Super Contributor
Posts: 578

How to delete rows with missing numbers

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;

Solution
‎07-11-2011 01:55 AM
Super User
Posts: 9,681

How to delete rows with missing numbers

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

Occasional Contributor
Posts: 6

Re: How to delete rows with missing numbers

Hi and altruists

 

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

Occasional Contributor
Posts: 6

Re: How to delete rows with missing numbers

Hello,

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

 

 

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;

New Contributor
Posts: 3

Re: How to delete rows with missing numbers

Hi Ksharp,

 

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

 

Thank you,

Prajwal

Contributor
Posts: 33

Re: How to delete rows with missing numbers

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.

 

 

Occasional Contributor
Posts: 6

Re: How to delete rows with missing numbers

[ Edited ]

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;

 

 

SAS Super FREQ
Posts: 3,478

Re: How to delete rows with missing numbers

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 53492 views
  • 9 likes
  • 10 in conversation