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

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Re: Do we have a "where NOT in" statement?

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** and **locked**.
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 07-23-2009 02:31 PM
(140910 views)

I know this is a very simple question,

We have a " where in (1,2,3,....)" statement

do we have "where not in (1,2,3...)?" or a similar statement?

Thanks!

We have a " where in (1,2,3,....)" statement

do we have "where not in (1,2,3...)?" or a similar statement?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

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

yes - just use it as you've written it (almost).

`where varname not in (1,2,3);`

Dare to experiment - error messages are free!

*Editor's note: consolidating some other helpful comments in this solution...*

*@bhthomas99* asks about the NOTIN operator (no space). @abbess replied:

The

NOTINoperator work perfectly, I have tried it my self. Also it is mentioned in the SAS Support thatNOTis a prefix wich can be used with other operators.

"

Aprefix operatoris an operator that is applied to the variable, constant, function, or parenthetic expression that immediately follows it. The plus sign (+) and minus sign (-) can be used as prefix operators. The word NOT and its equivalent symbols are also prefix operators"

See SAS Operators in Expressions (SAS documentation)

And @Doc_Duke points out that order-of-operations matters:

Be careful combining NOT, AND, and OR; you don't always get what you think, especially if you leave it to SAS to resolve the expression. For instance,

not varname IN (1, 2, 3)

does not give the same answer as

not (varname in (1,2,3)

7 REPLIES 7

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

yes - just use it as you've written it (almost).

`where varname not in (1,2,3);`

Dare to experiment - error messages are free!

*Editor's note: consolidating some other helpful comments in this solution...*

*@bhthomas99* asks about the NOTIN operator (no space). @abbess replied:

The

NOTINoperator work perfectly, I have tried it my self. Also it is mentioned in the SAS Support thatNOTis a prefix wich can be used with other operators.

"

Aprefix operatoris an operator that is applied to the variable, constant, function, or parenthetic expression that immediately follows it. The plus sign (+) and minus sign (-) can be used as prefix operators. The word NOT and its equivalent symbols are also prefix operators"

See SAS Operators in Expressions (SAS documentation)

And @Doc_Duke points out that order-of-operations matters:

Be careful combining NOT, AND, and OR; you don't always get what you think, especially if you leave it to SAS to resolve the expression. For instance,

not varname IN (1, 2, 3)

does not give the same answer as

not (varname in (1,2,3)

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

Bill wrote:

>Dare to experiment - error messages are free!

Thanks for the chuckle - my log is full of Red today.

>Dare to experiment - error messages are free!

Thanks for the chuckle - my log is full of Red today.

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

More completely, you can put a "not" infix operator in nearly every logical expression, and often outside, e.g.

where not (varname IN (1,2,3));

is a valid statement.

Be careful combining NOT, AND, and OR; you don't always get what you think, especially if you leave it to SAS to resolve the expression. For instance,

not varname IN (1, 2, 3)

does not give the same answer as

not (varname in (1,2,3)

where not (varname IN (1,2,3));

is a valid statement.

Be careful combining NOT, AND, and OR; you don't always get what you think, especially if you leave it to SAS to resolve the expression. For instance,

not varname IN (1, 2, 3)

does not give the same answer as

not (varname in (1,2,3)

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

Thanks for this Doc. I always use NOT before IN, and it never occured to me that

not varname IN (1, 2, 3)

reads as

(not varname) IN (1, 2, 3) which is true is var = 0

I'll keep this is mind, and add this comment so more people have a chance to read the thread.

not varname IN (1, 2, 3)

reads as

(not varname) IN (1, 2, 3) which is true is var = 0

I'll keep this is mind, and add this comment so more people have a chance to read the thread.

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

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

The **NOTIN** operator work perfectly, I have tried it my self. Also it is mentionned in the SAS Support that **NOT **is a prefix wich can be used with other operators.

"*A **prefix operator** is an operator that is applied to the variable, constant, function, or parenthetic expression that immediately follows it. The plus sign (+) and minus sign (-) can be used as prefix operators. The word NOT and its equivalent symbols are also prefix operators*"

Source: http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000780367.htm

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

Although you have a couple of already accepted solutions, I wanted to throw my two cents in as a PROC SQL guy. I use the NOTIN frequently, especially when querying text:

All my data:

```
proc sql;
select product, count(*)
from sashelp.shoes
group by product
order by product
;
quit;
```

Results:

Excluding BOOT and SANDAL:

```
proc sql;
select product, count(*)
from sashelp.shoes
where product notin ('Boot','Sandal')
group by product
order by product
;
quit;
```

Results:

Good luck!

Chris

Has my article or post helped? Please mark as Solution or Like the article!

**Don't miss out on SAS Innovate - Register now for the FREE Livestream!**

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

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.