Chapter 1: Performing Queries Using Proc SQL

1. Which of the clauses below correctly sorts rows by the values of the columns Price and SqFeet?

Order price, sqfeet

Order by price, sqfeet

Sort by price sqfeet

Sort price sqfeet

The ORDER BY clause specifies how the rows are to be sorted. You follow the keywords ORDER BY one or more column names or numbers, separated by columns.

Explanation

The ORDER BY clause specifies how the rows are to be sorted. You follow the keywords ORDER BY one or more column names or numbers, separated by columns.

2. Which of the following clauses in the PROC SQL program below is written incorrectly?

proc sql;
select style sqfeet
bedrooms
from choice.houses
where sqfeet ge 800;

SELECT

FROM

WHERE

Both a and c

The SELECT clause in the program is written incorrectly. Columns that are listen in the clause must be separated by commas, not just blanks.

Explanation

The SELECT clause in the program is written incorrectly. Columns that are listen in the clause must be separated by commas, not just blanks.

3. 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.

Select sales.produce sales.hardware

From sales.produce sales.hardware

From sales.produce, sales.hardware

Where sales.produce, sales.hardware

In the FROM clause, you list the names of the tables to be queried, separated by commas.

Explanation

In the FROM clause, you list the names of the tables to be queried, separated by commas.

4. 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, _____________

Profit=price-cost

Price-cost as Profit

Profit= price-cost

Profit as price-cost

To create a new column and assign a column alias to the column, you specify the following in the SELECT clause, in the order shown here: an expression, (optionally) the keyword AS, and column alias. The case that you use when you create the column name is the one that will be displayed in the output.

Explanation

To create a new column and assign a column alias to the column, you specify the following in the SELECT clause, in the order shown here: an expression, (optionally) the keyword AS, and column alias. The case that you use when you create the column name is the one that will be displayed in the output.

5. 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;

SELECT

FROM

WHERE

GROUP BY

Tables in the from statement must be separated by a comma (,)

Explanation

Tables in the from statement must be separated by a comma (,)

6. What happens if you use a GROUP BY clause in a PROC SQL step without a summary function?

The step does not execute.

The first numeric column is summed by default.

The GROUP BY clause is changed to an ORDER BY clause

The step executes but does not group or sort data.

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, it is changed to an ORDER BY clause.

Explanation

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, it is changed to an ORDER BY clause.

7. How many statements does the program below contain?

proc sql;
select grapes, oranges, grapes + oranges as
sumsales
from sales.produce
order by sumsales;

Two

Three

Four

Five

There are two statements, the PROC SQL statement and the SELECT statement. The SELECT statement contains three clauses.

Explanation

There are two statements, the PROC SQL statement and the SELECT statement. The SELECT statement contains three clauses.

8. If you specify a CREATE TABLE statement in your PROC SQL step,

The results of the query are displayed, and a new table is created.

A new table is created, but it does not contain any summarization that was specified in the PROC SQL step.

A new table is created, but no report is displayed.

Results are grouped by the value of the summarized column.

The CREATE TABLE statement enables you to store your results in a SAS table instead of displaying the query results as a report.

Explanation

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?

When you join multiple tables, the tables must contain a common column.

You must specify the table from which you want each column to be read.

The tables that are being joined must be from the same type of data source.

When using the PROC SQL step to query data from multiple tables, if there are two tables being joined that have a column with the same name, you must specify the table from which you want to retrieve the column. This is necessary to avoid any ambiguity and ensure that the correct column is selected from the appropriate table.

Explanation

When using the PROC SQL step to query data from multiple tables, if there are two tables being joined that have a column with the same name, you must specify the table from which you want to retrieve the column. This is necessary to avoid any ambiguity and ensure that the correct column is selected from the appropriate table.

10. 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.Proce. (Only the Address column appears in both tables.)

proc sql;
___________ where size.address = price.address; from list.size, list.price;

Select address, sqfeet, price

Select size.address, sqfeet, price

Select price.address, sqfeet, price

Either B or C

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.

Explanation

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.