Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- How to delete rows with all missing numbers from a range of variables

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 11-14-2022 04:04 PM
(640 views)

Hi,

I want to delete rows that have missing values for a list of consecutive variables. For example, from a the a dataset like:

id cat var1 var2 var3 var4 stop

42 a 1 0 0 1 Y

43 b . . . . Y

44 a 1 . 0 1 N

45 b 0 . . 1 Y

I want to delete row 43 which is missing data from var1 to var 4.

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

My apologies. It looks like you can't use a -- in a WHERE statement. Which I guess makes sense, because it relies on the order of variables in the PDV.

It should work if you change to a subsetting IF, e.g.:

```
12 data want;
13 set have ;
14 if cmiss(OF var1 -- zvariable) < 5 ; *IF not where;
15 run;
NOTE: There were 1 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 1 observations and 3 variables.
```

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

14 REPLIES 14

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Does it have to be missing values for all variables from var1 to var4?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Yes, missing all values from var 1 to 4.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Try this

```
data have;
input id cat $ var1 var2 var3 var4 stop $;
datalines;
42 a 1 0 0 1 Y
43 b . . . . Y
44 a 1 . 0 1 N
45 b 0 . . 1 Y
;
data want;
set have;
where n(var1-var4);
run;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

What defines the range of variables?

You can list them:

```
data want;
set have;
where n(var1, xvar2, yname3, zvariable) ;
run;
```

Or if you know the four variables are in order in the PDV you could use:

```
data want;
set have;
where n(of var1 -- zvariable) ;
run;
```

If you want to do it based on variable position (which is odd) I would run PROC CONTENTS to figure out the names of the variables in positions 1-4, then put that into a macro variable.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Do you want to include the character variable or exclude it?

If you want to exclude it, you can do:

`where n(of var1 - numeric - zvariable) ;`

That will only include the numeric variables in the range.

If you want to include the character variable, you can switch from the N function to the CMISS function, which can handle both numeric and character variables:

`where cmiss(of var1 -- zvariable) < 5 ; *assuming there are 5 variables in this range;`

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

What do you mean "not working"? Do you get an error, or the wrong result?

Please try making a little example like what @PeterClemmensen posted where there is a data step for making a HAVE dataset with a few records, and then a WANT step using CMISS. If you get errors in the log, please post the log as well. CMISS should work with both numeric and character variables. The documentation for CMISS is: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p1tth4ltf640din1ey86ubo2lky2.h...

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

This is the code:

```
data want;
set have;
where cmiss(var1 -- zvariable) <5;
run;
```

Where zvaribale is a character variable. I get this error in the log:

ERROR: WHERE clause operator requires numeric values.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

When you use the -- or - list as an argument to a function, you need to use the word OF at the beginning. Try:

```
data want;
set have;
where cmiss(OF var1 -- zvariable) < 5 ;
run;
```

If you don't write OF, SAS will do subtraction:

var1 - (-zvariable)

The error you're getting is because SAS is trying to do subtraction with a character variable.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks for your reply. I added the word OF as advised and I am getting this error in the log:

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

My apologies. It looks like you can't use a -- in a WHERE statement. Which I guess makes sense, because it relies on the order of variables in the PDV.

It should work if you change to a subsetting IF, e.g.:

```
12 data want;
13 set have ;
14 if cmiss(OF var1 -- zvariable) < 5 ; *IF not where;
15 run;
NOTE: There were 1 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 1 observations and 3 variables.
```

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

It worked! Thank you so much! This is extremely helpful.

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.