Global Statements

Books

SAS | Decommissioning

© 2024 | Rick Aster

Rick Aster

Routine SAS SQL

978-1-891957-21-5 33.00 (US)

---

Contents

• 1. Putting SAS and SQL Together
• 2. Queries
• 3. SQL Computations and Conditions
• 4. Summary Queries
• 5. SAS Output From SQL
• 6. Combining Tables
• 7. Working With SAS Data
• 8. Working With DBMS Data
• 9. SQL Options and Execution
• 10. Macro Variables for SQL
• Appendix 1. The SAS Data Model
• Appendix 2. SQL Reserved Words

Detailed Contents

• Foreword
• 1. Putting SAS and SQL Together
  • SQL
  • SAS
  • The SQL Procedure
  • Why Mix SAS and SQL?
  • SAS and SQL Objects and Terminology
  • SAS Coding Rules and Conventions
  • SAS and SQL List Syntax
  • SAS Tables and SAS Program Files
• 2. Queries
  • The Simple Query Expression
  • ODS and SAS Output
  • The SELECT Clause and Columns
  • New Columns and Aliases
  • Unnamed Columns
  • Column Attributes
  • The FROM Clause and Tables
  • The WHERE Clause
  • The ORDER BY Clause
  • List of Values
  • Storing a Result Set as a Table
  • Storing a Query Expression as a View
  • Selecting All Columns
  • Using Data Set Options
  • Reserved Words
• 3. SQL Computations and Conditions
  • Expressions as Result Columns
  • Expression Data Types
  • Constant Values
  • WHERE Expressions
  • The WHERE Statement and Data Set Option
  • Numeric Operators
  • Comparison Operators
  • Operator Priority
  • Operators for Character Values
  • The CASE Operator
  • Numeric Functions
  • SAS Functions Not Available in SQL
  • Selection Functions
  • Time Functions
  • Substrings and String Padding
  • Text Search Functions
  • String Concatenation
  • Functions for Character Processing and Encoding
  • Environment Functions
  • Functions for Formats and Informats
• 4. Summary Queries
  • Summary Statistics and Aggregate Functions
  • Null Values in Statistics
  • Aggregate Functions for Character Columns
  • Special Arguments for Aggregate Functions
  • Computations Based on Summary Data
  • Grouping
  • The HAVING Clause
  • Sorting Summary Rows
  • Query Execution Sequence
  • Using CALCULATED To Mark Column Aliases
• 5. SAS Output From SQL
  • SQL Statements and Global Statements
  • ODS Destinations
  • Large Output Tables
  • Title Lines
  • ODS Text Lines
  • Centering Output
  • Labels as Column Headers
  • Displaying Missing Values
  • Formats
  • Value Formats
• 6. Combining Tables
  • Table Aliases
  • The FROM Clause With a List of Tables
  • Table Join Operators
  • ID Columns When Joining Tables
  • Internal and External Identifiers
  • Joining Three or More Tables
  • Groups and Summary Functions When Joining Tables
  • Self Joins
  • Subqueries
  • Subqueries in Table Joins
  • Subquery as Column Expression
  • The IN Operator With a Subquery
  • Set Operators
  • Set Operators When Columns Aren’t Identical
  • Set Operator Results When One Set Has No Rows
  • Combining Summary Data
• 7. Working With SAS Data
  • Libraries
  • The WORK Library
  • Other Predefined Libraries
  • Tables
  • Rows
  • Changing Values in a Table
  • Columns
  • Data Types
  • Column Attributes
  • Indexes
  • Integrity Constraints
  • Views
  • Views That Contain Library Definitions
  • Editable Views
  • The CONTENTS Procedure
  • Deleting
  • Data Set Options
  • DICTIONARY Tables
• 8. Working With DBMS Data
  • Two Ways to Connect
  • SAS/ACCESS for Relational Databases, and Access Options
  • SQL Pass-Through
  • Avoiding Confusion When Working in Multiple Environments
  • Converting Database Columns to the SAS Environment
  • SAS Name Literals and the RENAME= Data Set Option
  • Time Data
  • Converting Numeric ID Codes
  • Character Column Lengths
  • Recoding
  • Character Transcoding
  • Strategies for Large Objects
  • The Database Library Engine
  • The DATASETS and CONTENTS Procedures With the Database Library Engine
  • Creating and Populating a Database Table
  • In-Database Processing
  • Remote SQL Pass-Through
  • Database Performance Issues
• 9. SQL Options and Execution
  • SQL Options
  • A Macro Variable Option
  • System Options for SQL
  • SQL Execution
• 10. Macro Variables for SQL
  • Working With Macro Variables
  • Automatic Macro Variables for SQL
  • SQL Return Codes
  • Other Automatic Macro Variables
  • Writing Queries With Macro Variables
  • Creating Macro Variables From Data Values
  • Combining Columns in a Macro Variable
  • Writing Results in the Log
  • Creating a List of Values in a Macro Variable
  • Creating a List of Macro Variables
• Appendix 1. The SAS Data Model
• Appendix 2. SQL Reserved Words
• Index

Corrections

Despite my best efforts and the many helpful suggestions of my publishers and editors, every book I’ve written has errors. Check this page for corrections to errors I find in Routine SAS SQL. If you may have found an error that is not listed here, please let me know about it by writing to me at run@globalstatements.com.