Chapter 3. Combining Tables Horizontally Using PROC SQL

1. Given the PROC SQL query and tables shown below, which output is generated?

This PROC SQL query is a right outer join, which retrieves all rows that match across tables, based on the join conditions in the ON clause, plus nonmatching rows from the right (second) table.

Explanation

This PROC SQL query is a right outer join, which retrieves all rows that match across tables, based on the join conditions in the ON clause, plus nonmatching rows from the right (second) table.

2. Which output will the following PROC SQL query generate?

This PROC SQL query is a left outer join, which retrieves all rows that match across tables (based on the join conditions in the ON clause), plus nonmatching rows from the left (first) table. No columns are overlaid, so all columns from both tables are displayed.

Explanation

This PROC SQL query is a left outer join, which retrieves all rows that match across tables (based on the join conditions in the ON clause), plus nonmatching rows from the left (first) table. No columns are overlaid, so all columns from both tables are displayed.

3. A Cartesian product is returned when

Join conditions are not specified in a PROC SQL join.

Join conditions are not specified in a PROC SQL set operation.

More than two tables are specified in a PROC SQL join.

The keyword ALL is used with the OUTER UNION operator.

A Cartesian product is returned when join conditions are not specified in a PROC SQL join. In a Cartesian product, each row from the first table is combined with every row from the second table.

Explanation

A Cartesian product is returned when join conditions are not specified in a PROC SQL join. In a Cartesian product, each row from the first table is combined with every row from the second table.

4. Given the PROC SQL query and tables shown below, which output is generated?

This PROC SQL query is an inner join. It combines the rows from the first table that match rows from the second table, based on the matching criteria specified in the WHERE clause. Columns are not overlaid, so all columns from the referenced tables (including any columns with duplicate names) are displayed. Any unmatched rows from either table are not displayed.

Explanation

This PROC SQL query is an inner join. It combines the rows from the first table that match rows from the second table, based on the matching criteria specified in the WHERE clause. Columns are not overlaid, so all columns from the referenced tables (including any columns with duplicate names) are displayed. Any unmatched rows from either table are not displayed.

5. In order for PROC SQL to perform an inner join,

The tables being joined must contain the same number of columns.

The tables must be sorted before they are joined.

The columns that are specified in a join condition in the WHERE clause must have the same data type.

The columns that are specified in a join condition in the WHERE clause must have the same name.

Inner joins combine the rows from the first table that match rows from the second table, based on one or more join conditions in the WHERE clause. The columns being matched must have the same data type, but they are not required to have the same name. For joins, the tables being joined can have different numbers of columns, and the rows do not need to be sorted.

Explanation

Inner joins combine the rows from the first table that match rows from the second table, based on one or more join conditions in the WHERE clause. The columns being matched must have the same data type, but they are not required to have the same name. For joins, the tables being joined can have different numbers of columns, and the rows do not need to be sorted.

6. Which statement about the use of table aliases is false?

Table aliases must be used when referencing identical table names from different libraries.

Table aliases can be referenced by using the keyword AS.

Table aliases must be used when using summary functions.

The use of summary functions does not require the use of table aliases. All of the other statements about table aliases that are shown here are true.

Explanation

The use of summary functions does not require the use of table aliases. All of the other statements about table aliases that are shown here are true.

7. Which PROC SQL query produces the same output as the query shown here?

proc sql;
    select a.*,
            duration
        from groupa as a,
            groupb as b
        where a.obs=b.obs;

NOTE Assume that the table Groupa contains the columns Obs and Med. Groupb contains the columns Obs and Duration. Δ

There are two valid formats for writing a PROC SQL inner join. The PROC SQL query shown at the top of this question uses the first inner join format, which does not use a keyword to indicate the type of join. The alternate format is similar to an outer join and uses the keyword INNER JOIN.

Explanation

There are two valid formats for writing a PROC SQL inner join. The PROC SQL query shown at the top of this question uses the first inner join format, which does not use a keyword to indicate the type of join. The alternate format is similar to an outer join and uses the keyword INNER JOIN.

8. Which PROC SQL query will generate the same output as the DATA step match-merge and PRINT step shown below?

In order to generate the same output as the DATA step and PRINT steps, the PROC SQL full outer join must use the COALESCE function with the duplicate columns specified as arguments.

Explanation

In order to generate the same output as the DATA step and PRINT steps, the PROC SQL full outer join must use the COALESCE function with the duplicate columns specified as arguments.

9. A PROC SQL inner join can combine

A maximum of two tables or in-line views, but multiple joins can be chained together.

A maximum of 32 tables or two in-line views.

A maximum of 32 tables, which includes any tables referenced by an in-line view.

A maximum of two tables and 32 columns.

A maximum of 32 tables can be combined in a single inner join. If the join involves views (either in-line views or PROC SQL views), it is the number of tables that underlie the views, not the number of views, that counts towards the limit of 32.

Explanation

A maximum of 32 tables can be combined in a single inner join. If the join involves views (either in-line views or PROC SQL views), it is the number of tables that underlie the views, not the number of views, that counts towards the limit of 32.

10. Which statement about in-line views is false?

Once defined, an in-line view can be referenced in any PROC SQL query in the current SAS session.

An in-line view can be assigned a table alias but not a permanent name.

In-line views can be combined with tables in PROC SQL joins.

Unlike a table, an in-line view exists only during query execution. Because it is temporary, an in-line view can be referenced only in the query in which it is defined.

Explanation

Unlike a table, an in-line view exists only during query execution. Because it is temporary, an in-line view can be referenced only in the query in which it is defined.

Chapter 3. Combining Tables Horizontally Using Proc SQL