PROC SQL, 3rd Edition

Book Description

PROC SQL: Beyond the Basics Using SAS®, Third Edition, is a step-by-step, example-driven guide that helps readers master the language of PROC SQL. Packed with analysis and examples illustrating an assortment of PROC SQL options, statements, and clauses, this book not only covers all the basics, but it also offers extensive guidance on complex topics such as set operators and correlated subqueries. Programmers at all levels will appreciate Kirk Lafler’s easy-to-follow examples, clear explanations, and handy tips to extend their knowledge of PROC SQL.

This third edition explores new and powerful features in SAS® 9.4, including topics such as:

  • IFC and IFN functions
  • nearest neighbor processing
  • the HAVING clause
  • indexes

It also features two completely new chapters on fuzzy matching and data-driven programming. Delving into the workings of PROC SQL with greater analysis and discussion, PROC SQL: Beyond the Basics Using SAS®, Third Edition, explores this powerful database language using discussion and numerous real-world examples.

Table of Contents

  1. About This Book
  2. Chapter 1: Designing Database Tables
    1. Introduction
    2. Database Design
    3. Column Names and Reserved Words
    4. Data Integrity
    5. Database Tables Used in This Book
    6. Table Contents
    7. Summary
  3. Chapter 2: Working with Data in PROC SQL
    1. Introduction
    2. The SELECT Statement and Clauses
    3. Overview of Data Types
    4. SQL Operators, Functions, and Keywords
    5. Dictionary Tables
    6. Summary
  4. Chapter 3: Formatting Output
    1. Introduction
    2. Formatting Output
    3. Formatting Output with the Output Delivery System
    4. Summary
  5. Chapter 4: Coding PROC SQL Logic
    1. Introduction
    2. Conditional Logic
    3. CASE Expressions
    4. Interfacing PROC SQL with the Macro Language
    5. Summary
  6. Chapter 5: Creating, Populating, and Deleting Tables
    1. Introduction
    2. Creating Tables
    3. Populating Tables
    4. Integrity Constraints
    5. Deleting Rows in a Table
    6. Deleting Tables
    7. Summary
  7. Chapter 6: Modifying and Updating Tables and Indexes
    1. Introduction
    2. Modifying Tables
    3. Indexes
    4. Updating Data in a Table
    5. Summary
  8. Chapter 7: Coding Complex Queries
    1. Introduction
    2. Introducing Complex Queries
    3. Joins
    4. Why Joins Are Important
    5. Cartesian Product Joins
    6. Inner Joins
    7. Outer Joins
    8. Subqueries
    9. Set Operations
    10. Data Structure Transformations
    11. Complex Query Applications
    12. Summary
  9. Chapter 8: Working with Views
    1. Introduction
    2. Views—Windows to Your Data
    3. Eliminating Redundancy
    4. Restricting Data Access—Security
    5. Hiding Logic Complexities
    6. Nesting Views
    7. Updatable Views
    8. Deleting Views
    9. Summary
  10. Chapter 9:  Fuzzy Matching Programming
    1. Introduction
    2. Data Sets Used in Examples
    3. 6-Step Fuzzy Matching Process
    4. Summary
  11. Chapter 10: Data-driven Programming
    1. Introduction
    2. Programming Paradigms
    3. SAS Metadata Sources
    4. DICTIONARY Tables
    5. CALL EXECUTE Routine
    6. Custom-defined Formats
    7. Macro Language
    8. Summary
  12. Chapter 11: Troubleshooting and Debugging
    1. Introduction
    2. The World of Bugs
    3. The Debugging Process
    4. Types of Problems
    5. Troubleshooting and Debugging Techniques
    6. Undocumented PROC SQL Options
    7. Summary
  13. Chapter 12: Tuning for Performance and Efficiency
    1. Introduction
    2. Understanding Performance Tuning
    3. Sorting and Performance
    4. User-Specified Sorting (SORTPGM=System Options)
    5. Grouping and Performance
    6. Splitting Tables
    7. Indexes and Performance
    8. Reviewing CONTENTS Output and System Messages
    9. Optimizing WHERE Clause Processing with Indexes
    10. Summary
  14. References

Product Information

  • Title: PROC SQL, 3rd Edition
  • Author(s): Kirk Paul Lafler
  • Release date: March 2019
  • Publisher(s): SAS Institute
  • ISBN: 9781635266818