You are previewing SAS® Certification Prep Guide: Advanced Programming for SAS®9, Third Edition.
O'Reilly logo
SAS® Certification Prep Guide: Advanced Programming for SAS®9, Third Edition

Book Description

Prepares you to take the Advanced Programming for SAS 9 exam. Major topics include SQL processing with SAS, the SAS macro language, advanced SAS programming techniques, and optimizing SAS programs. You will also become familiar with the enhancements and new functionality that are available in SAS 9.

Table of Contents

      1. Overview
      2. PROC SQL Basics
      3. Writing a PROC SQL Step
      4. Selecting Columns
      5. Specifying the Table
      6. Specifying Subsetting Criteria
      7. Ordering Rows
      8. Querying Multiple Tables
      9. Summarizing Groups of Data
      10. Creating Output Tables
      11. Additional Features
      12. Summary
      13. Quiz
      1. Overview
      2. Viewing SELECT Statement Syntax
      3. Displaying All Columns
      4. Limiting the Number of Rows Displayed
      5. Eliminating Duplicate Rows from Output
      6. Subsetting Rows by Using Conditional Operators
      7. Subsetting Rows by Using Calculated Values
      8. Enhancing Query Output
      9. Summarizing and Grouping Data
      10. Subsetting Data by Using Subqueries
      11. Subsetting Data by Using Noncorrelated Subqueries
      12. Subsetting Data by Using Correlated Subqueries
      13. Validating Query Syntax
      14. Additional Features
      15. Summary
      16. Quiz
      1. Overview
      2. Understanding Joins
      3. Generating a Cartesian Product
      4. Using Inner Joins
      5. Using Outer Joins
      6. Creating an Inner Join with Outer Join-Style Syntax
      7. Comparing SQL Joins and DATA Step Match-Merges
      8. Using In-Line Views
      9. Joining Multiple Tables and Views
      10. Summary
      11. Quiz
      1. Overview
      2. Understanding Set Operations
      3. Using the EXCEPT Set Operator
      4. Using the INTERSECT Set Operator
      5. Using the UNION Set Operator
      6. Using the OUTER UNION Set Operator
      7. Comparing Outer Unions and Other SAS Techniques
      8. Summary
      9. Quiz
      1. Overview
      2. Understanding Methods of Creating Tables
      3. Creating an Empty Table by Defining Columns
      4. Displaying the Structure of a Table
      5. Creating an Empty Table That Is like Another Table
      6. Creating a Table from a Query Result
      7. Inserting Rows of Data into a Table
      8. Creating a Table That Has Integrity Constraints
      9. Handling Errors in Row Insertions
      10. Displaying Integrity Constraints for a Table
      11. Updating Values in Existing Table Rows
      12. Deleting Rows in a Table
      13. Altering Columns in a Table
      14. Dropping Tables
      15. Summary
      16. Quiz
      1. Overview
      2. Understanding Indexes
      3. Deciding Whether to Create an Index
      4. Creating an Index
      5. Displaying Index Specifications
      6. Managing Index Usage
      7. Dropping Indexes
      8. Summary
      9. Quiz
      1. Overview
      2. Creating and Using PROC SQL Views
      3. Displaying the Definition for a PROC SQL View
      4. Managing PROC SQL Views
      5. Updating PROC SQL Views
      6. Dropping PROC SQL Views
      7. Summary
      8. Quiz
      1. Overview
      2. Specifying SQL Options
      3. Controlling Execution
      4. Controlling Output
      5. Testing and Evaluating Performance
      6. Resetting Options
      7. Using Dictionary Tables
      8. Additional Features
      9. Summary
      10. Quiz
      1. Overview
      2. Basic Concepts
      3. Using Automatic Macro Variables
      4. Using User-Defined Macro Variables
      5. Processing Macro Variables
      6. Displaying Macro Variable Values in the SAS Log
      7. Using Macro Functions to Mask Special Characters
      8. Using Macro Functions to Manipulate Character Strings
      9. Using SAS Functions with Macro Variables
      10. Combining Macro Variable References with Text
      11. Summary
      12. Quiz
      1. Overview
      2. Creating a Macro Variable During DATA Step Execution
      3. Creating Multiple Macro Variables During DATA Step Execution
      4. Referencing Macro Variables Indirectly
      5. Obtaining Macro Variable Values During DATA Step Execution
      6. Creating Macro Variables During PROC SQL Step Execution
      7. Working with PROC SQL Views
      8. Using Macro Variables in SCL Programs
      9. Summary
      10. Quiz
      1. Overview
      2. Basic Concepts
      3. Developing and Debugging Macros
      4. Using Macro Parameters
      5. Understanding Symbol Tables
      6. Processing Statements Conditionally
      7. Processing Statements Iteratively
      8. Using Arithmetic and Logical Expressions
      9. Summary
      10. Quiz
      1. Overview
      2. Understanding Session-Compiled Macros
      3. Storing Macro Definitions in External Files
      4. Storing Macro Definitions in Catalog SOURCE Entries
      5. Using the Autocall Facility
      6. Using Stored Compiled Macros
      7. Summary
      8. Quiz
      1. Overview
      2. Creating a Systematic Sample from a Known Number of Observations
      3. Creating a Systematic Sample from an Unknown Number of Observations
      4. Creating a Random Sample with Replacement
      5. Creating a Random Sample without Replacement
      6. Using Indexes
      7. Creating Indexes in the DATA Step
      8. Managing Indexes with PROC DATASETS
      9. Managing Indexes with PROC SQL
      10. Documenting and Maintaining Indexes
      11. Summary
      12. Quiz
      1. Overview
      2. Using a FILENAME Statement
      3. Using an INFILE Statement
      4. Appending SAS Data Sets
      5. Additional Features
      6. Summary
      7. Quiz
      1. Overview
      2. Reviewing Terminology
      3. Working with Lookup Values Outside of SAS Data Sets
      4. Combining Data with the DATA Step Match-Merge
      5. Using PROC SQL to Join Data
      6. Comparing DATA Step Match-Merges and PROC SQL Joins
      7. Combining Summary Data and Detail Data
      8. Using an Index to Combine Data
      9. Using a Transactional Data Set
      10. Summary
      11. Quiz
      1. Overview
      2. Using Multidimensional Arrays
      3. Using Stored Array Values
      4. Using PROC TRANSPOSE
      5. Merging the Transposed Data Set
      6. Using Hash Objects as Lookup Tables
      7. Summary
      8. Quiz
      1. Overview
      2. Creating Custom Formats Using the VALUE Statement
      3. Creating Custom Formats Using the PICTURE Statement
      4. Managing Custom Formats
      5. Using Custom Formats
      6. Creating Formats from SAS Data Sets
      7. Creating SAS Data Sets from Custom Formats
      8. Summary
      9. Quiz
      1. Overview
      2. Using the MODIFY Statement
      3. Modifying All Observations in a SAS Data Set
      4. Modifying Observations Using a Transaction Data Set
      5. Modifying Observations Located by an Index
      6. Controlling the Update Process
      7. Understanding Integrity Constraints
      8. Placing Integrity Constraints on a Data Set
      9. Documenting Integrity Constraints
      10. Removing Integrity Constraints
      11. Understanding Audit Trails
      12. Initiating and Reading Audit Trails
      13. Controlling Data in the Audit Trail
      14. Controlling the Audit Trail
      15. Understanding Generation Data Sets
      16. Initiating Generation Data Sets
      17. Processing Generation Data Sets
      18. Summary
      19. Quiz
      1. Overview
      2. Overview of Computing Resources
      3. Assessing Efficiency Needs at Your Site
      4. Understanding Efficiency Trade-offs
      5. Using SAS System Options to Track Resources
      6. Using Benchmarks to Compare Techniques
      7. Summary
      1. Overview
      2. Controlling Page Size and the Number of Buffers
      3. Using the SASFILE Statement
      4. Additional Features
      5. Summary
      6. Quiz
      1. Overview
      2. Reducing Data Storage Space for Character Variables
      3. Reducing Data Storage Space for Numeric Variables
      4. Compressing Data Files
      5. Using SAS DATA Step Views to Conserve Data Storage Space
      6. Summary
      7. Quiz
      1. Overview
      2. Executing Only Necessary Statements
      3. Eliminating Unnecessary Passes through the Data
      4. Reading and Writing Only Essential Data
      5. Storing Data in SAS Data Sets
      6. Avoiding Unnecessary Procedure Invocation
      7. Summary
      8. Quiz
      1. Overview
      2. Avoiding Unnecessary Sorts
      3. Using a Threaded Sort
      4. Calculating and Allocating Sort Resources
      5. Handling Large Data Sets
      6. Removing Duplicate Observations Efficiently
      7. Additional Features
      8. Summary
      9. Quiz
      1. Overview
      2. Using an Index for Efficient WHERE Processing
      3. Identifying Available Indexes
      4. Identifying Conditions That Can Be Optimized
      5. Estimating the Number of Observations
      6. Comparing Probable Resource Usage
      7. Deciding Whether to Create an Index
      8. Comparing Procedures That Produce Detail Reports
      9. Comparing Tools for Summarizing Data
      10. Summary
      11. Quiz
      1. Chapter 1: Performing Queries Using PROC SQL
      2. Chapter 2: Performing Advanced Queries Using PROC SQL
      3. Chapter 3: Combining Tables Horizontally Using PROC SQL
      4. Chapter 4: Combining Tables Vertically Using PROC SQL
      5. Chapter 5: Creating and Managing Tables Using PROC SQL
      6. Chapter 6: Creating and Managing Indexes Using PROC SQL
      7. Chapter 7: Creating and Managing Views Using PROC SQL
      8. Chapter 8: Managing Processing Using PROC SQL
      9. Chapter 9: Introducing Macro Variables
      10. Chapter 10: Processing Macro Variables at Execution Time
      11. Chapter 11: Creating and Using Macro Programs
      12. Chapter 12: Storing Macro Programs
      13. Chapter 13: Creating Samples and Indexes
      14. Chapter 14: Combining Data Vertically
      15. Chapter 15: Combining Data Horizontally
      16. Chapter 16: Using Lookup Tables to Match Data
      17. Chapter 17: Formatting Data
      18. Chapter 18: Modifying SAS Data Sets and Tracking Changes
      19. Chapter 19: Introduction to Efficient SAS Programming
      20. Chapter 20: Controlling Memory Usage
      21. Chapter 21: Controlling Data Storage Space
      22. Chapter 22: Using Best Practices
      23. Chapter 23: Selecting Efficient Sorting Strategies
      24. Chapter 24: Querying Data Efficiently