You are previewing SAS Certification Prep Guide, 4th Edition.
O'Reilly logo
SAS Certification Prep Guide, 4th Edition

Book Description

Businesses rely on career professionals with strong SAS knowledge and skills. Set yourself apart from the competition by earning the only globally recognized credential endorsed by SAS. The SAS Certification Prep Guide: Advanced Programming for SAS 9, Fourth Edition, 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, as well as a new chapter on creating functions with PROC FCMP. You will also become familiar with the enhancements and new functionality that are available in SAS 9. New or experienced SAS users will find this guide to be an invaluable resource that covers the objectives tested on the exam. The text contains quizzes that enable you to test your understanding of material in each chapter. Quiz solutions are included at the end of the book. Candidates must earn the SAS Certified Base Programmer for SAS 9 Credential before taking the SAS Advanced Programming for SAS 9 exam. You’ll find instructions on how to obtain sample data when accessing SAS through SAS Enterprise Guide, SAS Studio, SAS University Edition, and the SAS windowing environment. This edition provides significant improvements to numerous examples, making the code even more efficient. Experience is a critical component to becoming a SAS Certified Professional. This comprehensive guide along with training in SAS SQL1, SAS Macro Language 1, and SAS Programming 3 are valuable resources designed to help you prepare for the Advanced SAS Certification exam.

Table of Contents

  1. Title Page
  2. Copyright
  3. About This Book
    1. Chapter 1: Performing Queries Using PROC SQL
      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
      14. End Notes
    2. Chapter 2: Performing Advanced Queries Using PROC SQL
      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
    3. Chapter 3: Combining Tables Horizontally Using PROC SQL
      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
    4. Chapter 4: Combining Tables Vertically Using PROC SQL
      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
    5. Chapter 5: Creating and Managing Tables Using PROC SQL
      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
    6. Chapter 6: Creating and Managing Indexes Using PROC SQL
      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
    7. Chapter 7: Creating and Managing Views Using PROC SQL
      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
    8. Chapter 8: Managing Processing Using PROC SQL
      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. Chapter 9: Introducing Macro Variables
      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
    2. Chapter 10: Processing Macro Variables at Execution Time
      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
    3. Chapter 11: Creating and Using Macro Programs
      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
    4. Chapter 12: Storing Macro Programs
      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. Chapter 13: Creating Indexes
      1. Overview
      2. Using Indexes
      3. Creating Indexes in the DATA Step
      4. Managing Indexes with PROC DATASETS
      5. Managing Indexes with PROC SQL
      6. Documenting and Maintaining Indexes
      7. Summary
      8. Quiz
    2. Chapter 14: Combining Data Vertically
      1. Overview
      2. Using a FILENAME Statement
      3. Using the FILEVAR= Option
      4. Appending SAS Data Sets
      5. Additional Features
      6. Summary
      7. Quiz
    3. Chapter 15: Combining Data Horizontally
      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 Transaction Data Set
      10. Summary
      11. Quiz
    4. Chapter 16: Using Lookup Tables to Match Data
      1. Overview
      2. Using Multidimensional Arrays
      3. Populating an Array from a SAS Data Set
      4. Using PROC TRANSPOSE
      5. Merging the Transposed Data Set
      6. Using Hash Objects as Lookup Tables
      7. Summary
      8. Quiz
    5. Chapter 17: Formatting Data
      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
    6. Chapter 18: Modifying SAS Data Sets and Tracking Changes
      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. Chapter 19: Introduction to Efficient SAS Programming
      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
    2. Chapter 20: Controlling Memory Usage
      1. Overview
      2. Controlling Page Size and the Number of Buffers
      3. Using the SASFILE Statement
      4. Additional Features
      5. Summary
      6. Quiz
    3. Chapter 21: Controlling Data Storage Space
      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
    4. Chapter 22: Using Best Practices
      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
    5. Chapter 23: Querying Data Efficiently
      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
    6. Chapter 24: Creating Functions with PROC FCMP
      1. Overview
      2. Using PROC FCMP
      3. About PROC FCMP
      4. PROC FCMP Statement
      5. FUNCTION Statement
      6. RETURN Statement
      7. Using the Newly Defined Function
      8. Using PROC FCMP to Create a Subroutine
      9. Quiz
    1. Appendix 1: Quiz Answer Keys
      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 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: Querying Data Efficiently
      24. Chapter 24: Creating Functions with PROC FCMP
  4. Index
  5. Additional Resources