Overview Section
PROC SQL is a powerful tool for data manipulation and querying. It can perform many functions such as the conventional DATA and PROC steps but with fewer statements and computer resources. In this lesson, we will investigate how to select, subset, sort, summarize, and group data with SQL procedure.
Objectives
Upon completion of this lesson, you should be able to:
- Know about the basic syntax of PROC SQL and its uniqueness
- Use SELECT and FROM clauses to query a single table
- Display the result of a query in a predefined ODS destination
- Generate and name the new table with the CREATE TABLE statement
- Use the shortcut (*) to select all rows in a table
- Create new columns with the SELECT clause
- Use options in the SELECT statement to change variables’ format, label, and length
- Use the OUTOBS= option in the PROC SQL statement to limit the number of rows in the display
- Use the CASE operator inside the SELECT clause to conditionally process data
- Use the WHERE clause and its operators to subset a table
- Insert the keyword CALCULATED inside the WHERE clause to subset a table based on the calculated results
- Use the ORDER BY clause to sort data in ascending order by default
- Use its location number in the SELECT clause to reference a column
- Add the keyword DESC to sort data descendingly
- Summarize data with aggregation functions provided in PROC SQL
- Use multiple columns as arguments inside a summary function
- Select the column(s) besides a summary function with no GROUP BY clause
- Use GROUP BY clause with summary functions to summarize groups of data
- Use the COUNT() function to count non-missing values of rows of a table, a column, or non-duplicate values
- Use the HAVING clause to subset data
- Understand the differences between the HAVING and WHERE clause
- Query multiple tables by listing multiple tables in the FROM clause
- Understand a Cartesian product in SAS SQL
- Use the WHERE clause to complete the inner join