SAS Certified Professional Prep Guide: Advanced Programming Using SAS 9. 4

Contents
How to Prepare for the Exam
	Requirements and Details
		Requirements
		Exam Objectives and Updates to This Book
		Take a Practice Exam
		Registering for the Exam
		Additional Resources for Learning SAS Programming
	Syntax Conventions
Using Sample Data
	SettingUp Practice Data for SAS OnDemand for Academics
	Setting Up Practice Data in Other SAS Environments
Accessibility Features of the Prep Guide
	Overview
	Accessibility Documentation Help
	Documentation Format
SQL Processing with SAS
	PROC SQL Fundamentals
		PROC SQL Basics
			What Is PROC SQL?
			PROC SQL Syntax
		The PROC SQL SELECT Statement
			A Brief Overview
			SELECT Statement Syntax
			Example: Selecting Columns
			Example: Displaying All Columns Using SELECT *
			Example: Using the FEEDBACK Option
			Example: Creating a New Column
			Example: Eliminating Duplicate Rows from Output
		The FROM Clause
			FROM Clause Syntax
			Example: Querying a Single Table Using the FROM Clause
		The WHERE Clause
			A Brief Overview
			WHERE Clause Syntax
			Example: Using the WHERE Clause
			Subsetting Rows by Using Calculated Values
			Example: Using Calculated Values in a SELECT Clause
			Subsetting Rows Using Conditional Operators
		The GROUP BY Clause
			A Brief Overview
			GROUP BY Clause Syntax
			Example: Determine Total Number of Miles Using the SUM Function
			Number of Argument and Summary Function Processing
			Groups and Summary Function Processing
			SELECT Clause Columns and Summary Function Processing
			Example: Using a Summary Function with a Single Argument (Column)
			Example: Using a Summary Function with Multiple Arguments (Columns)
			Example: Using a Summary Function with Columns outside  theFunction
			Example: Using a Summary Function with a GROUP BY Clause
			Counting Values by Using the COUNT Summary Function
			Example: Counting All Rows in a Table
			Example: Counting Rows within Groups of Data
			Counting All Nonmissing Values in a Column
			Example: Counting All Unique Values in a Column
			Example: Listing All Unique Values in a Column
		The HAVING Clause
			A Brief Overview
			HAVING Clause Syntax
			Subsetting Grouped Data
			Example: Selecting Groups by Using the HAVING Clause
			Understanding Data Remerging
		The ORDER BY Clause
			A Brief Overview
			ORDER BY Clause Syntax
			Example: Ordering Rows by the Values of a Single Column
			Example: Ordering by Multiple Columns
			Example: Ordering Columns by Position
		PROC SQL Options
			A Brief Overview
			PROC SQL Statement Syntax
			Using Invocation Options
		Validating Query Syntax
			A Brief Overview
			Example: Using the NOEXEC Option
			Example: Using the VALIDATE Keyword
		Quiz
	Creating and Managing Tables
		The CREATE TABLE Statement
			A Brief Overview
			CREATE TABLE Statement Syntax
			Example: Creating an Empty Table by Defining Column Structure
			Specifying Data Types for Columns
			Specifying Column Widths
			Specifying Column Modifiers
			Example: Using Column Modifiers
		Using the LIKE Clause
			LIKE Clause Syntax
			Example: Creating an Empty Table That Is like Another
		Using the AS Keyword
			AS Keyword Syntax
			Example: Creating a Table from a Query Result
		The INSERT Statement
			A Brief Overview
			The SET Clause
			The VALUES Clause
			The INSERT Statement with SELECT and FROM Clauses
		The DESCRIBE TABLE Statement
			A Brief Overview
			DESCRIBE TABLE StatementSyntax
			Example: Displaying the Structure of a Table
		Using Dictionary Tables
			A Brief Overview
			Example: Exploring and Using Dictionary Tables
		Chapter Quiz
	Joining Tables Using PROC SQL
		Understanding Joins
		Generating a Cartesian Product
		Using Inner Joins
			A Brief Overview
			Inner Join Syntax
			Understanding How Joins Are Processed
			Example:  Using a FROM Clause with the INNER JOIN Keyword
			Example: Eliminating Duplicate Columns
			Example: Renaming a Column by Using a Column Alias
			Example: 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 Natural Joins
			A Brief Overview
			Natural Join Syntax
			Example: Using a Natural Join
		Using Outer Joins
			A Brief Overview
			Outer Join Syntax
			Example: Using a Left Outer Join
			Example: Eliminating Duplicate Columns in a Left Outer Join
			Example: Using a Right Outer Join
			Example: Using a Full Outer Join
			Example: Complex Outer Join
		Comparing SQL Joins and DATA Step Match-Merges
			A Brief Overview
			When All of the Values Match
			When Only Some of the Values Match
			COALESCE Function Syntax
			Example: Using the COALESCE Function
			Understanding the Advantages of PROC SQL Joins
		Quiz
	Joining Tables Using Set Operators
		Understanding Set Operators
			Introducing Set Operators
			Set Operator Syntax
			Example: The Basics of Using a Set Operator
			Using Multiple Set Operators
			Processing Unique versus Duplicate Rows
			Combining and Overlaying Columns
			Modifying Results Using Keywords
		Using the EXCEPT Set Operator
			A Brief Overview
			Example: Using the EXCEPT Operator Alone
			Example: Using the Keyword ALL with the EXCEPT Operator
			Example: Using the  Keyword CORR with the EXCEPT Operator
			Example: Using the Keywords ALL and CORR  with the EXCEPT Operator
			Example: EXCEPT Operator
		Using the INTERSECT Set Operator
			A Brief Overview
			Example: Using the INTERSECT Operator Alone
			Example: Using the  Keyword ALL with the INTERSECT Set Operator
			Example: Using the  Keyword CORR with the INTERSECT Set Operator
			Example: Using the Keywords ALL and CORR with the INTERSECTSet Operator
			Complex Example Using the INTERSECT Operator
		Using the UNION Set Operator
			A Brief Overview
			Example: Using the UNION Operator Alone
			Example: Using the Keyword ALL with the UNION Operator
			Example: Using the Keyword CORR with the UNION Operator
			Example: Using the Keywords ALL and CORR with the UNION Operator
			Example: Using the UNION Set Operator
			Example: Using a UNION Operator and Summary Functions
		Using the OUTER UNION Set Operator
			A Brief Overview
			Example: Using the OUTER UNION Operator Alone
			Example: Using the  Keyword CORR with One OUTER UNION Operator
			Example: Using Two OUTER UNION Operators with the Keyword CORR
		Quiz
	Using Subqueries
		Subsetting Data Using Subqueries
			Introducing Subqueries
			Subsetting Data by Using Noncorrelated Subqueries
			Subsetting Data by Using Correlated Subqueries
		Creating and Managing Views Using PROC SQL
			A Brief Overview
			The CREATE VIEW Statement
			The DESCRIBE VIEW Statement
			Managing PROC SQL Views
			Updating PROC SQL Views
			The DROP VIEW Statement
		Quiz
	Advanced SQL Techniques
		CreatingData-Driven Macro Variables with PROC SQL
			Creating Data-Driven Macro Variables with  the INTO Clause
			Displaying Macro Variable Values
			Removing Leading and Trailing Blanks
			Concatenating Values in Macro Variables
			Applying a Format to Character and Numeric Variables
		Accessing DBMS Data with SAS/ACCESS
			A Brief Overview
			SQL Pass-Through Facility
			SAS/ACCESS LIBNAME Engine
		The FedSQL Procedure
			A Brief Overview
			Comparing PROC FedSQL and PROC SQL
			PROC FEDSQL Syntax
			The LIBNAME Statement
			The LIMIT Clause
			The PUT Function
			System Options in FedSQL
		Quiz
