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
- About This Book
- About The Author
- Acknowledgments
-
Chapter 1: Designing Database Tables
- Introduction
- Database Design
- Conceptual View
- Table Definitions
- Redundant Information
- Normalization
- Normalization Strategies
- Column Names and Reserved Words
- ANSI SQL Reserved Words
- SQL Code
- Data Integrity
- Referential Integrity
- Database Tables Used in This Book
- CUSTOMERS Table
- INVENTORY Table
- INVOICE Table
- MANUFACTURERS Table
- PRODUCTS Table
- PURCHASES Table
- Table Contents
- The Database Structure
- Sample Database Tables
- Summary
-
Chapter 2: Working with Data in PROC SQL
- Introduction
- Overview of Data Types
- Numeric Data
- Date and Time Column Definitions
- Character Data
- Missing Values and NULL
- Arithmetic and Missing Data
- SQL Keywords
- SQL Operators and Functions
- Comparison Operators
- Logical Operators
- Arithmetic Operators
- Character String Operators and Functions
- Summarizing Data
- Predicates
- Dictionary Tables
- Dictionary Tables and Metadata
- Displaying Dictionary Table Definitions
- Dictionary Table Column Names
- Accessing a Dictionary Table’s Contents
- Summary
-
Chapter 3: Formatting Output
- Introduction
- Formatting Output
- Writing a Blank Line between Each Row
- Displaying Row Numbers
- Using the FORMAT= Column Modifier to Format Output
- Concatenating Character Strings
- Inserting Text and Constants between Columns
- Using Scalar Expressions with Selected Columns
- Ordering Output by Columns
- Grouping Data with Summary Functions
- Grouping Data and Sorting
- Subsetting Groups with the HAVING Clause
- Formatting Output with the Output Delivery System
- ODS and Output Formats
- Sending Output to a SAS Data Set
- Converting Output to Rich Text Format
- Exporting Data and Output to Excel
- Delivering Results to the Web
- Summary
-
Chapter 4: Coding PROC SQL Logic
- Introduction
- Conditional Logic
- SQL Code
- SQL Code
- SQL Code
- SQL Code
- CASE Expressions
- Simple Case Expression
- Searched CASE Expression
- Case Logic versus COALESCE Expression
- Assigning Labels and Grouping Data
- Logic and Nulls
- Interfacing PROC SQL with the Macro Language
- Exploring Macro Variables and Values
- Creating Multiple Macro Variables
- Using Automatic Macro Variables to Control Processing
- Building Macro Tools and Applications
- Creating Simple Macro Tools
- Cross-Referencing Columns
- Determining the Number of Rows in a Table
- Identifying Duplicate Rows in a Table
- Summary
-
Chapter 5: Creating, Populating, and Deleting Tables
- Introduction
- Creating Tables
- Creating a Table Using Column-Definition Lists
- Creating a Table Using the LIKE Clause
- Deriving a Table and Data from an Existing Table
- Populating Tables
- Adding Data to a Table with a SET Clause
- Adding Data to All of the Columns in a Row
- Adding Data to Some of the Columns in a Row
- Adding Data with a SELECT Query
- Bulk Loading Data from Microsoft Excel
- Integrity Constraints
- Defining Integrity Constraints
- Types of Integrity Constraints
- Preventing Null Values with a NOT NULL Constraint
- Enforcing Unique Values with a UNIQUE Constraint
- Validating Column Values with a CHECK Constraint
- Referential Integrity Constraints
- Establishing a Primary Key
- Establishing a Foreign Key
- Displaying Integrity Constraints
- Deleting Rows in a Table
- Deleting a Single Row in a Table
- Deleting More Than One Row in a Table
- Deleting All Rows in a Table
- Deleting Tables
- Deleting a Single Table
- Deleting Multiple Tables
- Deleting Tables That Contain Integrity Constraints
- Summary
-
Chapter 6: Modifying and Updating Tables and Indexes
- Introduction
- Modifying Tables
- Adding New Columns
- Controlling the Position of Columns in a Table
- Changing a Column’s Length
- Changing a Column’s Format
- Changing a Column’s Label
- Renaming a Column
- Renaming a Table
- Indexes
- Designing Indexes
- Cardinality
- Index Selectivity
- Defining Indexes
- Creating a Simple Index
- Creating a Composite Index
- Preventing Duplicate Values in an Index
- Modifying Columns Containing Indexes
- Deleting (Dropping) Indexes
- Updating Data in a Table
- Summary
-
Chapter 7: Coding Complex Queries
- Introduction
- Introducing Complex Queries
- Joins
- Why Joins Are Important
- Information Retrieval Based on Relationships
- DATA Step Merges versus PROC SQL Joins
- Types of Complex Queries
- Demystifying Join Algorithms
- Influencing Joins with a Little Magic
- Cartesian Product Joins
- Inner Joins
- Equijoins
- Non-Equijoins
- Reflexive or Self Joins
- Using Table Aliases in Joins
- Performing Computations in Joins
- Joins with Three Tables
- Joins with More Than Three Tables
- Outer Joins
- Left Outer Joins
- Right Outer Joins
- Full Outer Joins
- Subqueries
- Alternate Approaches to Subqueries
- Passing a Single Value with a Subquery
- Passing More Than One Row with a Subquery
- Comparing a Set of Values
- Correlated Subqueries
- Set Operations
- Rules for Set Operators
- Set Operators and Precedence
- Accessing Rows from the Intersection of Two Queries
- Accessing Rows from the Combination of Two Queries
- Concatenating Rows from Two Queries
- Comparing Rows from Two Queries
- Complex Query Applications
- One-to-One, One-to-Many, Many-to-One, and Many-to-Many Relationships
- Processing First, Last, and Between Rows for BY-and Groups
- Determining the Number of Rows in an Input Table
- Identifying Tables with the Most Indexes
- Summary
-
Chapter 8: Working with Views
- Introduction
- Views-Windows to Your Data
- What Views Aren’t
- Types of Views
- Creating Views
- Displaying a View’s Contents
- Describing View Definitions
- Creating and Using Views in SAS
- Views and SAS Procedures
- Views and DATA Steps
- Eliminating Redundancy
- Restricting Data Access-Security
- Hiding Logic Complexities
- Nesting Views
- Updatable Views
- Inserting New Rows of Data
- Updating Existing Rows of Data
- Deleting Rows of Data
- Deleting Views
- Summary
- Chapter 9: Troubleshooting and Debugging
-
Chapter 10: Tuning for Performance and Efficiency
- Introduction
- Understanding Performance Tuning
- Sorting and Performance
- User-Specified Sorting (SORTPGM= System Options)
- Automatic Sorting
- Grouping and Performance
- Splitting Tables
- Indexes and Performance
- Reviewing CONTENTS Output and System Messages
- Optimizing WHERE Clause Processing with Indexes
- Constructing Efficient Logic Conditions
- Avoiding UNIONs
- Summary
- Index
Product information
- Title: PROC SQL: Beyond the Basics Using SAS, Second Edition
- Author(s):
- Release date: October 2013
- Publisher(s): SAS Institute
- ISBN: 9781612907345
You might also like
book
PROC SQL: Beyond the Basics Using SAS®
Master the language of PROC SQL! Get faster and better results when you use the SQL …
book
Advanced SQL with SAS
This book introduces advanced techniques for using PROC SQL in SAS. If you are a SAS …
book
Elementary Statistics Using SAS
Bridging the gap between statistics texts and SAS documentation, Elementary Statistics Using SAS is written for …
book
PROC SQL, 3rd Edition
PROC SQL: Beyond the Basics Using SAS®, Third Edition, is a step-by-step, example-driven guide that helps …