Finding Patterns in a String (Pattern Matching % )

From sasCommunity

--KirkLafler 18:08, 24 April 2007 (EDT)
By Kirk Paul Lafler, Software Intelligence Corporation

Constructing specific search patterns in string expressions is a simple process with the LIKE predicate. The % acts as a wildcard character representing any number of characters, including any combination of upper or lower case characters. Combining the LIKE predicate with the % (percent sign) permits case-sensitive searches and is a popular technique used by savvy SQL programmers to find patterns in their data. The next example finds patterns in the movie category containing the uppercase character ‘D’ in the first position followed by any number of characters in the CATEGORY column.

PROC SQL Code

PROC SQL;
 SELECT title, rating, category
  FROM movies
   WHERE category LIKE ‘D%’;
QUIT;

Results

    Title                           Rating  Category
    Casablanca                      PG      Drama
    Forrest Gump                    PG-13   Drama
    Michael                         PG-13   Drama
    Dressed to Kill                 R       Drama Mysteries
    Ghost                           PG-13   Drama Romance
    Titanic                         PG-13   Drama Romance
    Silence of the Lambs            R       Drama Suspense

---

[edit] Contact Information

If you would like more information or have any questions about this tip, please contact: Kirk Paul Lafler, Software Intelligence Corporation at KirkLafler@cs.com. Kirk has been working with the SAS System since 1979 and is a SAS Certified Professional®. His company provides custom SAS programming, application design and development, consulting services, and hands-on SAS training to clients around the world. Kirk is the author of four books including PROC SQL: Beyond the Basics Using SAS by SAS Institute, and more than two hundred peer-reviewed articles and papers that have appeared in professional journals and SAS User Group proceedings. Kirk can be reached at:

                                       Kirk Paul Lafler

                                       Software Intelligence Corporation

                                       World Headquarters

                                       P.O. Box 1390

                                       Spring Valley, California 91979-1390

                                       E-mail: KirkLafler@cs.com

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.