Home Page Icon
Home Page
Table of Contents for
Scenario 8
Close
Scenario 8
by SAS Institute
SAS Certified Professional Prep Guide
Title Page
Copyright
Using Sample Data
Accessibility Features of the Prep Guide
How to Prepare for the Exam
Chapter 1: PROC SQL Fundamentals
Chapter 1: PROC SQL Fundamentals
PROC SQL Basics
The PROC SQL SELECT Statement
The FROM Clause
The WHERE Clause
The GROUP BY Clause
The HAVING Clause
The ORDER BY Clause
PROC SQL Options
Validating Query Syntax
Quiz
End Notes
Chapter 2: Creating and Managing Tables
The CREATE TABLE Statement
Using the LIKE Clause
Using the AS Keyword
The INSERT Statement
The DESCRIBE TABLE Statement
Using Dictionary Tables
Chapter Quiz
Chapter 3: Joining Tables Using PROC SQL
Understanding Joins
Generating a Cartesian Product
Using Inner Joins
Using Natural Joins
Using Outer Joins
Comparing SQL Joins and DATA Step Match-Merges
Quiz
Chapter 4: Joining Tables Using Set Operators
Understanding Set Operators
Using the EXCEPT Set Operator
Using the INTERSECT Set Operator
Using the UNION Set Operator
Using the OUTER UNION Set Operator
Quiz
Chapter 5: Using Subqueries
Subsetting Data Using Subqueries
Creating and Managing Views Using PROC SQL
Quiz
Chapter 6: Advanced SQL Techniques
Creating Data-Driven Macro Variables with PROC SQL
Accessing DBMS Data with SAS/ACCESS
The FedSQL Procedure
Quiz
Chapter 7: Creating and Using Macro Variables
Chapter 7: Creating and Using Macro Variables
Introducing Macro Variables
The SAS Macro Facility
Using Macro Variables
Troubleshooting Macro Variable References
Delimiting Macro Variable References
Quiz
Chapter 8: Storing and Processing Text
Processing Text with Macro Functions
Using SAS Macro Functions to Manipulate Character Strings
Using SAS Functions with Macro Variables
Using SAS Macro Functions to Mask Special Characters
Creating Macro Variables during PROC SQL Step Execution
Creating Macro Variables during DATA Step Execution
Referencing Macro Variables Indirectly
Quiz
Chapter 9: Working with Macro Programs
Defining and Calling a Macro
Passing Information into a Macro Using Parameters
Controlling Variable Scope
Debugging Macros
Conditional Processing
Iterative Processing
Quiz
Chapter 10: Advanced Macro Techniques
Storing Macro Definitions in External Files
Understanding Session Compiled Macros
Using the Autocall Facility
Data-Driven Macro Calls
Quiz
Chapter 11: Defining and Processing Arrays
Chapter 11: Defining and Processing Arrays
Defining and Referencing One-Dimensional Arrays
Expanding Your Use of One-Dimensional Arrays
Defining and Referencing Two-Dimensional Arrays
Quiz
Chapter 12: Processing Data Using Hash Objects
Declaring Hash Objects
Defining Hash Objects
Finding Key Values in a Hash Object
Writing a Hash Object to a Table
Hash Object Processing
Using Hash Iterator Objects
Quiz
Chapter 13: Using SAS Utility Procedures
Creating Picture Formats with the FORMAT Procedure
Creating Functions with PROC FCMP
Quiz
Chapter 14: Using Advanced Functions
Using a Variety of Advanced Functions
Performing Pattern Matching with Perl Regular Expressions
Quiz
Chapter 15: Practice Programming Scenarios
Chapter 15: Practice Programming Scenarios
Differences between the Workbook and Certification Exam
Scenario 1
Scenario 2
Scenario 3
Scenario 4
Scenario 5
Scenario 6
Scenario 7
Scenario 8
Scenario 9
Scenario 10
Chapter 16: Chapter Quiz Answer Keys
Chapter 16: 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
Chapter 17: Programming Scenario Solutions
Scenario 1
Scenario 2
Scenario 3
Scenario 4
Scenario 5
Scenario 6
Scenario 7
Scenario 8
Scenario 9
Scenario 10
Recommended Reading
Index
Additional Resources
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
Scenario 7
Next
Next Chapter
Scenario 9
Scenario 8
Directions
This scenario uses the Certadv.Empdata and Certadv.Newsals data sets. Write an SQL query that does the following:
Create a temporary table Work.Raise.
Include the following variables in your report: EmpID, LastName, Salary, NewSalary, and Raise.
Join both Certadv.Empdata and Certadv.Newsals based on the EmpID column to display only matching rows of the data.
Create a new column named Raise whose value is the difference between NewSalary and Salary. Format the column as DOLLAR10.2
Display only the rows where the value of Raise is greater than 3000 and order the query by the EmpID.
Include the row numbers in your report.
Test Your Code
What is the employee’s last name where the value of Raise is
$5,141.86
?
What is the value of Raise for employee ID number
E0028
and last name of Lichtenstein?
Exam Objective
Generate detailed reports by working with a single, joining tables, or using set operators in SQL.
Last updated: October 16, 2019
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