PROC SQL OPERATORS AND FUNCTIONS
--KirkLafler 17:50, 24 April 2007 (EDT)
By Kirk Paul Lafler, Software Intelligence Corporation
PROC SQL users have a number of ways to accomplish their objectives, particularly when the goal is to manipulate data. The SELECT statement is an extremely powerful statement in the SQL language. Its syntax can be somewhat complex because of the number of ways that columns, tables, operators, and functions can be combined into executable statements. There are several types of operators and functions in PROC SQL:
1) comparison operators,
2) logical operators,
3) arithmetic operators,
4) character string operators,
5) summary functions.
This tip will illustrate concatenation, character alignment, and phonetic and pattern matching character-string operations in manipulating data.
Character String Operators and Functions Character string operators and functions are typically used with character data. Numerous operators are presented to acquaint users to the power available with the SQL procedure. You’ll see a number of operators including string concatenation and character alignment.
Concatenation and Character Alignment The default alignment for character data is to the left, however character columns or expressions can also be aligned to the right. Two functions are available for character alignment: LEFT and RIGHT. The next example combines the concatenation operator “||” and the TRIM function with the LEFT function to left align a character expression while inserting blank spaces and a dash “-” between two character columns to subset “PG-rated” movies.
PROC SQL Code
PROC SQL; SELECT LEFT(TRIM(title) || “ - ” || category) AS Concatenation_Alignment FROM movies WHERE rating = “PG”; QUIT;
Concatenation_Alignment Casablanca - Drama Jaws - Action Adventure Poltergeist - Horror Rocky - Action Adventure Star Wars - Action Sci-Fi The Hunt for Red October - Action Adventure
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
P.O. Box 1390
Spring Valley, California 91979-1390
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.