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