Note: On the live exam,
you will be evaluated both on the results of your code and the code
itself. Your code should be similar to the following example code,
but does not need to match exactly:
proc sql number; /*1*/
select empdata.empID, /*2*/
empdata.LastName,
empdata.Salary,
newsals.NewSalary,
(newsals.NewSalary-empdata.Salary) /*3*/
as Raise format=dollar10.2
from certadv.empdata inner join /*4*/
certadv.newsals
on empdata.EmpID= newsals.EmpID /*5*/
where calculated Raise>3000 /*6*/
order by EmpID; /*7*/
quit;
1 |
The SQL procedure retrieves data
from tables or views to generate a report. The PROC SQL option NUMBER
prints the row numbers in the query output. |
2 |
The SELECT statement retrieves
the columns EmpID, LastName, and Salary from Certadv.Empdata and NewSalary
from Certadv.Newsals and displays the column values in the query result. |
3 |
The SELECT statement also creates
a column alias by using the AS keyword followed by the column name
of Raise. The value of Raise is the difference between the column
NewSalary from Certadv.Newsals and Salary from Certadv.Empdata. The
DOLLAR 10.2 format is applied to the new column, Raise. |
4 |
The FROM clause uses an INNER JOIN
keyword to join Certadv.Empdata and Certadv.Newsals. |
5 |
The ON clause specifies the column,
EmpID, to be used when joining Certadv.Empdata and Certadv.Newsals. |
6 |
The calculated keyword tells PROC
SQL that the value is calculated within the query. The WHERE clause
subsets the data to include only the observations where the value
of Raise is greater than 3000. |
7 |
The ORDER BY clause sorts the rows
by EmpID. |
Output 17.11 PROC SQL Query Result