SAS Macro Language Processing
	Creating and Using Macro Variables
		Introducing Macro Variables
			A Brief Overview
			Example: Using Macro Variables
		The SAS Macro Facility
			SAS Processing of Macros
			Tokenization
			Macro Triggers
		Using Macro Variables
			Using User-Defined Macro Variables
			Using Automatic Macro Variables
		Troubleshooting Macro Variable References
			SYMBOLGEN Option Syntax
			Example: Using the SYMBOLGEN Option
			%PUT Statement Syntax
			Example: Using the %PUT Statement
			The %SYMDEL Statement
		Delimiting Macro Variable References
			A Brief Overview
			Example: Using a Delimiter to Reference Macro Variables
		Quiz
	Storing and Processing Text
		Processing Text with Macro Functions
		Using SAS Macro Functions to Manipulate Character Strings
			Macro Character Functions
			The %UPCASE Function
			The %SUBSTR Function
			The %INDEX Function
			The %SCAN Function
		Using SAS Functions with Macro Variables
			The %SYSFUNC Function
			The %EVAL Function
			The %SYSEVALF Function
		Using SAS Macro Functions to Mask Special Characters
			Macro Quoting Functions
			The %STR Function
			The %NRSTR Function
			The %SUPERQ Function
			The %BQUOTE Function
			Macro Q Functions
		Creating Macro Variables during PROC SQL Step Execution
			INTO Clause and the NOPRINT Option Syntax
			Example: Using the INTO Clause and the NOPRINT Option
			Example: Creating Variables with the INTO Clause
			Example: Creating a Delimited List of Values
		Creating Macro Variables during DATA Step Execution
			The CALL SYMPUTX Routine
			The PUT Function
		Referencing Macro Variables Indirectly
			Indirect References
			Example: Referencing Macro Variables Indirectly
			Example: Creating a Series of Macro Variables
		Quiz
	Working with Macro Programs
		Defining and Calling a Macro
			Defining a Macro
			Compiling a Macro
			Calling a Macro
			Macro Execution
		Passing Information into a Macro Using Parameters
			Macros That Include Positional Parameters
			Example: Using Positional Parameters to Create Macro Variables
			Macros That Include Keyword Parameters
			Example: Using Keyword Parameters to Create Macro Variables
			Macros That Include Mixed Parameter Lists
			Example: Using Mixed Parameters to Create Macro Variables
		Controlling Variable Scope
			Scope of Macro Variables
			The %GLOBAL Statement
			The %LOCAL Statement
			Nested Scope
		Debugging Macros
			The MPRINT System Option
			Comments in Macro Programs
		Conditional Processing
			The %IF-%THEN Statement and %ELSE Statement
			The %DO-%END Statement with the %IF-%THEN Statement
			Example: Using %IF-%THEN, %DO-%END with IF-THEN Statements
			Example: Controlling Text Copied to the Input Stack
			The MLOGIC System Option
		Iterative Processing
			The %DO Statement
			Example: Using the %DO Statement
			Example: Generating Complete Steps
		Quiz
	Advanced Macro Techniques
		Storing Macro Definitions in External Files
			The %INCLUDE Statement
			Example: Using the %INCLUDE Statement
		Understanding Session Compiled Macros
		Using the Autocall Facility
			A Brief Overview
			Creating an Autocall Library
			Example: Saving a Macro
			Default Autocall Library
			Example: Using the LOWCASE Macro
			Accessing Autocall Macros
		Data-Driven Macro Calls
			A Brief Overview
			The DOSUBL Function
			Example: Using the DOSUBL Function
		Quiz
