1. Which of the clauses in the PROC SQL program below is written incorrectly?
proc sql;
select style sqfeet bedrooms
from choice.houses
where sqfeet ge 800;
a. SELECT
b. FROM
c. WHERE
d. both a and c
Correct answer: a
The SELECT clause in the program is written incorrectly. Columns that are listed in the clause must be separated by commas, not just blanks.
2. How many statements does the program below contain?
proc sql;
select grapes,oranges,
grapes + oranges as sumsales
from sales.produce
order by sumsales;
a. two
b. three
c. four
d. five
Correct answer: a
There are two statements, the PROC SQL statement and the SELECT statement. The SELECT statement contains three clauses.
3. Complete the following PROC SQL query to select the columns Address and SqFeet
from the table List.Size and to select Price from the table List.Price. (Only the
Address column appears in both tables.)
proc sql;
_____________
where size.address = price.address;
from list.size,list.price;
a. select address,sqfeet,price
b. select size.address,sqfeet,price
c. select price.address,sqfeet,price
d. either b or c
Correct answer: b
The SELECT clause lists the columns from both tables to be queried. You must use a prefix with the Address column because it appears in both tables. The prefix specifies the table from which you want the column to be read.
4. Which of the clauses below correctly sorts rows by the values of the columns Price
and SqFeet?
a. order price, sqfeet
b. order by price,sqfeet
c. sort by price sqfeet
d. sort price sqfeet
Correct answer: b
The ORDER BY clause specifies how the rows are to be sorted. You follow the keywords ORDER BY by one or more column names or numbers, separated by commas.
5. Which clause below specifies that the two tables Produce and Hardware be queried?
Both tables are located in a library to which the libref Sales has been assigned.
a. select sales.produce sales.hardware
b. from sales.produce sales.hardware
c. from sales.produce,sales.hardware
d. where sales.produce, sales.hardware
Correct answer: c
In the FROM clause, you list the names of the tables to be queried, separated by commas.
6. Complete the SELECT clause below to create a new column named Profit by
subtracting the values of the column Cost from those of the column Price.
select fruit,cost,price,
________________
a. Profit=price-cost
b. price-cost as Profit
c. profit=price-cost
d. Profit as price-cost
7. What happens if you use a GROUP BY clause in a PROC SQL step without a
summary function?
a. The step does not execute.
b. The first numeric column is summed by default.
c. The GROUP BY clause is changed to an ORDER BY clause.
d. The step executes but does not group or sort data.
Correct answer: c
The GROUP BY clause is used in queries that include one or more summary functions. If you specify a GROUP BY clause in a query that does not contain a summary function, your clause is changed to an ORDER BY clause.
8. If you specify a CREATE TABLE statement in your PROC SQL step,
a. the results of the query are displayed, and a new table is created.
b. a new table is created, but it does not contain any summarization that was
specified in the PROC SQL step.
c. a new table is created, but no report is displayed.
d. results are grouped by the value of the summarized column.
Correct answer: c
The CREATE TABLE statement enables you to store your results in a SAS table instead of displaying the query results as a report.
9. Which statement is true regarding the use of the PROC SQL step to query data that is
stored in two or more tables?
a. When you join multiple tables, the tables must contain a common column.
b. You must specify the table from which you want each column to be read.
c. The tables that are being joined must be from the same type of data source.
d. If two tables that are being joined contain a same-named column, then you must specify the table from which you want the column to be read.
Correct answer: d
If you are joining two tables that contain a same-named column, then you must use a prefix to specify the table(s) from which you want the column to be read. Remember that if you join tables that don't contain columns that have matching data values, you can produce a huge amount of output. Be sure to specify a WHERE clause to select only the rows that you want.
10. Which clause in the following program is incorrect?
proc sql;
select sex,mean(weight) as avgweight
from company.employees company.health
where employees.id=health.id
group by sex;
a. SELECT
b. FROM
c. WHERE
d. GROUP BY
Correct answer: b
The table names that are specified in the FROM clause must be separated by commas. Note that you can specify columns in the WHERE clause that are not specified in the SELECT clause.
-----------------------------------------------------------------------------
