1. SQL Processing With SAS
by SAS Institute
SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition
- Copyright
- About This Book
- 1. SQL Processing With SAS
- 1. Performing Queries Using PROC SQL
- 2. Performing Advanced Queries Using PROC SQL
- Overview
- Viewing SELECT Statement Syntax
- Displaying All Columns
- Limiting the Number of Rows Displayed
- Eliminating Duplicate Rows from Output
- Subsetting Rows by Using Conditional Operators
- Using Operators in PROC SQL
- Using the BETWEEN-AND Operator to Select within a Range of Values
- Using the CONTAINS or Question Mark (?) Operator to Select a String
- Example
- Using the IN Operator to Select Values from a List
- Using the IS MISSING or IS NULL Operator to Select Missing Values
- Example
- Using the LIKE Operator to Select a Pattern
- Specifying a Pattern
- Example
- Using the Sounds-Like (=*) Operator to Select a Spelling Variation
- Subsetting Rows by Using Calculated Values
- Enhancing Query Output
- Summarizing and Grouping Data
- Number of Arguments and Summary Function Processing
- Groups and Summary Function Processing
- SELECT Clause Columns and Summary Function Processing
- Using a Summary Function with a Single Argument (Column)
- Using a Summary Function with Multiple Arguments (Columns)
- Using a Summary Function without a GROUP BY Clause
- Using a Summary Function with Columns Outside of the Function
- Using a Summary Function with a GROUP BY Clause
- Counting Values by Using the COUNT Summary Function
- Counting All Rows
- Counting All Non-Missing Values in a Column
- Counting All Unique Values in a Column
- Selecting Groups by Using the HAVING Clause
- Understanding Data Remerging
- Example
- Subsetting Data by Using Subqueries
- Subsetting Data by Using Noncorrelated Subqueries
- Subsetting Data by Using Correlated Subqueries
- Validating Query Syntax
- Additional Features
- Summary
- Text Summary
- Viewing SELECT Statement Syntax
- Displaying All Columns
- Limiting the Number of Rows Displayed
- Eliminating Duplicate Rows from Output
- Subsetting Rows by Using Conditional Operators
- Subsetting Rows by Using Calculated Values
- Enhancing Query Output
- Summarizing and Grouping Data
- Subsetting Data by Using Subqueries
- Subsetting Data by Using Noncorrelated Subqueries
- Subsetting Data by Using Correlated Subqueries
- Validating Query Syntax
- Additional Features
- Syntax
- Sample Programs
- Points to Remember
- Text Summary
- Quiz
- 3. Combining Tables Horizontally Using PROC SQL
- Overview
- Understanding Joins
- Generating a Cartesian Product
- Using Inner Joins
- Introducing Inner Join Syntax
- Example
- Understanding How Joins Are Processed
- Eliminating Duplicate Columns
- Renaming a Column by Using a Column Alias
- Joining Tables That Have Rows with Matching Values
- Specifying a Table Alias
- Example: Complex PROC SQL Inner Join
- Example: PROC SQL Inner Join with Summary Functions
- Using Outer Joins
- Creating an Inner Join with Outer Join-Style Syntax
- Comparing SQL Joins and DATA Step Match-Merges
- Using In-Line Views
- Joining Multiple Tables and Views
- Summary
- Quiz
- 4. Combining Tables Vertically Using PROC SQL
- 5. Creating and Managing Tables Using PROC SQL
- Overview
- Understanding Methods of Creating Tables
- Creating an Empty Table by Defining Columns
- Displaying the Structure of a Table
- Creating an Empty Table That Is Like Another Table
- Creating a Table from a Query Result
- Inserting Rows of Data into a Table
- Creating a Table That Has Integrity Constraints
- Handling Errors in Row Insertions
- Displaying Integrity Constraints for a Table
- Updating Values in Existing Table Rows
- Updating Rows by Using the Same Expression
- Example
- Updating Rows by Using Different Expressions
- Example
- How PROC SQL Updates Rows Based on a CASE Expression
- How the Case Operand Works
- Updating Rows by Using the CASE Expression without a Case Operand
- Example
- Updating Rows by Using the CASE Expression with a Case Operand
- Example
- Using the CASE Expression in the SELECT Statement
- Example
- Deleting Rows in a Table
- Altering Columns in a Table
- Dropping Tables
- Summary
- Text Summary
- Understanding Methods of Creating Tables
- Creating an Empty Table by Defining Columns
- Displaying the Structure of a Table
- Creating an Empty Table That Is Like Another Table
- Creating a Table from a Query Result
- Inserting Rows of Data into a Table
- Creating a Table That Has Integrity Constraints
- Handling Errors in Row Insertions
- Displaying Integrity Constraints for a Table
- Updating Values in Existing Table Rows
- Deleting Rows in a Table
- Altering Columns in a Table
- Dropping Tables
- Syntax
- Sample Programs
- Creating an Empty Table by Defining Columns
- Creating an Empty Table That Is Like Another Table
- Creating a Table from a Query Result
- Displaying the Structure of a Table
- Inserting Rows into a Table by Specifying Column Names and Values
- Inserting Rows into a Table by Specifying Lists of Values
- Inserting Rows into a Table from a Query Result
- Creating a Table That Has Integrity Constraints
- Displaying Integrity Constraints for a Table
- Updating Rows in a Table Based on an Expression
- Updating Rows in a Table by Using a CASE Expression
- Updating Rows in a Table by Using a CASE Expression (Alternate Syntax)
- Deleting Rows in a Table
- Adding, Modifying, and Dropping Columns in a Table
- Dropping a Table
- Points to Remember
- Text Summary
- Quiz
- 6. Creating and Managing Indexes Using PROC SQL
- Overview
- Understanding Indexes
- Deciding Whether to Create an Index
- Creating an Index
- Displaying Index Specifications
- Managing Index Usage
- Understanding How SAS Decides Whether to Use an Index
- Determining Whether SAS Is Using an Index
- Example: Query That Uses an Index
- Example: Query That Does Not Use an Index
- Controlling Index Usage
- Using IDXWHERE= to Direct SAS to Use or Not to Use an Index
- Example
- Using IDXNAME= to Direct SAS to Use a Specified Index
- Example
- Dropping Indexes
- Summary
- Quiz
- 7. Creating and Managing Views Using PROC SQL
- 8. Managing Processing Using PROC SQL
- 2. SAS Macro Language
- 9. Introducing Macro Variables
- Overview
- Basic Concepts
- Using Automatic Macro Variables
- Using User-Defined Macro Variables
- Processing Macro Variables
- Displaying Macro Variable Values in the SAS Log
- Using Macro Functions to Mask Special Characters
- Using Macro Functions to Manipulate Character Strings
- Using SAS Functions with Macro Variables
- Combining Macro Variable References with Text
- Summary
- Text Summary
- Basic Concepts
- Using Automatic Macro Variables
- Using User-Defined Macro Variables
- Processing Macro Variables
- Displaying Macro Variable Values in the SAS Log
- Using Macro Functions to Mask Special Characters
- Using Macro Functions to Manipulate Character Strings
- Using SAS Functions with Macro Variables
- Combining Macro Variable References with Text
- Syntax
- Sample Programs
- Points to Remember
- Text Summary
- Quiz
- 10. Processing Macro Variables at Execution Time
- Overview
- Creating a Macro Variable During DATA Step Execution
- Creating Multiple Macro Variables During DATA Step Execution
- Referencing Macro Variables Indirectly
- Obtaining Macro Variable Values During DATA Step Execution
- Creating Macro Variables During PROC SQL Step Execution
- Working with PROC SQL Views
- Using Macro Variables in SCL Programs
- Summary
- Text Summary
- Creating a Macro Variable During DATA Step Execution
- Creating Multiple Macro Variables During DATA Step Execution
- Referencing Macro Variables Indirectly
- Obtaining Macro Variable Values During DATA Step Execution
- Creating Macro Variables During PROC SQL Step Execution
- Working with PROC SQL Views
- Using Macro Variables in SCL Programs
- Syntax
- Sample Programs
- Points to Remember
- Text Summary
- Quiz
- 11. Creating and Using Macro Programs
- 12. Storing Macro Programs
- 9. Introducing Macro Variables
- 3. Advanced SAS Programming Techniques
- 13. Creating Samples and Indexes
- Overview
- Creating a Systematic Sample from a Known Number of Observations
- Creating a Systematic Sample from an Unknown Number of Observations
- Creating a Random Sample with Replacement
- Creating a Random Sample without Replacement
- Using Indexes
- Creating Indexes in the DATA Step
- Managing Indexes with PROC DATASETS
- Managing Indexes with PROC SQL
- Documenting and Maintaining Indexes
- Summary
- Text Summary
- Creating a Systematic Sample from a Known Number of Observations
- Creating a Systematic Sample from an Unknown Number of Observations
- Creating a Random Sample with Replacement
- Creating a Random Sample without Replacement
- Using Indexes
- Creating Indexes in the DATA Step
- Managing Indexes with PROC DATASETS and PROC SQL
- Documenting and Maintaining Indexes
- Syntax
- Sample Programs
- Creating a Systematic Sample from a Known Number of Observations
- Creating a Systematic Sample from an Unknown Number of Observations
- Creating a Random Sample with Replacement
- Creating a Random Sample without Replacement
- Creating an Index in the DATA Step
- Managing Indexes with PROC DATASETS
- Managing Indexes with PROC SQL
- Points to Remember
- Text Summary
- Quiz
- 14. Combining Data Vertically
- 15. Combining Data Horizontally
- Overview
- Reviewing Terminology
- Working with Lookup Values Outside of SAS Data Sets
- Combining Data with the DATA Step Match-Merge
- Using PROC SQL to Join Data
- Comparing DATA Step Match-Merges and PROC SQL Joins
- Combining Summary Data and Detail Data
- Using an Index to Combine Data
- Using a Transactional Data Set
- Summary
- Text Summary
- Reviewing Terminology
- Working with Lookup Values Outside of SAS Data Sets
- Combining Data with the DATA Step Match-Merge
- Using PROC SQL to Join Data
- Comparing DATA Step Match-Merges and PROC SQL Joins
- Combining Summary Data and Detail Data
- Using an Index to Combine Data
- Using a Transactional Data Set
- Syntax
- Sample Programs
- Combining Data with the IF-THEN/ELSE Statement
- Combining Data with the ARRAY Statement
- Combining Data with the FORMAT Procedure
- Performing a DATA Step Match-Merge
- Performing a PROC SQL Join
- Working with a Many-to-Many Match
- Combining Summary Data and Detail Data
- Using an Index to Combine Data
- Using a Transactional Data Set
- Points to Remember
- Text Summary
- Quiz
- 16. Using Lookup Tables to Match Data
- Overview
- Using Multidimensional Arrays
- Using Stored Array Values
- Using PROC TRANSPOSE
- Merging the Transposed Data Set
- Using Hash Objects as Lookup Tables
- The Structure of a Hash Object
- Example
- Data Step Component Objects
- Declaring the Hash Object
- Instantiating the Hash Object
- Declaring and Instantiating the Hash Object in a Single Step
- Defining Keys and Data
- Using the Call Missing Routine
- Loading Key and Data Values
- Retrieving Matching Data
- Hash Object Processing
- Creating a Hash Object From a SAS Data Set
- Using a Non-Executing SET Statement
- Working with Multiple Data Variables
- Retrieving Multiple Data Values
- Using Return Codes with the FIND Method
- Example
- Summary
- Quiz
- 17. Formatting Data
- Overview
- Creating Custom Formats Using the VALUE Statement
- Creating Custom Formats Using the PICTURE Statement
- Managing Custom Formats
- Using Custom Formats
- Creating Formats from SAS Data Sets
- Creating SAS Data Sets from Custom Fomats
- Summary
- Quiz
- 18. Modifying SAS Data Sets and Tracking Changes
- Overview
- Using the MODIFY Statement
- Modifying All Observations in a SAS Data Set
- Modifying Observations Using a Transaction Data Set
- Modifying Observations Located by an Index
- Controlling the Update Process
- Understanding Integrity Constraints
- Placing Integrity Constraints on a Data Set
- Documenting Integrity Constraints
- Removing Integrity Constraints
- Understanding Audit Trails
- Initiating and Reading Audit Trails
- Controlling Data in the Audit Trail
- Controlling the Audit Trail
- Understanding Generation Data Sets
- Initiating Generation Data Sets
- Processing Generation Data Sets
- Summary
- Text Summary
- Using the MODIFY Statement
- Modifying All Observations in a SAS Data Set
- Modifying Observations Using a Transaction Data Set
- Modifying Observations Located by an Index
- Controlling the Update Process
- Placing Integrity Constraints on a Data Set
- Documenting and Removing Integrity Constraints
- Initiating and Terminating Audit Trails
- Controlling Data in the Audit Trail
- Initiating Generation Data Sets
- Processing Generation Data Sets
- Syntax
- Modifying All Observations in a SAS Data Set
- Modifying a Master Data Set Using the BY Statement
- Modifying a Master Data Set Using a Transaction Data Set and an Index
- Controlling the Update Process
- Using PROC DATASETS to Create Integrity Constraints, Generation Data Sets, and Audit Trails
- Using _IORC_ with %SYSRC
- Specifying an Audit Trail File
- Using PROC DATASETS to Rename or Delete Generation Data Sets
- Sample Programs
- Points to Remember
- Text Summary
- Quiz
- 13. Creating Samples and Indexes
- 4. Optimizing SAS Programs
- 19. Introduction to Efficient SAS Programming
- 20. Controlling Memory Usage
- 21. Controlling Data Storage Space
- Overview
- Reducing Data Storage Space for Character Variables
- Reducing Data Storage Space for Numeric Variables
- Compressing Data Files
- Review of Uncompressed Data File Structure
- Compressed Data File Structure
- Deciding Whether to Compress a Data File
- The COMPRESS= System Option and the COMPRESS= Data Set Option
- Example
- Accessing Observations Directly in a Compressed Data Set
- The POINTOBS= Data Set Option
- Example
- The REUSE= System Option and the REUSE= Data Set Option
- Example
- How SAS Compresses Data
- Comparative Example: Creating and Reading Compressed Data Files
- Using SAS DATA Step Views to Conserve Data Storage Space
- DATA Step Views
- Example
- The DESCRIBE Statement
- Creating and Referencing a SAS DATA Step View
- Referencing a Data View Multiple Times in One Program
- Example
- Making Multiple Passes Through Data in a Data View
- Creating Data Views on Unstable Data
- Comparative Example: Creating and Reading a SAS Data View
- Summary
- Quiz
- 22. Using Best Practices
- Overview
- Executing Only Necessary Statements
- Positioning of the Subsetting IF Statement
- Comparative Example: Creating a Subset of Data
- Using Conditional Logic Efficiently
- Comparative Example: Creating Variables Conditionally Using DO Groups
- Comparative Example: Creating Variables Conditionally When Calling Functions
- Using DO Groups Efficiently
- Comparative Example: Creating Data in DO Groups
- Eliminating Unnecessary Passes through the Data
- Using a Single DATA or PROC Step to Enhance Efficiency
- Using a Single DATA Step to Create Multiple Output Data Sets
- Using the SORT Procedure with a WHERE Statement to Create Sorted Subsets
- Using the DATASETS Procedure to Modify Variable Attributes
- Comparative Example: Creating Multiple Subsets of a SAS Data Set
- Comparative Example: Creating a Sorted Subset of a SAS Data Set
- Comparative Example: Changing the Variable Attributes of a SAS Data Set
- Reading and Writing Only Essential Data
- Selecting Observations Using Subsetting IF versus WHERE Statement
- Comparative Example: Creating a Subset of a SAS Data Set
- Other Differences between the IF and WHERE Statements
- Using the WHERE Statement with the OBS= and FIRSTOBS= Options
- Selecting Observations When Reading Data from External Files
- Comparative Example: Creating a Subset of Data by Reading Data from an External File
- Subsetting Variables with the KEEP= and DROP= Statements and Options
- Comparative Example: Creating a Report That Contains Average and Median Statistics
- Comparative Example: Creating a SAS Data Set That Contains Only Certain Variables
- Storing Data in SAS Data Sets
- Avoiding Unnecessary Procedure Invocation
- Summary
- Quiz
- 23. Selecting Efficient Sorting Strategies
- Overview
- Avoiding Unnecessary Sorts
- Using BY-Group Processing with an Index
- Comparative Example: Using BY-Group Processing with an Index to Avoid a Sort
- Using the NOTSORTED Option
- Example
- Using FIRST. and LAST.
- Example
- Using the GROUPFORMAT Option
- Example
- Using the CLASS Statement
- Example
- Comparative Example: Using a BY or CLASS Statement to Avoid a Sort
- Using the SORTEDBY= Data Set Option
- Example
- Using a Threaded Sort
- Calculating and Allocating Sort Resources
- Handling Large Data Sets
- Removing Duplicate Observations Efficiently
- Additional Features
- Summary
- Quiz
- 24. Querying Data Efficiently
- Overview
- Using an Index for Efficient WHERE Processing
- Identifying Available Indexes
- Example: Identifying One Available Index
- Example: Identifying Multiple Available Indexes
- Compound Optimization
- Example: Composite Index That Can Be Used to Optimize Multiple Conditions
- Example: Composite Index That Can Be Used to Optimize One Condition
- Example: Composite Index That Cannot Be Used for Optimizing
- Identifying Conditions That Can Be Optimized
- Estimating the Number of Observations
- Comparing Probable Resource Usage
- Deciding Whether to Create an Index
- Guidelines for Deciding Whether to Create an Index
- Example: Selecting Subsets of Various Sizes from Data Sets of Various Sizes
- Query 1: Small Subset from a Large Data Set
- Query 2: Large Subset from a Large Data Set
- Query 3: Small Subset from a Small Data Set
- Using the Options IDXWHERE= and IDXNAME= to Control Index Usage
- Specifying MSGLEVEL=I to Determine Whether SAS Is Using an Index
- Example: Using IDXWHERE=NO to Prevent Index Usage
- Comparing Procedures That Produce Detail Reports
- Comparing Tools for Summarizing Data
- Comparing Resource Usage across Summarization Tools
- Comparative Example: Displaying Summary Statistics for One Class Variable
- Using PROC MEANS to Display Summary Statistics for Combinations of Class Variables
- Comparing Resource Usage across Three Techniques for Using PROC MEANS
- Using a Basic PROC MEANS Step to Combine All Class Variables
- Example: Displaying Summary Statistics for All Combinations of the Class Variables
- Understanding Types
- Using the TYPES Statement in PROC MEANS to Combine Class Variables
- Example: Using the TYPES Statement in PROC MEANS
- Using the NWAY Option in PROC MEANS to Combine Class Variables
- Example: Using the NWAY Option in Multiple PROC MEANS Steps
- Using the WHERE= Option in PROC MEANS to Combine Class Variables
- Example: Using the WHERE= Option in PROC MEANS
- Comparative Example: Displaying Summary Statistics for Combinations of Class Variables
- Additional Features
- Summary
- Using an Index for Efficient WHERE Processing
- Using an Index for Efficient WHERE Processing
- Identifying Available Indexes
- Identifying Conditions That Can Be Optimized
- Estimating the Number of Observations
- Comparing Probable Resource Usage
- Deciding Whether to Create an Index
- Comparing Procedures That Produce Detail Reports
- Comparing Tools for Summarizing Data
- Using an Index for Efficient WHERE Processing
- Quiz
- 5. Quiz Answer Keys
- 1. Quiz Answer Keys
- Chapter 1: Performing Queries Using PROC SQL
- Chapter 2: Performing Advanced Queries Using PROC SQL
- Chapter 3: Combining Tables Horizontally Using PROC SQL
- Chapter 4: Combining Tables Vertically Using PROC SQL
- Chapter 5: Creating and Managing Tables Using PROC SQL
- Chapter 6: Creating and Managing Indexes Using PROC SQL
- Chapter 7: Creating and Managing Views Using PROC SQL
- Chapter 8: Managing Processing Using PROC SQL
- Chapter 9: Introducing Macro Variables
- Chapter 10: Processing Macro Variables at Execution Time
- Chapter 11: Creating and Using Macro Programs
- Chapter 12: Storing Macro Programs
- Chapter 13: Creating Samples and Indexes
- Chapter 14: Combining Data Vertically
- Chapter 15: Combining Data Horizontally
- Chapter 16: Using Lookup Tables to Match Data
- Chapter 17: Formatting Data
- Chapter 18: Modifying SAS Data Sets and Tracking Changes
- Chapter 19: Introduction to Efficient SAS Programming
- Chapter 20: Controlling Memory Usage
- Chapter 21: Controlling Data Storage Space
- Chapter 22: Using Best Practices
- Chapter 23: Selecting Efficient Sorting Strategies
- Chapter 24: Querying Data Efficiently
- 1. Quiz Answer Keys
| Chapter 1 Performing Queries Using PROC SQL 3 |
| Chapter 2 Performing Advanced Queries Using PROC SQL 25 |
| Chapter 3 Combining Tables Horizontally Using PROC SQL 79 |
| Chapter 4 Combining Tables Vertically Using PROC SQL 123 |
| Chapter 5 Creating and Managing Tables Using PROC SQL 159 |
| Chapter 6 Creating and Managing Indexes Using PROC SQL 221 |
| Chapter 7 Creating and Managing Views Using PROC SQL 243 |
| Chapter 8 Managing Processing Using PROC SQL 261 |
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.