Advanced SAS Programming Techniques
	Defining and Processing Arrays
		Defining and Referencing One-Dimensional Arrays
			A Brief Overview
			ARRAY Statement Syntax
			Defining the Number of Elements
			Specifying the Array Elements
			Using Column Lists as Array Elements
			Referencing a One-Dimensional Array
			DO Statement Syntax
			Example: Processing Repetitive Code
			Handling an Unknown Number of Array Elements
			Using the DIM Function
			Compilation and Execution Phases for Array Processing
		Expanding Your Use of One-Dimensional Arrays
			Creating Character Columns with an Array
			Specifying Lower and Upper Bounds
			Assigning Initial Values to Arrays
			Example: Assigning Initial Values to Arrays
			Specifying Temporary Array Elements
			Example: Rotating Data
		Defining and Referencing Two-Dimensional Arrays
			A Brief Overview
			Example: Creating a Two-Dimensional Array with Initial Values
			Example: Creating a Two-Dimensional Array to Perform TableLookup
		Quiz
	Processing Data Using Hash Objects
		Declaring Hash Objects
			A Brief Overview
			Key Components
			Data Components
			Declaring a Hash Object
			Hash Object Process
		Defining Hash Objects
			Hash Object Methods
			Defining a Hash Object
			Example: Defining a Hash Object
		Finding Key Values in a Hash Object
			The FIND Method
			Retrieving Matching Data
		Writing a Hash Object to a Table
			The ADD Method
			Example: Adding Key and Data Values
			The OUTPUT Method
			Controlling Output with the OUTPUT Method
			Avoiding Messages in the SAS Log
		Hash Object Processing
		Using Hash Iterator Objects
			What Is a Hash Iterator Object?
			Declaring and Defining a Hash Object and a Hash Iterator Object
			Retrieving Hash Object Data with the Hash Iterator Object
			Example: Using the Hash Iterator Object
		Quiz
	Using SAS Utility Procedures
		Creating Picture Formats with the FORMAT Procedure
			A Brief Overview
			PICTURE Statement Syntax
			Example: Using the PICTURE Statement
			Creating Custom Date, Time, and Datetime Formats Using Directives
			Specifying Date and Time Directives
			Example: Using Directives
			Creating Custom Numeric Formats
		Creating Functions with PROC FCMP
			A Brief Overview
			PROC FCMP Syntax
			CMPLIB= Option Syntax
			Example: Creating a Custom Character Function with One Argument
			Example: Creating a Custom Numeric Function with One Argument
			Example: Creating a Custom Character Function with MultipleArguments
		Quiz
	Using Advanced Functions
		Using a Variety of Advanced Functions
			The LAG Function
			The COUNT/COUNTC/COUNTW Function
			The FIND/FINDC/FINDW Function
		Performing Pattern Matching with Perl Regular Expressions
			A Brief Overview
			Using Metacharacters
			Example: Using Metacharacters
			The PRXMATCH Function
			The PRXPARSE Function
			The PRXCHANGE Function
		Quiz
