SAS 9.4 SQL Procedure User's Guide, Second Edition, 2nd Edition

SAS 9.4 SQL Procedure User's Guide, Second Edition, 2nd Edition

by SAS Institute
Released July 2015
Publisher(s): SAS Institute
ISBN: 9781629598932

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Describes the basics of using the SQL procedure and provides comprehensive reference information. The usage information includes retrieving data from single and multiple tables; selecting specific data from tables; subsetting, ordering, and summarizing data; updating tables; combining tables to create new tables and useful reports; performing queries on database management system (DBMS) tables; using PROC SQL with the SAS macro facility; and debugging and optimizing PROC SQL code. The reference information includes statements, dictionary components, and system options.

Table of contents

  1. Contents
  2. About This Book
    1. Syntax Conventions for the SAS Language
      1. Overview of Syntax Conventions for the SAS Language
      2. Syntax Components
      3. Style Conventions
      4. Special Characters
      5. References to SAS Libraries and External Files
  3. What's New in the SAS 9.4 SQL Procedure
    1. Overview
    2. Additional SAS SQL System Option and PROC SQL Statement Options
    3. Support Added for Linguistic Collation
    4. Support Added for Extended Attributes in DICTIONARY Tables
    5. Replaced BUFFERSIZE PROC SQL Statement Option
  4. Using the SQL Procedure
    1. Introduction to the SQL Procedure
      1. What Is SQL?
      2. What Is the SQL Procedure?
      3. Terminology
        1. Tables
        2. Queries
        3. Views
        4. Null Values
      4. Comparing PROC SQL with the SAS DATA Step
      5. Notes about the Example Tables (1/3)
      6. Notes about the Example Tables (2/3)
      7. Notes about the Example Tables (3/3)
    2. Retrieving Data from a Single Table
      1. Overview of the SELECT Statement
        1. How to Use the SELECT Statement
        2. SELECT and FROM Clauses
        3. WHERE Clause
        4. ORDER BY Clause
        5. GROUP BY Clause
        6. HAVING Clause
        7. Ordering the SELECT Statement
      2. Selecting Columns in a Table
        1. Selecting All Columns in a Table
        2. Selecting Specific Columns in a Table
        3. Eliminating Duplicate Rows from the Query Results
        4. Determining the Structure of a Table
      3. Creating New Columns (1/2)
      4. Creating New Columns (2/2)
        1. Adding Text to Output
        2. Calculating Values
        3. Assigning a Column Alias
        4. Referring to a Calculated Column by Alias
        5. Assigning Values Conditionally
        6. Replacing Missing Values
        7. Specifying Column Attributes
      5. Sorting Data (1/2)
      6. Sorting Data (2/2)
        1. Overview of Sorting Data
        2. Sorting by Column
        3. Sorting by Multiple Columns
        4. Specifying a Sort Order
        5. Sorting by Calculated Column
        6. Sorting by Column Position
        7. Sorting by Columns That Are Not Selected
        8. Specifying a Different Sorting Sequence
        9. Sorting Columns That Contain Missing Values
      7. Retrieving Rows That Satisfy a Condition (1/3)
      8. Retrieving Rows That Satisfy a Condition (2/3)
      9. Retrieving Rows That Satisfy a Condition (3/3)
        1. Using a Simple WHERE Clause
        2. Retrieving Rows Based on a Comparison
        3. Retrieving Rows That Satisfy Multiple Conditions
        4. Using Other Conditional Operators
        5. Using Truncated String Comparison Operators
        6. Using a WHERE Clause with Missing Values
      10. Summarizing Data (1/2)
      11. Summarizing Data (2/2)
        1. Overview of Summarizing Data
        2. Using Aggregate Functions
        3. Summarizing Data with a WHERE Clause
        4. Displaying Sums
        5. Combining Data from Multiple Rows into a Single Row
        6. Remerging Summary Statistics
        7. Using Aggregate Functions with Unique Values
        8. Summarizing Data with Missing Values
      12. Grouping Data
        1. Grouping by One Column
        2. Grouping without Summarizing
        3. Grouping by Multiple Columns
        4. Grouping and Sorting Data
        5. Grouping with Missing Values
      13. Filtering Grouped Data
        1. Overview of Filtering Grouped Data
        2. Using a Simple HAVING Clause
        3. Choosing between HAVING and WHERE
        4. Using HAVING with Aggregate Functions
      14. Validating a Query
    3. Retrieving Data from Multiple Tables
      1. Introduction
      2. Selecting Data from More Than One Table By Using Joins (1/5)
      3. Selecting Data from More Than One Table By Using Joins (2/5)
      4. Selecting Data from More Than One Table By Using Joins (3/5)
      5. Selecting Data from More Than One Table By Using Joins (4/5)
      6. Selecting Data from More Than One Table By Using Joins (5/5)
        1. Overview of Selecting Data from More Than One Table By Using Joins
        2. Inner Joins
        3. Outer Joins
        4. Specialty Joins
        5. Using the Coalesce Function in Joins
        6. Comparing DATA Step Match-Merges with PROC SQL Joins
      7. Using Subqueries to Select Data (1/2)
      8. Using Subqueries to Select Data (2/2)
        1. Single-Value Subqueries
        2. Multiple-Value Subqueries
        3. Correlated Subqueries
        4. Testing for the Existence of a Group of Values
        5. Multiple Levels of Subquery Nesting
        6. Combining a Join with a Subquery
      9. When to Use Joins and Subqueries
      10. Combining Queries with Set Operators (1/2)
      11. Combining Queries with Set Operators (2/2)
        1. Working with Two or More Query Results
        2. Producing Unique Rows from Both Queries (UNION)
        3. Producing Rows That Are in Only the First Query Result (EXCEPT)
        4. Producing Rows That Belong to Both Query Results (INTERSECT)
        5. Concatenating Query Results (OUTER UNION)
        6. Producing Rows from the First Query or the Second Query
    4. Creating and Updating Tables and Views
      1. Introduction
      2. Creating Tables
        1. Creating Tables from Column Definitions
        2. Creating Tables from a Query Result
        3. Creating Tables like an Existing Table
        4. Copying an Existing Table
        5. Using Data Set Options
      3. Inserting Rows into Tables
        1. Inserting Rows with the SET Clause
        2. Inserting Rows with the VALUES Clause
        3. Inserting Rows with a Query
      4. Updating Data Values in a Table
        1. Updating All Rows in a Column with the Same Expression
        2. Updating Rows in a Column with Different Expressions
        3. Handling Update Errors
      5. Deleting Rows
      6. Altering Columns
        1. Adding a Column
        2. Modifying a Column
        3. Deleting a Column
      7. Creating an Index
        1. Using PROC SQL to Create Indexes
        2. Tips for Creating Indexes
        3. Deleting Indexes
      8. Deleting a Table
      9. Using SQL Procedure Tables in SAS Software
      10. Creating and Using Integrity Constraints in a Table
      11. Creating and Using PROC SQL Views (1/2)
      12. Creating and Using PROC SQL Views (2/2)
        1. Overview of Creating and Using PROC SQL Views
        2. Creating Views
        3. Describing a View
        4. Updating a View
        5. Embedding a LIBNAME in a View
        6. Deleting a View
        7. Specifying In-Line Views
        8. Tips for Using SQL Procedure Views
        9. Using SQL Procedure Views in SAS Software
    5. Programming with the SQL Procedure
      1. Introduction
      2. Using PROC SQL Options to Create and Debug Queries
        1. Overview of Using PROC SQL Options to Create and Debug Queries
        2. Restricting Row Processing with the INOBS= and OUTOBS= Options
        3. Limiting Iterations with the LOOPS= Option
        4. Checking Syntax with the NOEXEC Option and the VALIDATE Statement
        5. Expanding SELECT * with the FEEDBACK Option
        6. Timing PROC SQL with the STIMER Option
        7. Resetting PROC SQL Options with the RESET Statement
      3. Improving Query Performance
        1. Overview of Improving Query Performance
        2. Using Indexes to Improve Performance
        3. Using the Keyword ALL in Set Operations
        4. Omitting the ORDER BY Clause When Creating Tables and Views
        5. Using In-Line Views versus Temporary Tables
        6. Comparing Subqueries with Joins
        7. Using WHERE Expressions with Joins
        8. Optimizing the PUT Function
        9. Replacing References to the DATE, TIME, DATETIME, and TODAY Functions
        10. Disabling the Remerging of Data When Using Summary Functions
      4. Using Column Aliases
        1. Overview of Column Aliases
        2. Column Alias Extensions
        3. Using the CALCULATED Keyword with Column Aliases
      5. Accessing SAS System Information By Using DICTIONARY Tables (1/2)
      6. Accessing SAS System Information By Using DICTIONARY Tables (2/2)
        1. What Are Dictionary Tables?
        2. Retrieving Information about DICTIONARY Tables and Sashelp Views
        3. Using DICTIONARY.Tables
        4. Using DICTIONARY.Columns
        5. DICTIONARY Tables and Performance
      7. Using SAS Data Set Options with PROC SQL
      8. Using PROC SQL with the SAS Macro Facility (1/2)
      9. Using PROC SQL with the SAS Macro Facility (2/2)
        1. Overview of Using PROC SQL with the SAS Macro Facility
        2. Creating Macro Variables in PROC SQL
        3. Concatenating Values in Macro Variables
        4. Defining Macros to Create Tables
        5. Using the PROC SQL Automatic Macro Variables
      10. Formatting PROC SQL Output By Using the REPORT Procedure
      11. Accessing a DBMS with SAS/ACCESS Software (1/2)
      12. Accessing a DBMS with SAS/ACCESS Software (2/2)
        1. Overview of Accessing a DBMS with SAS/ACCESS Software
        2. Connecting to a DBMS By Using the LIBNAME Statement
        3. Connecting to a DBMS By Using the SQL Procedure Pass-Through Facility
        4. Updating PROC SQL and SAS/ACCESS Views
      13. Using the Output Delivery System with PROC SQL
    6. Practical Problem-Solving with PROC SQL
      1. Overview
      2. Computing a Weighted Average
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      3. Comparing Tables
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      4. Overlaying Missing Data Values
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      5. Computing Percentages within Subtotals
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      6. Counting Duplicate Rows in a Table
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      7. Expanding Hierarchical Data in a Table
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      8. Summarizing Data in Multiple Columns
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      9. Creating a Summary Report
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      10. Creating a Customized Sort Order
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      11. Conditionally Updating a Table
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      12. Updating a Table with Values from Another Table
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      13. Creating and Using Macro Variables
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
      14. Using PROC SQL Tables in Other SAS Procedures
        1. Problem
        2. Background Information
        3. Solution
        4. How It Works
  5. SQL Procedure Reference
    1. SQL Procedure
      1. Overview
        1. What Is the SQL Procedure?
        2. What Are PROC SQL Tables?
        3. What Are Views?
        4. SQL Procedure Coding Conventions
        5. Threaded Processing Using PROC SQL
      2. Syntax: SQL Procedure (1/11)
      3. Syntax: SQL Procedure (2/11)
      4. Syntax: SQL Procedure (3/11)
      5. Syntax: SQL Procedure (4/11)
      6. Syntax: SQL Procedure (5/11)
      7. Syntax: SQL Procedure (6/11)
      8. Syntax: SQL Procedure (7/11)
      9. Syntax: SQL Procedure (8/11)
      10. Syntax: SQL Procedure (9/11)
      11. Syntax: SQL Procedure (10/11)
      12. Syntax: SQL Procedure (11/11)
        1. PROC SQL Statement
        2. ALTER TABLE Statement
        3. CONNECT Statement
        4. CREATE INDEX Statement
        5. CREATE TABLE Statement
        6. CREATE VIEW Statement
        7. DELETE Statement
        8. DESCRIBE Statement
        9. DISCONNECT Statement
        10. DROP Statement
        11. EXECUTE Statement
        12. INSERT Statement
        13. RESET Statement
        14. SELECT Statement
        15. SELECT Clause
        16. INTO Clause
        17. FROM Clause
        18. WHERE Clause
        19. GROUP BY Clause
        20. HAVING Clause
        21. ORDER BY Clause
        22. UPDATE Statement
        23. VALIDATE Statement
      13. Examples: SQL Procedure (1/10)
      14. Examples: SQL Procedure (2/10)
      15. Examples: SQL Procedure (3/10)
      16. Examples: SQL Procedure (4/10)
      17. Examples: SQL Procedure (5/10)
      18. Examples: SQL Procedure (6/10)
      19. Examples: SQL Procedure (7/10)
      20. Examples: SQL Procedure (8/10)
      21. Examples: SQL Procedure (9/10)
      22. Examples: SQL Procedure (10/10)
        1. Creating a Table and Inserting Data into It
        2. Creating a Table from a Query's Result
        3. Updating Data in a PROC SQL Table
        4. Joining Two Tables
        5. Combining Two Tables
        6. Reporting from DICTIONARY Tables
        7. Performing an Outer Join
        8. Creating a View from a Query’s Result
        9. Joining Three Tables
        10. Querying an In-Line View
        11. Retrieving Values with the SOUNDS-LIKE Operator
        12. Joining Two Tables and Calculating a New Value
        13. Producing All the Possible Combinations of the Values in a Column
        14. Matching Case Rows and Control Rows
        15. Counting Missing Values with a SAS Macro
    2. SQL Procedure Components
      1. Overview
      2. Dictionary (1/11)
      3. Dictionary (2/11)
      4. Dictionary (3/11)
      5. Dictionary (4/11)
      6. Dictionary (5/11)
      7. Dictionary (6/11)
      8. Dictionary (7/11)
      9. Dictionary (8/11)
      10. Dictionary (9/11)
      11. Dictionary (10/11)
      12. Dictionary (11/11)
        1. BETWEEN Condition
        2. BTRIM Function
        3. CALCULATED
        4. CASE Expression
        5. COALESCE Function
        6. column-definition
        7. column-modifier
        8. column-name
        9. CONNECTION TO
        10. CONTAINS Condition
        11. EXISTS Condition
        12. IN Condition
        13. IS Condition
        14. joined-table
        15. LIKE Condition
        16. LOWER Function
        17. query-expression
        18. sql-expression
        19. SUBSTRING Function
        20. summary-function
        21. table-expression
        22. UPPER Function
  6. Appendixes
    1. SQL Macro Variables and System Options
      1. Dictionary
        1. SQLCONSTDATETIME System Option
        2. SQLGENERATION= System Option
        3. SQLIPONEATTEMPT System Option
        4. SQLMAPPUTTO= System Option
        5. SQLREDUCEPUT= System Option
        6. SQLREDUCEPUTOBS= System Option
        7. SQLREDUCEPUTVALUES= System Option
        8. SQLREMERGE System Option
        9. SQLUNDOPOLICY= System Option
        10. SYS_SQLSETLIMIT Macro Variable
    2. PROC SQL and the ANSI Standard
    3. Example Code Shown in Using the SQL Procedure (1/10)
    4. Example Code Shown in Using the SQL Procedure (2/10)
    5. Example Code Shown in Using the SQL Procedure (3/10)
    6. Example Code Shown in Using the SQL Procedure (4/10)
    7. Example Code Shown in Using the SQL Procedure (5/10)
    8. Example Code Shown in Using the SQL Procedure (6/10)
    9. Example Code Shown in Using the SQL Procedure (7/10)
    10. Example Code Shown in Using the SQL Procedure (8/10)
    11. Example Code Shown in Using the SQL Procedure (9/10)
    12. Example Code Shown in Using the SQL Procedure (10/10)
    13. Data Sets for Examples in SQL Procedure Reference
      1. Overview
      2. Employees
      3. Houses
      4. Match_11
      5. Proclib.Delay
      6. Proclib.Houses
      7. Proclib.March
      8. Proclib.Paylist2
      9. Proclib.Payroll
      10. Proclib.Payroll2
      11. Proclib.Schedule2
      12. Proclib.Staff
      13. Proclib.Staff2
      14. Proclib.Superv2
      15. Stores
      16. Survey
  7. Recommended Reading
  8. Glossary
  9. Index (1/3)
  10. Index (2/3)
  11. Index (3/3)

Product information

  • Title: SAS 9.4 SQL Procedure User's Guide, Second Edition, 2nd Edition
  • Author(s): SAS Institute
  • Release date: July 2015
  • Publisher(s): SAS Institute
  • ISBN: 9781629598932