Scenario 10

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;
   create view work.phonelist as                     /*1*/
      select Department format=$25.,                 /*2*/
             EmployeeName as Name format=$25.,
             PhoneNumber 'Home Phone' format=$16.
      from certadv.empadd as a                       /*3*/
           inner join
           certadv.empph as p
           on a.EmployeeID = p.EmployeeID
           inner join
           certadv.emporg as o
           on o.EmployeeID = p.EmployeeID
      where PhoneType='Home';                        /*4*/
quit;

proc sql number;                                     /*5*/
title 'Sales Management Department Home Phone Numbers';
   select Name, PhoneNumber
      from work.phonelist                            /*6*/
      where Department='Sales Management'            /*7*/
      order by Name;                                 /*8*/
quit;
1 The CREATE VIEW statement creates the view that contains information about the employees’ names, departments, and phone numbers. The view Work.PhoneList creates a virtual table from the accompanying SELECT statement. Although the underlying tables, Certadv.Empadd, Certadv.Empph, and Certadv.Org, can change, the instructions that comprise the view stay constant.
2 The SELECT statement selects three variables:
  • Department with a format of $25.
  • EmployeeName with a column alias of Name and a format of $25.
  • PhoneNumber with a label of Home Phone and a format of $16.
3 The FROM clause specifies Certadv.Empadd as the first source table with as alias of A. The INNER JOIN keyword is used to specify the type of join. Certadv.Empph is specified as the secondary source table with an alias of P. The ON keyword specifies the column on which the join occurs. The ON keyword specifies that from source table A, the values of EmployeeID must match the values of EmployeeID from source table P. Another INNER JOIN keyword is used to specify the type of join. Certadv.Emporg is specified as the third source table with an alias of O. The ON keyword specifies that from source table O, the value of EmployeeID must match the values of EmployeeID from source table P.
4 The WHERE clause subsets the data to include only those observations where the PhoneType is Home.
5 The SQL procedure retrieves data from tables or views to generate a report. The SQL option NUMBER prints the row numbers in the query output. The SQL procedure prints the title 'Sales Management Department Home Phone Numbers'. The SELECT statement includes Name and PhoneNumber in the result.
6 The FROM clause specifies Work.PhoneList as the view to be queried.
7 The WHERE clause specifies to subset the data based on the condition that only those in the Sales Management department are in the query result.
8 The ORDER BY clause orders the rows by EmployeeName.
Output 17.13 PROC SQL Query Result
PROC SQL Query Result

Test Your Code Solution

  1. Correct Answer: 11
  2. Correct Answer: Lu, Patrick
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