This 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.
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.
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.
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%';
Thanks! I find myself searching for where operators all the time.
Nice summary table of WHERE operators!
Thanks for the tips. I never knew of the CONTAINS function. I will keep it in mind.
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".
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.