Workbook
	Practice Programming Scenarios
		Differencesbetween the Workbook and Certification Exam
		Scenario 1
			Directions
			Test Your Code
			Exam Objective
		Scenario 2
			Directions
			Test Your Code
			Exam Objective
		Scenario 3
			Directions
			Test Your Code
			Exam Objective
		Scenario 4
			Directions
			Test Your Code
			Exam Objective
		Scenario 5
			Directions
			Test Your Code
			Exam Objective
		Scenario 6
			Directions
			Test Your Code
			Exam Objective
		Scenario 7
			Directions
			Test Your Code
			Exam Objective
		Scenario 8
			Directions
			Test Your Code
			Exam Objective
		Scenario 9
			Directions
			Test Your Code
			Exam Objective
		Scenario 10
			Directions
			Test Your Code
			Exam Objective
Solutions
	Chapter Quiz Answer Keys
		Chapter 1: PROC SQL Fundamentals
		Chapter 2: Creating and Managing Tables
		Chapter 3: Joining Tables  Using PROC SQL
		Chapter 4: Joining Tables  Using Set Operators
		Chapter 5: Using Subqueries
		Chapter 6: Advanced SQL Techniques
		Chapter 7: Creating and Using Macro Variables
		Chapter 8: Storing and Processing Text
		Chapter 9: Working with Macro Programs
		Chapter 10: Advanced Macro Techniques
		Chapter 11: Defining and Processing Arrays
		Chapter 12: Processing Data Using Hash Objects
		Chapter 13: Using SAS Utility Procedures
		Chapter 14: Using Advanced Functions
	Programming Scenario Solutions
		Scenario 1
			Code Solution
			Test Your Code Solution
		Scenario 2
			Code Solution
			Test Your Code Solution
		Scenario 3
			Code Solution
			Test Your Code Solution
		Scenario 4
			Code Solution
			Test Your Code Solution
		Scenario 5
			Code Solution
			Test Your Code Solution
		Scenario 6
			Code Solution
			Test Your Code Solution
		Scenario 7
			Code Solution
			Test Your Code Solution
		Scenario 8
			Code Solution
			Test Your Code Solution
		Scenario 9
			Code Solution
			Test Your Code Solution
		Scenario 10
			Code Solution
			Test Your Code Solution
Recommended Reading
Index