Blogs

Blogs

SAS author's tip: Some useful WHERE operators

7
By Topics | Learn SAS

Learning SAS by ExampleThis week's SAS tip is from Ron Cody and his very popular book Learning SAS by Example: A Programmer's Guide. If you haven't yet discovered Ron Cody's work, you're missing out. Fortunately, you can learn a lot more about Ron and his many books--as well as view bonus content here.

The following excerpt is from SAS Press author Ron Cody and his book "Learning SAS by Example: A Programmer's Guide" Copyright © 2007, SAS Institute Inc., Cary, North Carolina, USA. ALL RIGHTS RESERVED. (please note that results may vary depending on your version of SAS software)

Some Useful WHERE Operators

The table here lists some of the useful operators that you can use with a WHERE statement.

Note: When using the LIKE operator, the underscore character takes the place of a single character, while the percent sign can be substituted for a string of any length (including a null string).

Here are some examples.

Notes:
1. The IS NULL or IS MISSING expression matches a character or a numeric missing value.
2. The BETWEEN AND expression matches all the values greater than or equal to the first value and less than or equal to the second value. This works with character as well as numeric variables.
3. The CONTAINS expression matches any character value containing the given string.
4. The LIKE expression uses two wildcard operators. The underscore (_) is a place holder; enter as many underscores as you need to stand for the same number of characters. The percent (%) matches nothing or a string of any length.

Tags Learning SAS by Example ron cody sas author's tip sas press sas tip
Share

About Author

Shelly Goodin

Social Media Specialist, SAS Publications

Shelly Goodin is SAS Publications' social media marketer and the editor of "SAS Publishing News". She’s worked in the publishing industry for over thirteen years, including seven years at SAS, and enjoys creating opportunities for fans of SAS and JMP software to get to know SAS Publications' many offerings and authors.

7 Comments

  1. Richard Erickson on

    Regarding Shelly's comment, "If you haven't discovered Ron Cody's _Learning SAS by Example_ you're missing out." ABSOLUTELY! I ran across this book some time ago and I think it's a great resource.

  2. William White on

    Note that the examples with the contains and like operators will not run as shown. The character substrings, including the wildcard characters, must have quotes around them:
    where Name contains 'mac';
    where Name like 'R_n%';

  6. Mike Zdeb on

    Hi. Just like to add one thing about waht Ron refers to as "phonetic matching", the =* operator.

    That operator is referred to in SAS doc as "sounds-like". It actually uses the SOUNDEX function to compare to compare (in the examples) above the SOUNDEX of the variable NAME to the string "Nick".

    The SOUNDEX of "Nick" is "N2" ... so any name the produces the same SOUNDEX (for example Nick, Nack, Nikki) is a match. You can look up the rules for SOUNDEX in SAS doc. That will explain why this string "NAEHIOUWYCGJKQSXZAEHIOUWY" also matches the name "Nick".

    • Ron Cody on

      As usual, Mike is correct--the =* operator does use the SOUNDEX function. I described it as a phonetic match because it is sometimes thought of in that way. Thanks for all the comments. I don't have much (read that any) experience with blogs so thanks to Shelly, I may subscribe to this one and see how it goes.

Back to Top