SAS Certified Professional Prep Guide

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

1. What is the employee’s last name where the value ...