Scenario 9

Code Solution

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
PROC SQL Query Result

Test Your Code Solution

  1. Correct Answer: 34
  2. Correct Answer: $1,377,442
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset