You are previewing PROC SQL: Beyond the Basics Using SAS, Second Edition.
O'Reilly logo
PROC SQL: Beyond the Basics Using SAS, Second Edition

Book Description

Kirk Lafler's PROC SQL: Beyond the Basics Using SAS, Second Edition, offers 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 can be approached in a number of ways. Users can read it cover-to-cover or selectively by chapter; they can use the extensive index to find content of interest or refer to the helpful "Summary" that precede each chapter to look for help on a specific topic. The second edition explores new and powerful features in SAS 9.3, and includes such topics as adding data to a table with a SET clause; bulk loading data from Microsoft Excel; distinguishing between DATA step merges and PROC SQL joins; rules for designing indexes; cardinality and index selectivity; and demystifying join algorithms. It also features an expanded discussion of CASE expressions, and new sections on complex query applications, and grouping and performance. Delving into the workings of PROC SQL with greater analysis and discussion, PROC SQL: Beyond the Basic Using SAS, Second Edition, examines a broad range of topics and provides greater detail about this powerful database language using discussion and numerous real-world examples. This book is part of the SAS Press program.

Table of Contents

  1. About This Book
  2. About The Author
  3. Acknowledgments
  4. Chapter 1: Designing Database Tables
    1. Introduction
    2. Database Design
    3. Conceptual View
    4. Table Definitions
    5. Redundant Information
    6. Normalization
    7. Normalization Strategies
    8. Column Names and Reserved Words
    9. ANSI SQL Reserved Words
    10. SQL Code
    11. Data Integrity
    12. Referential Integrity
    13. Database Tables Used in This Book
    14. CUSTOMERS Table
    15. INVENTORY Table
    16. INVOICE Table
    18. PRODUCTS Table
    19. PURCHASES Table
    20. Table Contents
    21. The Database Structure
    22. Sample Database Tables
    23. Summary
  5. Chapter 2: Working with Data in PROC SQL
    1. Introduction
    2. Overview of Data Types
    3. Numeric Data
    4. Date and Time Column Definitions
    5. Character Data
    6. Missing Values and NULL
    7. Arithmetic and Missing Data
    8. SQL Keywords
    9. SQL Operators and Functions
    10. Comparison Operators
    11. Logical Operators
    12. Arithmetic Operators
    13. Character String Operators and Functions
    14. Summarizing Data
    15. Predicates
    16. Dictionary Tables
    17. Dictionary Tables and Metadata
    18. Displaying Dictionary Table Definitions
    19. Dictionary Table Column Names
    20. Accessing a Dictionary Table’s Contents
    21. Summary
  6. Chapter 3: Formatting Output
    1. Introduction
    2. Formatting Output
    3. Writing a Blank Line between Each Row
    4. Displaying Row Numbers
    5. Using the FORMAT= Column Modifier to Format Output
    6. Concatenating Character Strings
    7. Inserting Text and Constants between Columns
    8. Using Scalar Expressions with Selected Columns
    9. Ordering Output by Columns
    10. Grouping Data with Summary Functions
    11. Grouping Data and Sorting
    12. Subsetting Groups with the HAVING Clause
    13. Formatting Output with the Output Delivery System
    14. ODS and Output Formats
    15. Sending Output to a SAS Data Set
    16. Converting Output to Rich Text Format
    17. Exporting Data and Output to Excel
    18. Delivering Results to the Web
    19. Summary
  7. Chapter 4: Coding PROC SQL Logic
    1. Introduction
    2. Conditional Logic
    3. SQL Code
    4. SQL Code
    5. SQL Code
    6. SQL Code
    7. CASE Expressions
    8. Simple Case Expression
    9. Searched CASE Expression
    10. Case Logic versus COALESCE Expression
    11. Assigning Labels and Grouping Data
    12. Logic and Nulls
    13. Interfacing PROC SQL with the Macro Language
    14. Exploring Macro Variables and Values
    15. Creating Multiple Macro Variables
    16. Using Automatic Macro Variables to Control Processing
    17. Building Macro Tools and Applications
    18. Creating Simple Macro Tools
    19. Cross-Referencing Columns
    20. Determining the Number of Rows in a Table
    21. Identifying Duplicate Rows in a Table
    22. Summary
  8. Chapter 5: Creating, Populating, and Deleting Tables
    1. Introduction
    2. Creating Tables
    3. Creating a Table Using Column-Definition Lists
    4. Creating a Table Using the LIKE Clause
    5. Deriving a Table and Data from an Existing Table
    6. Populating Tables
    7. Adding Data to a Table with a SET Clause
    8. Adding Data to All of the Columns in a Row
    9. Adding Data to Some of the Columns in a Row
    10. Adding Data with a SELECT Query
    11. Bulk Loading Data from Microsoft Excel
    12. Integrity Constraints
    13. Defining Integrity Constraints
    14. Types of Integrity Constraints
    15. Preventing Null Values with a NOT NULL Constraint
    16. Enforcing Unique Values with a UNIQUE Constraint
    17. Validating Column Values with a CHECK Constraint
    18. Referential Integrity Constraints
    19. Establishing a Primary Key
    20. Establishing a Foreign Key
    21. Displaying Integrity Constraints
    22. Deleting Rows in a Table
    23. Deleting a Single Row in a Table
    24. Deleting More Than One Row in a Table
    25. Deleting All Rows in a Table
    26. Deleting Tables
    27. Deleting a Single Table
    28. Deleting Multiple Tables
    29. Deleting Tables That Contain Integrity Constraints
    30. Summary
  9. Chapter 6: Modifying and Updating Tables and Indexes
    1. Introduction
    2. Modifying Tables
    3. Adding New Columns
    4. Controlling the Position of Columns in a Table
    5. Changing a Column’s Length
    6. Changing a Column’s Format
    7. Changing a Column’s Label
    8. Renaming a Column
    9. Renaming a Table
    10. Indexes
    11. Designing Indexes
    12. Cardinality
    13. Index Selectivity
    14. Defining Indexes
    15. Creating a Simple Index
    16. Creating a Composite Index
    17. Preventing Duplicate Values in an Index
    18. Modifying Columns Containing Indexes
    19. Deleting (Dropping) Indexes
    20. Updating Data in a Table
    21. Summary
  10. Chapter 7: Coding Complex Queries
    1. Introduction
    2. Introducing Complex Queries
    3. Joins
    4. Why Joins Are Important
    5. Information Retrieval Based on Relationships
    6. DATA Step Merges versus PROC SQL Joins
    7. Types of Complex Queries
    8. Demystifying Join Algorithms
    9. Influencing Joins with a Little Magic
    10. Cartesian Product Joins
    11. Inner Joins
    12. Equijoins
    13. Non-Equijoins
    14. Reflexive or Self Joins
    15. Using Table Aliases in Joins
    16. Performing Computations in Joins
    17. Joins with Three Tables
    18. Joins with More Than Three Tables
    19. Outer Joins
    20. Left Outer Joins
    21. Right Outer Joins
    22. Full Outer Joins
    23. Subqueries
    24. Alternate Approaches to Subqueries
    25. Passing a Single Value with a Subquery
    26. Passing More Than One Row with a Subquery
    27. Comparing a Set of Values
    28. Correlated Subqueries
    29. Set Operations
    30. Rules for Set Operators
    31. Set Operators and Precedence
    32. Accessing Rows from the Intersection of Two Queries
    33. Accessing Rows from the Combination of Two Queries
    34. Concatenating Rows from Two Queries
    35. Comparing Rows from Two Queries
    36. Complex Query Applications
    37. One-to-One, One-to-Many, Many-to-One, and Many-to-Many Relationships
    38. Processing First, Last, and Between Rows for BY-and Groups
    39. Determining the Number of Rows in an Input Table
    40. Identifying Tables with the Most Indexes
    41. Summary
  11. Chapter 8: Working with Views
    1. Introduction
    2. Views-Windows to Your Data
    3. What Views Aren’t
    4. Types of Views
    5. Creating Views
    6. Displaying a View’s Contents
    7. Describing View Definitions
    8. Creating and Using Views in SAS
    9. Views and SAS Procedures
    10. Views and DATA Steps
    11. Eliminating Redundancy
    12. Restricting Data Access-Security
    13. Hiding Logic Complexities
    14. Nesting Views
    15. Updatable Views
    16. Inserting New Rows of Data
    17. Updating Existing Rows of Data
    18. Deleting Rows of Data
    19. Deleting Views
    20. Summary
  12. Chapter 9: Troubleshooting and Debugging
    1. Introduction
    2. The World of Bugs
    3. The Debugging Process
    4. Types of Problems
    5. Troubleshooting and Debugging Techniques
    6. Validating Queries with the VALIDATE Statement
    7. Documented PROC SQL Options and Statement
    8. Undocumented PROC SQL Options
    9. Macro Variables
    10. Troubleshooting and Debugging Examples
    11. Summary
  13. Chapter 10: Tuning for Performance and Efficiency
    1. Introduction
    2. Understanding Performance Tuning
    3. Sorting and Performance
    4. User-Specified Sorting (SORTPGM= System Options)
    5. Automatic Sorting
    6. Grouping and Performance
    7. Splitting Tables
    8. Indexes and Performance
    9. Reviewing CONTENTS Output and System Messages
    10. Optimizing WHERE Clause Processing with Indexes
    11. Constructing Efficient Logic Conditions
    12. Avoiding UNIONs
    13. Summary
  14. Index