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