Providing software solutions since 1976
Sample 25039: Manipulating Data with PROC SQL
 |  |  |  |
PROC SQL users have a number of ways available to accomplish their objectives, particularly when the goal is to manipulate data. For example, the SELECT statement is an extremely powerful statement in the SQL language. Its syntax can be somewhat complex because of the many ways that columns, tables, operators, and functions can be combined to form executable statements. The SELECT statement is used in the examples included in this tip.
Here are the types of operators and functions in PROC SQL:
- comparison operators
- logical operators
- arithmetic operators
- character string operators
- summary functions
Character String Operators and Functions
Usually, character string operators and functions are used with character data. Numerous operators are presented to acquaint users with the power that's available when using PROC SQL. These operators enable you to control character alignment and string concatenation.
Character Alignment and Concatenation
The default alignment for character data is to the left. However, character columns or expressions can also be aligned to the right. Two functions in PROC SQL 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, and inserts blank spaces and a dash (-) between two character columns to create the subset "PG-rated" from the table MOVIES.
|
The PROC SQL code:
PROC SQL;
SELECT TRIM(LEFT(title) || " - " || category) AS Concatenation_Alignment
FROM movies
WHERE rating = "PG";
QUIT;
The results: Concatenation_Alignment ____________________________________________________ Casablanca - Drama Jaws - Action Adventure Poltergeist - Horror Rocky - Action Adventure Star Wars - Action Sci-Fi The Hunt for Red October - Action Adventure |
Phonetic Matching (Sounds-Like Operator, =* )
A technique for finding names that sound alike or names that have spelling variations is available in PROC SQL. Although not technically a function, the sounds-like operator =* searches and selects character data based on two expressions: the search value and the matched value. Anyone that has looked for a last name in a telephone directory is quickly reminded of the possible phonetic variations. To show how the sounds-like operator works, we will search on the column TITLE in the MOVIES table, using the string "Rucky", for all phonetic variations that are related to the movie title "Rocky".
|
The PROC SQL code:
PROC SQL;
SELECT title, rating, category
FROM movies
WHERE title =* "Rucky";
QUIT;
The results: Title Rating Category ____________________________________________________________ Rocky PG Action Adventure |
Finding Patterns in a String (Pattern Matching, % and _ )
Constructing specific search patterns in string expressions is a simple process with the LIKE predicate. The percent sign (%) acts as a wildcard character that represents multiple characters, including any combination of uppercase or lowercase characters. Combining the LIKE predicate with the percent sign (%) permits case-sensitive searches. It's a popular technique used by savvy SQL programmers to find patterns in their data. The next example finds patterns in the CATEGORY column in the MOVIE table that contains the uppercase character D in the first position, followed by any number of characters.
|
The PROC SQL code:
PROC SQL;
SELECT title, rating, category
FROM movies
WHERE category LIKE 'D%';
QUIT;
The 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 |
About the Author
If you would like more information or have any questions about this tip, you can contact Kirk Paul Lafler, Software Intelligence Corporation at KirkLafler@cs.com. Kirk's new book, Power PROC SQL, published by SAS will be available in late September 2004 to provide a comprehensive collection of PROC SQL coding examples for intermediate and advanced SAS users. Also look for Power SAS: A Survival Guide, published by Apress to provide SAS users with the largest and most comprehensive collection of SAS tips and techniques ever offered. Power SAS can be purchased on Amazon.com and BarnesandNoble.com.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL Non SAS Authors ==> Kirk Paul Lafler |
| Date Modified: | 2005-03-24 03:02:17 |
| Date Created: | 2005-01-26 15:52:44 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |
