PROC SQL: Beyond the Basics Using SAS, Second Edition

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
   17. MANUFACTURERS 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