BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Osebeyo
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

12 REPLIES 12
data_null__
Jade | Level 19

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

rtritz
Calcite | Level 5

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Super User

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
Fluorite | Level 6

Hi and altruists

 

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

bharathkumar
Fluorite | Level 6

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;

pmpradhan
Quartz | Level 8

Hi Ksharp,

 

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

 

Thank you,

Prajwal

lalohg
Quartz | Level 8

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
Calcite | Level 5

thanks, very useful shortcut!!

bharathkumar
Fluorite | Level 6

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-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 112525 views
  • 12 likes
  • 11 in conversation