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; /*1*/
select catx(" ",'Total Paid to All',JobTitle,'Staff'), /*2*/
sum(Salary) format=dollar14., /*3*/
count(*) as TotalCount format=comma16. /*4*
from certadv.salesstaff /*5*/
group by JobTitle; /*6*/
quit;
1 |
The SQL procedure retrieves data
from tables or views to generate a report. |
2 |
The SELECT statement uses the CATX
function to return a character string that combines 'Total
Paid to All' , the value of JobTitle, and 'Staff' .
For example, for rows where JobTitle is Sales Rep. I, the concatenated
string is 'Total Paid to All Sales Rep. I Staff' . |
3 |
The SELECT statement uses the SUM
function to calculate the total amount of salaries paid to each unique
value for JobTitle. The FORMAT statement formats the value of the
SUM function as DOLLAR14. |
4 |
The SELECT statement uses the COUNT
function to count the total number of sales representatives in each
unique value for JobTitle. The COUNT function counts the number of
rows and creates a column named TotalCount using the AS keyword. |
5 |
The FROM clause specifies Certadv.Salesstaff
as the source table to be queried. |
6 |
The GROUP BY clause groups rows
that have the same values into summary rows. The GROUP BY clause groups
all unique values for JobTitle into one row. For example, Sales Rep.
I is one row, Sales Rep. II is another, and so on. |
Output 17.12 PROC SQL Query Result