SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition

SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition

by SAS Institute
Released June 2009
Publisher(s): SAS Institute
ISBN: 9781607640448

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The SAS Certification Prep Guide: Advanced Programming for SAS 9, Second 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. You will also become familiar with the enhancements and new functionality that are available in SAS 9.

Experienced SAS users who want to prepare for this exam will find this guide to be an invaluable, convenient, and comprehensive resource that covers all of the objectives tested on the exam. The text contains quizzes that enable you to test your understanding of material in each chapter. Additionally, solutions to all quizzes are included at the back of the book. Candidates must earn the SAS Certified Base Programmer Credential for SAS 9 before taking the SAS Advanced Programming for SAS 9 exam.

Table of contents

  1. Copyright
  2. About This Book
    1. Purpose
    2. Audience
    3. Prerequisites
    4. How to Create Practice Data
    5. Syntax Conventions
    6. SAS Certification Practice Exam: Advanced Programming for SAS®9
    7. SAS Advanced Programming for SAS®9 Exam
    8. Additional Resources
  3. 1. SQL Processing With SAS
    1. 1. Performing Queries Using PROC SQL
      1. Overview
        1. Introduction
        2. Objectives
      2. PROC SQL Basics
        1. How PROC SQL Is Unique
      3. Writing a PROC SQL Step
        1. The SELECT Statement
      4. Selecting Columns
        1. Creating New Columns
      5. Specifying the Table
      6. Specifying Subsetting Criteria
      7. Ordering Rows
        1. Ordering by Multiple Columns
      8. Querying Multiple Tables
        1. Specifying Columns That Appear in Multiple Tables
        2. Specifying Multiple Table Names
        3. Subsetting Rows
        4. Ordering Rows
      9. Summarizing Groups of Data
        1. Example
        2. Summary Functions
      10. Creating Output Tables
        1. Example
      11. Additional Features
      12. Summary
        1. Text Summary
          1. PROC SQL Basics
          2. Writing a PROC SQL Step
          3. Selecting Columns
          4. Specifying the Table
          5. Specifying Subsetting Criteria
          6. Ordering Rows
          7. Querying Multiple Tables
          8. Summarizing Groups of Data
          9. Creating Output Tables
          10. Additional Features
        2. Syntax
        3. Sample Programs
          1. Querying a Table
          2. Summarizing Groups of Data
          3. Creating a Table from the Results of a Query on Two Tables
        4. Points to Remember
      13. Quiz
    2. 2. Performing Advanced Queries Using PROC SQL
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Viewing SELECT Statement Syntax
      3. Displaying All Columns
        1. Using SELECT *
        2. Using the FEEDBACK Option
      4. Limiting the Number of Rows Displayed
        1. Example
      5. Eliminating Duplicate Rows from Output
        1. Example
      6. Subsetting Rows by Using Conditional Operators
        1. Using Operators in PROC SQL
        2. Using the BETWEEN-AND Operator to Select within a Range of Values
        3. Using the CONTAINS or Question Mark (?) Operator to Select a String
        4. Example
        5. Using the IN Operator to Select Values from a List
        6. Using the IS MISSING or IS NULL Operator to Select Missing Values
        7. Example
        8. Using the LIKE Operator to Select a Pattern
        9. Specifying a Pattern
        10. Example
        11. Using the Sounds-Like (=*) Operator to Select a Spelling Variation
      7. Subsetting Rows by Using Calculated Values
        1. Understanding How PROC SQL Processes Calculated Columns
        2. Using the Keyword CALCULATED
      8. Enhancing Query Output
        1. Specifying Column Formats and Labels
        2. Specifying Titles and Footnotes
        3. Adding a Character Constant to Output
      9. Summarizing and Grouping Data
        1. Number of Arguments and Summary Function Processing
        2. Groups and Summary Function Processing
        3. SELECT Clause Columns and Summary Function Processing
        4. Using a Summary Function with a Single Argument (Column)
        5. Using a Summary Function with Multiple Arguments (Columns)
        6. Using a Summary Function without a GROUP BY Clause
        7. Using a Summary Function with Columns Outside of the Function
        8. Using a Summary Function with a GROUP BY Clause
        9. Counting Values by Using the COUNT Summary Function
        10. Counting All Rows
        11. Counting All Non-Missing Values in a Column
        12. Counting All Unique Values in a Column
        13. Selecting Groups by Using the HAVING Clause
        14. Understanding Data Remerging
        15. Example
      10. Subsetting Data by Using Subqueries
        1. Introducing Subqueries
        2. Types of Subqueries
      11. Subsetting Data by Using Noncorrelated Subqueries
        1. Using Single-Value Noncorrelated Subqueries
        2. Using Multiple-Value Noncorrelated Subqueries
        3. Example
        4. Using Comparisons with Subqueries
        5. Using the ANY Operator
        6. Example
        7. Using the ALL Operator
        8. Example
      12. Subsetting Data by Using Correlated Subqueries
        1. Example
        2. Using the EXISTS and NOT EXISTS Conditional Operators
        3. Example: Correlated Subquery with NOT EXISTS
      13. Validating Query Syntax
        1. Using the NOEXEC Option
        2. Using the VALIDATE Keyword
      14. Additional Features
      15. Summary
        1. Text Summary
          1. Viewing SELECT Statement Syntax
          2. Displaying All Columns
          3. Limiting the Number of Rows Displayed
          4. Eliminating Duplicate Rows from Output
          5. Subsetting Rows by Using Conditional Operators
          6. Subsetting Rows by Using Calculated Values
          7. Enhancing Query Output
          8. Summarizing and Grouping Data
          9. Subsetting Data by Using Subqueries
          10. Subsetting Data by Using Noncorrelated Subqueries
          11. Subsetting Data by Using Correlated Subqueries
          12. Validating Query Syntax
          13. Additional Features
        2. Syntax
        3. Sample Programs
          1. Displaying all Columns in Output and an Expanded Column List in the SAS Log
          2. Eliminating Duplicate Rows from Output
          3. Subsetting Rows by Using Calculated Values
          4. Subsetting Data by Using a Noncorrelated Subquery
          5. Subsetting Data by Using a Correlated Subquery
        4. Points to Remember
      16. Quiz
    3. 3. Combining Tables Horizontally Using PROC SQL
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Understanding Joins
      3. Generating a Cartesian Product
      4. Using Inner Joins
        1. Introducing Inner Join Syntax
        2. Example
        3. Understanding How Joins Are Processed
        4. Eliminating Duplicate Columns
        5. Renaming a Column by Using a Column Alias
        6. Joining Tables That Have Rows with Matching Values
        7. Specifying a Table Alias
        8. Example: Complex PROC SQL Inner Join
        9. Example: PROC SQL Inner Join with Summary Functions
      5. Using Outer Joins
        1. Introducing Types of Outer Joins
        2. Using a Left Outer Join
        3. Using a Right Outer Join
        4. Using a Full Outer Join
        5. Example: Outer Join
      6. Creating an Inner Join with Outer Join-Style Syntax
      7. Comparing SQL Joins and DATA Step Match-Merges
        1. When All of the Values Match
        2. When Only Some of the Values Match
        3. When Only Some of the Values Match: Using the COALESCE Function
        4. Understanding the Advantages of PROC SQL Joins
      8. Using In-Line Views
        1. Introducing In-Line Views
        2. Referencing an In-Line View with Other Views or Tables
        3. Referencing Multiple Tables in an In-Line View
        4. Assigning an Alias to an In-Line View
        5. Example: Query That Contains an In-Line View
      9. Joining Multiple Tables and Views
        1. Example: Complex Query That Combines Four Tables
        2. Example: Technique 1 (PROC SQL Subqueries, Joins, and In-Line Views)
          1. Query 1: Identify the crew for the Copenhagen (CPH) flight
          2. Query 2: Find the states and job categories of the crew members
          3. Query 3: Find the employee numbers of the crew supervisors
          4. Query 4: Find the names of the supervisors
        3. Example: Technique 2 (PROC SQL Multi-way Join with Reflexive Join)
        4. Example: Technique 3 (Traditional SAS Programming)
      10. Summary
        1. Text Summary
          1. Understanding Joins
          2. Generating a Cartesian Product
          3. Using Inner Joins
          4. Using Outer Joins
          5. Creating an Inner Join with Outer Join-Style Syntax
          6. Comparing SQL Joins and DATA Step Match-Merges
          7. Using In-Line Views
          8. Joining Multiple Tables and Views
        2. Syntax
        3. Sample Programs
          1. Combining Tables by Using an Inner Join
          2. Combining Tables by Using a Left Outer Join
          3. Overlaying Common Columns in a Full Outer Join
          4. Joining Tables by Using a Subquery and an In-Line View
        4. Points to Remember
      11. Quiz
    4. 4. Combining Tables Vertically Using PROC SQL
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Understanding Set Operations
        1. Example
        2. Processing a Single Set Operation
        3. Using Multiple Set Operators
        4. Example
        5. Processing Multiple Set Operations
        6. Introducing Set Operators
        7. Processing Unique versus Duplicate Rows
        8. Combining and Overlaying Columns
        9. Modifying Results by Using Keywords
      3. Using the EXCEPT Set Operator
        1. Using the EXCEPT Operator Alone
        2. Using the Keyword ALL with the EXCEPT Operator
        3. Using the Keyword CORR with the EXCEPT Operator
        4. Using the Keywords ALL and CORR with the EXCEPT Operator
        5. Example: EXCEPT Operator
        6. Example: EXCEPT Operator in an In-Line View
      4. Using the INTERSECT Set Operator
        1. Using the INTERSECT Operator Alone
        2. Using the Keyword ALL with the INTERSECT Operator
        3. Using the Keyword CORR with the INTERSECT Operator
        4. Using the Keywords ALL and CORR with the INTERSECT Operator
        5. Example: INTERSECT Operator
      5. Using the UNION Set Operator
        1. Using the UNION Operator Alone
        2. Using the Keyword ALL with the UNION Operator
        3. Using the Keyword CORR with the UNION Operator
        4. Using the Keywords ALL and CORR with the UNION Operator
        5. Example: UNION Operator
        6. Example: UNION Operator and Summary Functions
      6. Using the OUTER UNION Set Operator
        1. Using the OUTER UNION Operator Alone
        2. Using the Keyword CORR with the OUTER UNION Operator
        3. Example: OUTER UNION Operator
      7. Comparing Outer Unions and Other SAS Techniques
        1. Program 1: PROC SQL OUTER UNION Set Operation with CORR
        2. Program 2: DATA Step, SET Statement, and PROC PRINT Step
      8. Summary
        1. Text Summary
          1. Understanding Set Operations
          2. Using the EXCEPT Set Operator
          3. Using the INTERSECT Set Operator
          4. Using the UNION Set Operator
          5. Using the OUTER UNION Set Operator
          6. Comparing Outer Unions and Other SAS Techniques
        2. Syntax
        3. Sample Program
        4. Points to Remember
      9. Quiz
    5. 5. Creating and Managing Tables Using PROC SQL
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Understanding Methods of Creating Tables
      3. Creating an Empty Table by Defining Columns
        1. Example
        2. Specifying Data Types
        3. Specifying Column Widths
        4. Specifying Column Modifiers
        5. Example
      4. Displaying the Structure of a Table
        1. Example
      5. Creating an Empty Table That Is Like Another Table
        1. Example
        2. Specifying a Subset of Columns from a Table
        3. Example
      6. Creating a Table from a Query Result
        1. Example
        2. Copying a Table
        3. Example
      7. Inserting Rows of Data into a Table
        1. Inserting Rows by Using the SET Clause
        2. Example
        3. Inserting Rows by Using the VALUES Clause
        4. Example
        5. Inserting Rows from a Query Result
        6. Example
      8. Creating a Table That Has Integrity Constraints
        1. General Integrity Constraints
        2. Referential Integrity Constraints
        3. Creating a Constraint in a Column Specification
        4. Example
        5. Creating a Constraint by Using a Constraint Specification
        6. Example
      9. Handling Errors in Row Insertions
        1. Example
        2. Using the UNDO_POLICY= Option to Control UNDO Processing
        3. Example
      10. Displaying Integrity Constraints for a Table
        1. Example
      11. Updating Values in Existing Table Rows
        1. Updating Rows by Using the Same Expression
        2. Example
        3. Updating Rows by Using Different Expressions
        4. Example
        5. How PROC SQL Updates Rows Based on a CASE Expression
        6. How the Case Operand Works
        7. Updating Rows by Using the CASE Expression without a Case Operand
        8. Example
        9. Updating Rows by Using the CASE Expression with a Case Operand
        10. Example
        11. Using the CASE Expression in the SELECT Statement
        12. Example
      12. Deleting Rows in a Table
        1. Example
      13. Altering Columns in a Table
        1. Adding Columns to a Table
        2. Example
        3. Dropping Columns from a Table
        4. Example
        5. Modifying Columns in a Table
        6. Example
        7. Adding, Dropping, and Modifying Columns in a Single Statement
        8. Example
      14. Dropping Tables
        1. Example
      15. Summary
        1. Text Summary
          1. Understanding Methods of Creating Tables
          2. Creating an Empty Table by Defining Columns
          3. Displaying the Structure of a Table
          4. Creating an Empty Table That Is Like Another Table
          5. Creating a Table from a Query Result
          6. Inserting Rows of Data into a Table
          7. Creating a Table That Has Integrity Constraints
          8. Handling Errors in Row Insertions
          9. Displaying Integrity Constraints for a Table
          10. Updating Values in Existing Table Rows
          11. Deleting Rows in a Table
          12. Altering Columns in a Table
          13. Dropping Tables
        2. Syntax
        3. Sample Programs
          1. Creating an Empty Table by Defining Columns
          2. Creating an Empty Table That Is Like Another Table
          3. Creating a Table from a Query Result
          4. Displaying the Structure of a Table
          5. Inserting Rows into a Table by Specifying Column Names and Values
          6. Inserting Rows into a Table by Specifying Lists of Values
          7. Inserting Rows into a Table from a Query Result
          8. Creating a Table That Has Integrity Constraints
          9. Displaying Integrity Constraints for a Table
          10. Updating Rows in a Table Based on an Expression
          11. Updating Rows in a Table by Using a CASE Expression
          12. Updating Rows in a Table by Using a CASE Expression (Alternate Syntax)
          13. Deleting Rows in a Table
          14. Adding, Modifying, and Dropping Columns in a Table
          15. Dropping a Table
        4. Points to Remember
      16. Quiz
    6. 6. Creating and Managing Indexes Using PROC SQL
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Understanding Indexes
        1. Accessing Rows in a Table
        2. Simple and Composite Indexes
        3. Unique Indexes
        4. Example
      3. Deciding Whether to Create an Index
        1. PROC SQL Queries That Can Be Optimized by an Index
        2. Benefits of Using an Index
        3. Example: Using an Index to Access a Small Subset of Data
        4. Understanding the Costs of Using an Index
        5. Guidelines for Creating Indexes
      4. Creating an Index
        1. Creating Multiple Indexes
        2. Example: Creating a Simple Index
        3. Example: Creating a Composite, Unique Index
      5. Displaying Index Specifications
        1. Alternatives to the DESCRIBE TABLE Statement
        2. Example
      6. Managing Index Usage
        1. Understanding How SAS Decides Whether to Use an Index
        2. Determining Whether SAS Is Using an Index
        3. Example: Query That Uses an Index
        4. Example: Query That Does Not Use an Index
        5. Controlling Index Usage
        6. Using IDXWHERE= to Direct SAS to Use or Not to Use an Index
        7. Example
        8. Using IDXNAME= to Direct SAS to Use a Specified Index
        9. Example
      7. Dropping Indexes
        1. Example: Dropping a Composite Index
      8. Summary
        1. Text Summary
          1. Understanding Indexes
          2. Deciding Whether to Create an Index
          3. Creating an Index
          4. Displaying Index Specifications
          5. Managing Index Usage
          6. Dropping Indexes
        2. Syntax
        3. Sample Programs
          1. Creating a Simple, Unique Index and a Composite Index
          2. Displaying Index Specifications
          3. Determining Whether SAS Is Using an Index
          4. Directing SAS to Ignore All Indexes
          5. Directing SAS to Use a Specified Index
          6. Dropping an Index
        4. Points to Remember
      9. Quiz
    7. 7. Creating and Managing Views Using PROC SQL
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Creating and Using PROC SQL Views
        1. PROC SQL Views
        2. Creating PROC SQL Views
        3. Example
        4. Using PROC SQL Views
      3. Displaying the Definition for a PROC SQL View
        1. Example
      4. Managing PROC SQL Views
        1. Guidelines for Using PROC SQL Views
        2. Omitting the Libref
        3. Using an Embedded LIBNAME Statement
        4. Example
        5. Creating a View to Enhance Table Security
        6. Example
      5. Updating PROC SQL Views
        1. Example
      6. Dropping PROC SQL Views
        1. Example
      7. Summary
        1. Text Summary
          1. Using PROC SQL Views
          2. Creating 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
        2. Syntax
        3. Sample Programs
          1. Creating a PROC SQL View
          2. Displaying the Definition for a PROC SQL View
          3. Using a PROC SQL View in a Query
          4. Updating a PROC SQL View
          5. Dropping a PROC SQL View
        4. Points to Remember
      8. Quiz
    8. 8. Managing Processing Using PROC SQL
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Specifying SQL Options
      3. Controlling Execution
        1. Restricting Row Processing
        2. Example
      4. Controlling Output
        1. Including a Column of Row Numbers
        2. Example
        3. Double-Spacing Output
        4. Example
        5. Flowing Characters within a Column
        6. Example
      5. Testing and Evaluating Performance
        1. Writing Timing Information for Each Statement
        2. Example
      6. Resetting Options
        1. Example
      7. Using Dictionary Tables
        1. Exploring and Using Dictionary Tables
        2. Example
        3. Example
      8. Additional Features
        1. Restricting the Number of Loops
        2. Stopping Execution in PROC SQL after an Error
      9. Summary
        1. Text Summary
          1. Specifying SQL Options
          2. Restricting Row Processing
          3. Controlling Output
          4. Testing and Evaluating Performance
          5. Resetting Options
          6. Using Dictionary Tables
          7. Additional Features
        2. Syntax
        3. Sample Programs
          1. Querying a Table Using PROC SQL Options
          2. Describing and Querying a Dictionary Table
        4. Points to Remember
      10. Quiz
  4. 2. SAS Macro Language
    1. 9. Introducing Macro Variables
      1. Overview
        1. Introduction
        2. Objectives
      2. Basic Concepts
        1. Macro Variables
        2. Referencing Macro Variables
        3. Example: Referencing a Macro Variable
        4. Example: Referencing a Macro Variable in a Title
      3. Using Automatic Macro Variables
        1. Example
      4. Using User-Defined Macro Variables
        1. The %LET Statement
        2. Example
        3. %LET Statement Examples
      5. Processing Macro Variables
        1. SAS Processing
        2. Tokenization
        3. Examples
        4. Macro Triggers
      6. Displaying Macro Variable Values in the SAS Log
        1. The SYMBOLGEN Option
        2. Example
        3. The %PUT Statement
        4. Example
      7. Using Macro Functions to Mask Special Characters
        1. Macro Quoting Functions
        2. Example
        3. The %STR Function
        4. Example
        5. The %NRSTR Function
        6. Example
        7. The %BQUOTE Function
        8. Example
      8. Using Macro Functions to Manipulate Character Strings
        1. Macro Character Functions
        2. The %UPCASE Function
        3. Example
        4. The %QUPCASE Function
        5. Example
        6. The %SUBSTR Function
        7. Example
        8. The %QSUBSTR Function
        9. Example
        10. The %INDEX Function
        11. Example
        12. The %SCAN Function
        13. Example
        14. The %QSCAN Function
        15. Example
      9. Using SAS Functions with Macro Variables
        1. The %SYSFUNC Function
        2. Example
        3. Quoting with %QSYSFUNC
        4. Example
      10. Combining Macro Variable References with Text
        1. Delimiters in Macro Variable Names
      11. Summary
        1. Text Summary
          1. Basic Concepts
          2. Using Automatic Macro Variables
          3. Using User-Defined Macro Variables
          4. Processing Macro Variables
          5. Displaying Macro Variable Values in the SAS Log
          6. Using Macro Functions to Mask Special Characters
          7. Using Macro Functions to Manipulate Character Strings
          8. Using SAS Functions with Macro Variables
          9. Combining Macro Variable References with Text
        2. Syntax
        3. Sample Programs
          1. Creating Macro Variables with a %LET Statement
          2. Using Automatic Macro Variables
          3. Inserting Macro Variables Immediately After Text
          4. Inserting Macro Variables Immediately Before Text
        4. Points to Remember
      12. Quiz
    2. 10. Processing Macro Variables at Execution Time
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Creating a Macro Variable During DATA Step Execution
        1. Example
        2. The SYMPUT Routine
        3. Using SYMPUT with a Literal
        4. Example
        5. Using SYMPUT with a DATA Step Variable
        6. Example
        7. The SYMPUTX Routine
        8. Example
        9. Using CALL SYMPUT with DATA Step Expressions
        10. Example
        11. PUT Function
        12. Example
      3. Creating Multiple Macro Variables During DATA Step Execution
        1. Creating Multiple Macro Variables with CALL SYMPUT
        2. Example
      4. Referencing Macro Variables Indirectly
        1. Introduction
        2. The Forward Re-Scan Rule
        3. Example
        4. Example
        5. Example
      5. Obtaining Macro Variable Values During DATA Step Execution
        1. The SYMGET Function
        2. Example
      6. Creating Macro Variables During PROC SQL Step Execution
        1. The INTO Clause and the NOPRINT Option
        2. Example
        3. Creating Variables with the INTO Clause
        4. Example
        5. Example
        6. Creating a Delimited List of Values
        7. Example
      7. Working with PROC SQL Views
      8. Using Macro Variables in SCL Programs
        1. The SYMPUTN Routine
        2. Example
        3. The SYMGETN Function
        4. Example
      9. Summary
        1. Text Summary
          1. Creating a Macro Variable During DATA Step Execution
          2. Creating Multiple Macro Variables During DATA Step Execution
          3. Referencing Macro Variables Indirectly
          4. Obtaining Macro Variable Values During DATA Step Execution
          5. Creating Macro Variables During PROC SQL Step Execution
          6. Working with PROC SQL Views
          7. Using Macro Variables in SCL Programs
        2. Syntax
        3. Sample Programs
          1. Using CALL SYMPUT to Create Macro Variables
          2. Referencing Macro Variables Indirectly
          3. Using SYMGET to Obtain Macro Variable Values
          4. Creating Macro Variables with the INTO Clause
        4. Points to Remember
      10. Quiz
    3. 11. Creating and Using Macro Programs
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Basic Concepts
        1. Defining a Macro
        2. Example
        3. Compiling a Macro
        4. The MCOMPILENOTE Option
        5. Example
        6. Calling a Macro
        7. Example
        8. Macro Execution
        9. Example
      3. Developing and Debugging Macros
        1. Monitoring Execution with System Options
        2. The MPRINT Option
        3. Example
        4. The MLOGIC Option
        5. Example
        6. Comments in Macro Programs
        7. Example
      4. Using Macro Parameters
        1. Example
        2. Macros That Include Positional Parameters
        3. Example
        4. Macros That Include Keyword Parameters
        5. Example
        6. Macros That Include Mixed Parameter Lists
        7. Example
        8. Macros That Include the PARMBUFF Option
        9. Example
      5. Understanding Symbol Tables
        1. The Global Symbol Table
        2. The %GLOBAL Statement
        3. Example
        4. The Local Symbol Table
        5. The %LOCAL Statement
        6. Example
        7. Rules for Creating and Updating Variables
        8. Multiple Local Symbol Tables
        9. Example
        10. The MPRINTNEST Option
        11. Example
        12. The MLOGICNEST Option
        13. Example
      6. Processing Statements Conditionally
        1. Conditional Execution
        2. %IF-%THEN Compared to IF-THEN
        3. Example
        4. Example
        5. Macro Execution with Conditional Processing
        6. Example
        7. Example
        8. Conditional Processing of Parts of Statements
        9. Example
        10. Case Sensitivity in Macro Comparisons
        11. Example
      7. Processing Statements Iteratively
        1. Example
        2. Example
        3. Generating Complete Steps
        4. Example
      8. Using Arithmetic and Logical Expressions
        1. The %EVAL Function
        2. Examples
        3. Example
        4. Automatic Evaluation
      9. Summary
        1. Text Summary
          1. Basic Concepts
          2. Developing and Debugging Macros
          3. Using Macro Parameters
          4. Understanding Symbol Tables
          5. Processing Statements Conditionally
          6. Processing Statements Iteratively
          7. Using Arithmetic and Logical Expressions
        2. Syntax
        3. Sample Programs
          1. Defining a Basic Macro
          2. Defining a Macro with Positional Parameters
          3. Defining a Macro with Keyword Parameters
          4. Defining a Macro with Mixed Parameters
          5. Using the %IF-%THEN Statement
          6. Using the Iterative %DO Statement
        4. Points to Remember
      10. Quiz
    4. 12. Storing Macro Programs
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Understanding Session-Compiled Macros
      3. Storing Macro Definitions in External Files
        1. Example
      4. Storing Macro Definitions in Catalog SOURCE Entries
        1. Example
        2. The CATALOG Procedure
        3. Example
        4. The CATALOG Access Method
        5. Example
        6. Example
      5. Using the Autocall Facility
        1. Creating an Autocall Library
        2. Example
        3. Default Autocall Library
        4. Example
        5. Accessing Autocall Macros
        6. Example
      6. Using Stored Compiled Macros
        1. The Stored Compiled Macro Facility
        2. Creating a Stored Compiled Macro
        3. Using the SOURCE Option
        4. Example
        5. Accessing Stored Compiled Macros
        6. Example
        7. Accessing Stored Macro Code
        8. Example
      7. Summary
        1. Text Summary
          1. Understanding Session-Compiled Macros
          2. Storing Macro Definitions in External Files
          3. Storing Macro Definitions in Catalog SOURCE Entries
          4. Using the Autocall Facility
          5. Using Stored Compiled Macros
        2. Syntax
        3. Sample Programs
          1. Compiling an Externally Stored Macro Definition with the %INCLUDE Statement
          2. Listing the Contents of a Catalog
          3. Using the Catalog Access Method
          4. Accessing an Autocall Macro
          5. Creating a Stored Compiled Macro
        4. Points to Remember
      8. Quiz
  5. 3. Advanced SAS Programming Techniques
    1. 13. Creating Samples and Indexes
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Creating a Systematic Sample from a Known Number of Observations
        1. Example
        2. Example
      3. Creating a Systematic Sample from an Unknown Number of Observations
        1. Example
      4. Creating a Random Sample with Replacement
        1. Using the RANUNI Function
        2. Example
        3. Using a Multiplier with the RANUNI Function
        4. Using the CEIL Function
        5. Example
        6. Example
      5. Creating a Random Sample without Replacement
        1. Example
      6. Using Indexes
        1. Types of Indexes
      7. Creating Indexes in the DATA Step
        1. Examples
        2. Example
        3. Determining Whether SAS Is Using an Index
        4. Example
      8. Managing Indexes with PROC DATASETS
        1. Example
      9. Managing Indexes with PROC SQL
        1. Example
      10. Documenting and Maintaining Indexes
        1. Example
        2. Example
        3. Copying Data Sets
        4. Examples
        5. Renaming Data Sets
        6. Example
        7. Renaming Variables
        8. Example
      11. Summary
        1. Text Summary
          1. Creating a Systematic Sample from a Known Number of Observations
          2. Creating a Systematic Sample from an Unknown Number of Observations
          3. Creating a Random Sample with Replacement
          4. Creating a Random Sample without Replacement
          5. Using Indexes
          6. Creating Indexes in the DATA Step
          7. Managing Indexes with PROC DATASETS and PROC SQL
          8. Documenting and Maintaining Indexes
        2. Syntax
        3. Sample Programs
          1. Creating a Systematic Sample from a Known Number of Observations
          2. Creating a Systematic Sample from an Unknown Number of Observations
          3. Creating a Random Sample with Replacement
          4. Creating a Random Sample without Replacement
          5. Creating an Index in the DATA Step
          6. Managing Indexes with PROC DATASETS
          7. Managing Indexes with PROC SQL
        4. Points to Remember
      12. Quiz
    2. 14. Combining Data Vertically
      1. Overview
        1. Introduction
        2. Objectives
      2. Using a FILENAME Statement
        1. Example
      3. Using an INFILE Statement
        1. Example
        2. Assigning the Names of the Files to Be Read
        3. Example
        4. Using the COMPRESS Function
        5. Example
        6. Using the END= Option
        7. Example
        8. Using Date Functions
        9. Example
        10. Using the INTNX Function
        11. Example
      4. Appending SAS Data Sets
        1. Example
        2. Using the FORCE Option
        3. Example
        4. Appending Variables with Different Lengths
        5. Example
        6. Appending Variables with Different Types
        7. Example
      5. Additional Features
        1. Storing Raw Data Filenames in a SAS Data Set
        2. Storing Raw Data Filenames in an External File
      6. Summary
        1. Text Summary
          1. Using a FILENAME Statement
          2. Using an INFILE Statement
          3. Appending SAS Data Sets
          4. Additional Features
        2. Syntax
          1. Combining Raw Data Files Using a FILENAME Statement
          2. Combining Raw Data Files Using an INFILE Statement
          3. Combining SAS Data Sets Using PROC APPEND
        3. Sample Programs
          1. Combining Raw Data Files Using a FILENAME Statement
          2. Combining Raw Data Files Using an INFILE Statement
          3. Combining SAS Data Sets Using PROC APPEND
        4. Points to Remember
      7. Quiz
    3. 15. Combining Data Horizontally
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Reviewing Terminology
        1. Relationships between Input Data Sources
      3. Working with Lookup Values Outside of SAS Data Sets
        1. The IF-THEN/ELSE Statement
        2. Example: Using the IF-THEN/ELSE Statement to Combine Data
        3. SAS Arrays
        4. Example: Using the ARRAY Statement to Combine Data
        5. User-Defined SAS Formats
        6. Example: Using the FORMAT Procedure to Combine Data
      4. Combining Data with the DATA Step Match-Merge
        1. The DATA Step Match-Merge
        2. Working with Multiple Lookup Tables
        3. Example
      5. Using PROC SQL to Join Data
        1. The SQL Procedure
        2. Example: Working with Multiple Lookup Tables
      6. Comparing DATA Step Match-Merges and PROC SQL Joins
        1. Examples
        2. DATA Step Match-Merge
        3. Execution of a DATA Step Match-Merge
        4. PROC SQL Join
        5. Execution of a PROC SQL Join
        6. Example: Combining Data from a Many-to-Many Match
        7. Using Multiple SET Statements
        8. Example: Using Multiple SET Statements with a Many-to-Many Match
      7. Combining Summary Data and Detail Data
        1. The MEANS Procedure
        2. Example
        3. Example
        4. The Sum Statement
        5. Example
      8. Using an Index to Combine Data
        1. The KEY= Option
        2. Example
        3. Example
        4. Example
        5. The _IORC_ Variable
        6. Example
      9. Using a Transactional Data Set
        1. Using the UPDATE Statement
        2. Example
      10. Summary
        1. Text Summary
          1. Reviewing Terminology
          2. Working with Lookup Values Outside of SAS Data Sets
          3. Combining Data with the DATA Step Match-Merge
          4. Using PROC SQL to Join Data
          5. Comparing DATA Step Match-Merges and PROC SQL Joins
          6. Combining Summary Data and Detail Data
          7. Using an Index to Combine Data
          8. Using a Transactional Data Set
        2. Syntax
        3. Sample Programs
          1. Combining Data with the IF-THEN/ELSE Statement
          2. Combining Data with the ARRAY Statement
          3. Combining Data with the FORMAT Procedure
          4. Performing a DATA Step Match-Merge
          5. Performing a PROC SQL Join
          6. Working with a Many-to-Many Match
          7. Combining Summary Data and Detail Data
          8. Using an Index to Combine Data
          9. Using a Transactional Data Set
        4. Points to Remember
      11. Quiz
    4. 16. Using Lookup Tables to Match Data
      1. Overview
        1. Introduction
        2. Objectives
      2. Using Multidimensional Arrays
        1. Review of the Multidimensional Array Statement
        2. Example
      3. Using Stored Array Values
        1. Example
        2. Creating an Array
        3. Loading the Array Elements
        4. Reading the Actual Values
      4. Using PROC TRANSPOSE
        1. Example
        2. Adding Descriptive Variable Names
      5. Merging the Transposed Data Set
        1. Structuring the Data for a Merge
        2. Using a BY Statement with PROC TRANSPOSE
        3. Sorting the Work.Ctarget2 Data Set
        4. Reorganizing the Sasuser.Monthsum Data Set
        5. Sorting the Work.Mnthsum2 Data Set
        6. Completing the Merge
      6. Using Hash Objects as Lookup Tables
        1. The Structure of a Hash Object
        2. Example
        3. Data Step Component Objects
        4. Declaring the Hash Object
        5. Instantiating the Hash Object
        6. Declaring and Instantiating the Hash Object in a Single Step
        7. Defining Keys and Data
        8. Using the Call Missing Routine
        9. Loading Key and Data Values
        10. Retrieving Matching Data
        11. Hash Object Processing
        12. Creating a Hash Object From a SAS Data Set
        13. Using a Non-Executing SET Statement
        14. Working with Multiple Data Variables
        15. Retrieving Multiple Data Values
        16. Using Return Codes with the FIND Method
        17. Example
      7. Summary
        1. Text Summary
          1. Introduction
          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
        2. Syntax
          1. Using a Multidimensional Array
          2. Using Stored Array Values
          3. Using PROC TRANSPOSE and a Merge
          4. Using a Hash Object as a Lookup Table
        3. Sample Programs
          1. Using a Multidimensional Array
          2. Using Stored Array Values
          3. Using PROC TRANSPOSE and a Merge
          4. Using a Hash Object That Is Loaded From Hard-Coded Values
          5. Using a Hash Object That Is Loaded From a SAS Data Set
        4. Points to Remember
      8. Quiz
    5. 17. Formatting Data
      1. Overview
        1. Introduction
        2. Objectives
      2. Creating Custom Formats Using the VALUE Statement
        1. Review of Creating Non-Overlapping Formats
        2. Creating a Format with Overlapping Ranges
        3. Example
      3. Creating Custom Formats Using the PICTURE Statement
        1. Ways to Specify Pictures
        2. Example
        3. Guidelines for Specifying Directives
        4. Example
      4. Managing Custom Formats
        1. Using FMTLIB with PROC FORMAT to Document Formats
        2. Example
        3. Using PROC CATALOG to Manage Formats
        4. Example
      5. Using Custom Formats
        1. Example
        2. Using a Permanent Storage Location for Formats
        3. Example
        4. Substituting Formats to Avoid Errors
        5. Example
      6. Creating Formats from SAS Data Sets
        1. Example
        2. Rules for Control Data Sets
        3. Example
          1. Step 1: List Data Set Variables
          2. Step 2: Restructure the Data
        4. Apply the Format
      7. Creating SAS Data Sets from Custom Fomats
        1. Example
          1. Step 1: Create a SAS Data Set from the Format
          2. Step 2: Edit the Data Set
          3. Step 3: Create a Format from the SAS Data Set
      8. Summary
        1. Text Summary
          1. Creating Custom Formats Using the VALUE Statement
          2. Creating Formats with Overlapping Ranges
          3. Creating Custom Formats Using the PICTURE Statement
          4. Documenting Formats
          5. Managing Formats
          6. Using Custom Formats
          7. Creating Formats from SAS Data Sets
          8. Creating SAS Data Sets from Formats
        2. Syntax
        3. Sample Programs
          1. Creating a Multilabel Format
          2. Creating a Picture Format
          3. Creating a Picture Format Using Directives
          4. Restructuring a SAS Data Set and Creating a Format from the Data
          5. Creating a SAS Data Set from a Format
        4. Points to Remember
      9. Quiz
    6. 18. Modifying SAS Data Sets and Tracking Changes
      1. Overview
        1. Introduction
        2. Objectives
      2. Using the MODIFY Statement
      3. Modifying All Observations in a SAS Data Set
        1. Example
      4. Modifying Observations Using a Transaction Data Set
        1. Example
        2. Handling Duplicate Values
        3. Handling Missing Values
      5. Modifying Observations Located by an Index
        1. Example
        2. Handling Duplicate Values
      6. Controlling the Update Process
        1. Example
        2. Monitoring I/O Error Conditions
        3. Using _IORC_ with %SYSRC
        4. Example
      7. Understanding Integrity Constraints
        1. General Integrity Constraints
        2. Referential Integrity Constraints
      8. Placing Integrity Constraints on a Data Set
        1. Example
        2. How Constraints Are Enforced
        3. Example
        4. Copying a Data Set and Preserving Integrity Constraints
      9. Documenting Integrity Constraints
        1. Example
      10. Removing Integrity Constraints
        1. Example
      11. Understanding Audit Trails
      12. Initiating and Reading Audit Trails
        1. Example
        2. Reading Audit Trail Files
        3. Examples
      13. Controlling Data in the Audit Trail
        1. Data Set Variables
        2. Audit Trail Variables
        3. Values of the _ATOPCODE_ Variable
        4. Using the LOG Statement to Control the Data in the Audit Trail
        5. Example
        6. User Variables
        7. Example
      14. Controlling the Audit Trail
        1. Example
      15. Understanding Generation Data Sets
      16. Initiating Generation Data Sets
        1. Example
        2. Creating Generation Data Sets
      17. Processing Generation Data Sets
        1. Examples
        2. How Generation Numbers Change
        3. Examples
      18. Summary
        1. Text Summary
          1. Using the MODIFY Statement
          2. Modifying All Observations in a SAS Data Set
          3. Modifying Observations Using a Transaction Data Set
          4. Modifying Observations Located by an Index
          5. Controlling the Update Process
          6. Placing Integrity Constraints on a Data Set
          7. Documenting and Removing Integrity Constraints
          8. Initiating and Terminating Audit Trails
          9. Controlling Data in the Audit Trail
          10. Initiating Generation Data Sets
          11. Processing Generation Data Sets
        2. Syntax
          1. Modifying All Observations in a SAS Data Set
          2. Modifying a Master Data Set Using the BY Statement
          3. Modifying a Master Data Set Using a Transaction Data Set and an Index
          4. Controlling the Update Process
          5. Using PROC DATASETS to Create Integrity Constraints, Generation Data Sets, and Audit Trails
          6. Using _IORC_ with %SYSRC
          7. Specifying an Audit Trail File
          8. Using PROC DATASETS to Rename or Delete Generation Data Sets
        3. Sample Programs
          1. Modifying a Data Set Using the MODIFY Statement with a BY Statement and with the KEY= Option
          2. Placing Integrity Constraints on Data
          3. Initiating an Audit Trail
          4. Initiating Generation Data Sets
        4. Points to Remember
      19. Quiz
  6. 4. Optimizing SAS Programs
    1. 19. Introduction to Efficient SAS Programming
      1. Overview
        1. Introduction
        2. Objectives
      2. Overview of Computing Resources
      3. Assessing Efficiency Needs at Your Site
        1. Assessing Your Technical Environment
        2. Assessing Your Programs
        3. Assessing Your Data
      4. Understanding Efficiency Trade-offs
      5. Using SAS System Options to Track Resources
      6. Using Benchmarks to Compare Techniques
        1. Guidelines for Benchmarking
      7. Summary
          1. Overview of Computing Resources
          2. Assessing Efficiency Needs at Your Site
          3. Understanding Efficiency Trade-offs
          4. Using SAS System Options to Track Resources
          5. Using Benchmarks to Compare Techniques
    2. 20. Controlling Memory Usage
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Controlling Page Size and the Number of Buffers
        1. Measuring I/O
        2. Page Size
        3. Reporting Page Size
        4. Using the BUFSIZE= Option
        5. Using the BUFNO= Option
        6. Comparative Example: Using the BUFSIZE= Option and the BUFNO= Option
          1. Programming Techniques
          2. General Recommendations
      3. Using the SASFILE Statement
        1. Guidelines for Using the SASFILE Statement
        2. Comparative Example: Using the SASFILE Statement
          1. Programming Techniques
          2. General Recommendations
      4. Additional Features
        1. Using the IBUFSIZE= System Option
      5. Summary
          1. Controlling Page Size and the Number of Buffers
          2. Using the SASFILE Statement
          3. Additional Features
      6. Quiz
    3. 21. Controlling Data Storage Space
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Reducing Data Storage Space for Character Variables
        1. How SAS Assigns Lengths to Character Variables
        2. Reducing the Length of Character Data with the LENGTH Statement
        3. Other Techniques
      3. Reducing Data Storage Space for Numeric Variables
        1. How SAS Stores Numeric Variables
        2. Assigning Lengths to Numeric Variables
        3. Example
        4. Maintaining Precision in Reduced-Length Numeric Variables
        5. Using PROC COMPARE
        6. Example
        7. Comparative Example: Creating a SAS Data Set That Contains Reduced-Length Numeric Variables
          1. Programming Techniques
          2. General Recommendations
      4. Compressing Data Files
        1. Review of Uncompressed Data File Structure
        2. Compressed Data File Structure
        3. Deciding Whether to Compress a Data File
        4. The COMPRESS= System Option and the COMPRESS= Data Set Option
        5. Example
        6. Accessing Observations Directly in a Compressed Data Set
        7. The POINTOBS= Data Set Option
        8. Example
        9. The REUSE= System Option and the REUSE= Data Set Option
        10. Example
        11. How SAS Compresses Data
        12. Comparative Example: Creating and Reading Compressed Data Files
          1. Programming Techniques
          2. General Recommendations
      5. Using SAS DATA Step Views to Conserve Data Storage Space
        1. DATA Step Views
        2. Example
        3. The DESCRIBE Statement
        4. Creating and Referencing a SAS DATA Step View
        5. Referencing a Data View Multiple Times in One Program
        6. Example
        7. Making Multiple Passes Through Data in a Data View
        8. Creating Data Views on Unstable Data
        9. Comparative Example: Creating and Reading a SAS Data View
          1. Programming Techniques
          2. General Recommendations
      6. Summary
          1. Reducing Data Storage Space for Character Variables
          2. Reducing Data Storage Space for Numeric Variables
          3. Compressing Data Files
          4. Using SAS DATA Step Views to Conserve Data Storage Space
      7. Quiz
    4. 22. Using Best Practices
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Executing Only Necessary Statements
        1. Positioning of the Subsetting IF Statement
        2. Comparative Example: Creating a Subset of Data
          1. Programming Techniques
          2. General Recommendations
        3. Using Conditional Logic Efficiently
        4. Comparative Example: Creating Variables Conditionally Using DO Groups
          1. Programming Techniques
          2. General Recommendations
        5. Comparative Example: Creating Variables Conditionally When Calling Functions
          1. Programming Techniques
          2. General Recommendations
        6. Using DO Groups Efficiently
        7. Comparative Example: Creating Data in DO Groups
          1. Programming Techniques
          2. General Recommendations
      3. Eliminating Unnecessary Passes through the Data
        1. Using a Single DATA or PROC Step to Enhance Efficiency
        2. Using a Single DATA Step to Create Multiple Output Data Sets
        3. Using the SORT Procedure with a WHERE Statement to Create Sorted Subsets
        4. Using the DATASETS Procedure to Modify Variable Attributes
        5. Comparative Example: Creating Multiple Subsets of a SAS Data Set
          1. Programming Techniques
          2. General Recommendations
        6. Comparative Example: Creating a Sorted Subset of a SAS Data Set
          1. Programming Techniques
          2. General Recommendations
        7. Comparative Example: Changing the Variable Attributes of a SAS Data Set
          1. Programming Techniques
          2. General Recommendations
      4. Reading and Writing Only Essential Data
        1. Selecting Observations Using Subsetting IF versus WHERE Statement
        2. Comparative Example: Creating a Subset of a SAS Data Set
          1. Programming Techniques
          2. General Recommendations
        3. Other Differences between the IF and WHERE Statements
        4. Using the WHERE Statement with the OBS= and FIRSTOBS= Options
        5. Selecting Observations When Reading Data from External Files
        6. Comparative Example: Creating a Subset of Data by Reading Data from an External File
          1. Programming Techniques
          2. General Recommendations
        7. Subsetting Variables with the KEEP= and DROP= Statements and Options
        8. Comparative Example: Creating a Report That Contains Average and Median Statistics
          1. Programming Techniques
          2. General Recommendations
        9. Comparative Example: Creating a SAS Data Set That Contains Only Certain Variables
          1. Programming Techniques
          2. General Recommendations
      5. Storing Data in SAS Data Sets
        1. Comparative Example: Creating a SAS Data Set or Reading from an External File
          1. Programming Techniques
          2. General Recommendations
      6. Avoiding Unnecessary Procedure Invocation
        1. Executing the DATASETS Procedure
        2. RUN-Group Processing
        3. Using Different Types of RUN Groups with PROC DATASETS
        4. Comparative Example: Modifying the Descriptor Portion of SAS Data Sets
          1. Programming Techniques
          2. General Recommendations
      7. Summary
          1. Executing Only Necessary Statements
          2. Eliminating Unnecessary Passes Through the Data
          3. Reading and Writing Only Essential Data
          4. Storing SAS Data in SAS Data Sets
          5. Avoiding Unnecessary Procedure Invocation
      8. Quiz
    5. 23. Selecting Efficient Sorting Strategies
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Avoiding Unnecessary Sorts
        1. Using BY-Group Processing with an Index
        2. Comparative Example: Using BY-Group Processing with an Index to Avoid a Sort
          1. Programming Techniques
          2. General Recommendations
        3. Using the NOTSORTED Option
        4. Example
        5. Using FIRST. and LAST.
        6. Example
        7. Using the GROUPFORMAT Option
        8. Example
        9. Using the CLASS Statement
        10. Example
        11. Comparative Example: Using a BY or CLASS Statement to Avoid a Sort
          1. Programming Techniques
          2. General Recommendations
        12. Using the SORTEDBY= Data Set Option
        13. Example
      3. Using a Threaded Sort
        1. Using the CPUCOUNT= System Option
      4. Calculating and Allocating Sort Resources
        1. Sort Space Requirements
        2. Example
        3. Using the SORTSIZE= Option
      5. Handling Large Data Sets
        1. Dividing a Large Data Set
        2. Comparative Example: Dividing and Sorting a Large Data Set 1
          1. Programming Techniques
          2. General Recommendations
        3. Comparative Example: Dividing and Sorting a Large Data Set 2
          1. Programming Techniques
          2. General Recommendations
        4. Using the TAGSORT Option
        5. Example
      6. Removing Duplicate Observations Efficiently
        1. Using the NODUPKEY Option
        2. Example
        3. Using the NODUPRECS Option
        4. Example
        5. Using the EQUALS | NOEQUALS Option
        6. Example
        7. Using FIRST. LAST. Processing in the DATA Step
        8. Comparative Example: Removing Duplicate Observations Efficiently
          1. Programming Techniques
          2. Comparing Techniques to Eliminate Duplicate Data
          3. General Recommendations
      7. Additional Features
        1. Selecting a Host Sort Utility
        2. Using the SORTPGM= System Option
        3. Using the SORTCUTP= System Option
        4. Using the SORTCUT= System Option
        5. Using the SORTNAME= System Option
        6. Example
      8. Summary
          1. Avoiding Unnecessary Sorts
          2. Using a Threaded Sort
          3. Calculating and Allocating Sort Resources
          4. Handling Large Data Sets
          5. Removing Duplicate Observations Efficiently
          6. Additional Features
      9. Quiz
    6. 24. Querying Data Efficiently
      1. Overview
        1. Introduction
        2. Objectives
        3. Prerequisites
      2. Using an Index for Efficient WHERE Processing
        1. Accessing Data Sequentially
        2. Example
        3. Accessing Data Directly
        4. Example
        5. Benefits and Costs of Using an Index
        6. How SAS Selects an Access Method
      3. Identifying Available Indexes
        1. Example: Identifying One Available Index
        2. Example: Identifying Multiple Available Indexes
        3. Compound Optimization
        4. Example: Composite Index That Can Be Used to Optimize Multiple Conditions
        5. Example: Composite Index That Can Be Used to Optimize One Condition
        6. Example: Composite Index That Cannot Be Used for Optimizing
      4. Identifying Conditions That Can Be Optimized
        1. Requirements for Optimizing a Single WHERE Condition
        2. WHERE Conditions That Cannot Be Optimized
        3. Requirements for Compound Optimization
        4. Example: Compound Optimization
      5. Estimating the Number of Observations
        1. Printing Centile Information
        2. Example
      6. Comparing Probable Resource Usage
        1. How SAS Compares Resource Usage
        2. Factors That Affect I/O
        3. Subset Size Relative to Data Set Size
        4. Number of Pages in the Data File
        5. Order of the Data
        6. Cost to Uncompress a Compressed File for a Sequential Read
        7. Other Factors That Affect Resource Usage
      7. Deciding Whether to Create an Index
        1. Guidelines for Deciding Whether to Create an Index
        2. Example: Selecting Subsets of Various Sizes from Data Sets of Various Sizes
        3. Query 1: Small Subset from a Large Data Set
        4. Query 2: Large Subset from a Large Data Set
        5. Query 3: Small Subset from a Small Data Set
        6. Using the Options IDXWHERE= and IDXNAME= to Control Index Usage
        7. Specifying MSGLEVEL=I to Determine Whether SAS Is Using an Index
        8. Example: Using IDXWHERE=NO to Prevent Index Usage
      8. Comparing Procedures That Produce Detail Reports
        1. Example: Using PROC PRINT and PROC SQL to Create Detail Reports
        2. Report 1: Simple Detail Report
        3. Report 2: Subset Detail Report
        4. Report 3: Sorted Detail Report
        5. Report 4: Sorted Subset Detail Report
      9. Comparing Tools for Summarizing Data
        1. Comparing Resource Usage across Summarization Tools
        2. Comparative Example: Displaying Summary Statistics for One Class Variable
          1. Programming Techniques
          2. General Recommendations
        3. Using PROC MEANS to Display Summary Statistics for Combinations of Class Variables
        4. Comparing Resource Usage across Three Techniques for Using PROC MEANS
        5. Using a Basic PROC MEANS Step to Combine All Class Variables
        6. Example: Displaying Summary Statistics for All Combinations of the Class Variables
        7. Understanding Types
        8. Using the TYPES Statement in PROC MEANS to Combine Class Variables
        9. Example: Using the TYPES Statement in PROC MEANS
        10. Using the NWAY Option in PROC MEANS to Combine Class Variables
        11. Example: Using the NWAY Option in Multiple PROC MEANS Steps
        12. Using the WHERE= Option in PROC MEANS to Combine Class Variables
        13. Example: Using the WHERE= Option in PROC MEANS
        14. Comparative Example: Displaying Summary Statistics for Combinations of Class Variables
          1. Programming Techniques
          2. General Recommendations
        15. Additional Features
      10. Summary
          1. Using an Index for Efficient WHERE Processing
          2. Identifying Available Indexes
          3. Identifying Conditions That Can Be Optimized
          4. Estimating the Number of Observations
          5. Comparing Probable Resource Usage
          6. Deciding Whether to Create an Index
          7. Comparing Procedures That Produce Detail Reports
          8. Comparing Tools for Summarizing Data
      11. Quiz
  7. 5. Quiz Answer Keys
    1. 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 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

Product information

  • Title: SAS® Certification Prep Guide: Advanced Programming for SAS®9 Second Edition
  • Author(s): SAS Institute
  • Release date: June 2009
  • Publisher(s): SAS Institute
  • ISBN: 9781607640448