Chapter 3. Combining Tables Horizontally Using Proc SQL

Approved & Edited by ProProfs Editorial Team

The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.

Learn about Our Editorial Process

| By Moxleyv

Moxleyv

Community Contributor

Quizzes Created: 38 | Total Attempts: 20,578

Questions: 10 | Attempts: 151 | Updated: Mar 20, 2023

Questions

Settings

Create your own Quiz

Share on Facebook Share on Twitter Share on Whatsapp Share on Pinterest Share on Email Copy to Clipboard Embed on your website

Chapter 3. Combining Tables Horizontally Using Proc SQL - Quiz

Practice for the SAS advance certification exam.

Questions and Answers

1.

A Cartesian product is returned when
- A.
  
  Join conditions are not specified in a PROC SQL join.
- B.
  
  Join conditions are not specified in a PROC SQL set operation.
- C.
  
  More than two tables are specified in a PROC SQL join.
- D.
  
  The keyword ALL is used with the OUTER UNION operator.
Correct Answer
A. Join conditions are not specified in a PROC SQL join.

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.

Rate this question:
2.

Given the PROC SQL query and tables shown below, which output is generated?
- A.
- B.
- C.
- D.
Correct Answer
B.

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.

Rate this question:
3.

Given the PROC SQL query and tables shown below, which output is generated?
- A.
- B.
- C.
- D.
Correct Answer
D.

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.

Rate this question:
4.

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. Δ
- A.
  
  proc sql; select a.obs label='Obs', med b.obs label='Obs', duration from groupa as a, groupb as b where a.obs=b.obs;
- B.
  
  proc sql; select coalesce(a.obs, b.obs) label='Obs', med, duration from groupa as a full join groupb as b on a.obs=b.obs;
- C.
  
  proc sql; select a.*, duration from groupa as a left join groupb as b where a.obs=b.obs;
- D.
  
  proc sql; select a.*, duration from groupa as a inner join groupb as b on a.obs=b.obs;
Correct Answer
D. proc sql; select a.*, duration from groupa as a inner join groupb as b on a.obs=b.obs;

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.

Rate this question:
5.

Which output will the following PROC SQL query generate?
- A.
- B.
- C.
- D.
Correct Answer
A.

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.

Rate this question:
6.

In order for PROC SQL to perform an inner join,
- A.
  
  The tables being joined must contain the same number of columns.
- B.
  
  The tables must be sorted before they are joined.
- C.
  
  The columns that are specified in a join condition in the WHERE clause must have the same data type.
- D.
  
  The columns that are specified in a join condition in the WHERE clause must have the same name.
Correct Answer
C. The columns that are specified in a join condition in the WHERE clause must have the same data type.

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.

Rate this question:
7.

Which statement about in-line views is false?
- A.
  
  Once defined, an in-line view can be referenced in any PROC SQL query in the current SAS session.
- B.
  
  An in-line view can be assigned a table alias but not a permanent name.
- C.
  
  In-line views can be combined with tables in PROC SQL joins.
- D.
  
  This PROC SQL query contains an in-line view that uses valid syntax: proc sql; select name, numvisits from (select name, sum(checkin) as numvisits from facility as f, members as m where area='POOL' and f.id=m.id group by name) where numvisits<=10 order by 1;
Correct Answer
A. Once defined, an in-line view can be referenced in any PROC SQL query in the current SAS session.

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.

Rate this question:
8.

Which PROC SQL query will generate the same output as the DATA step match-merge and PRINT step shown below?
- A.
  
  proc sql; title 'Merged'; select a.g3, z, r from table1 as a full join table2 as b on a.g3 = b.g3 order by 1;
- B.
  
  proc sql; title 'Merged'; select a.g3, z, r from table1 as a full join corr table2 as b on a.g3 = b.g3 order by 1;
- C.
  
  proc sql; title 'Merged'; select coalesce(a.g3, b.g3) label='G3', z, r from table1 as a full join table2 as b on a.g3 = b.g3 order by 1;
- D.
  
  proc sql; title 'Merged'; select g3, z, r from table1 as a full join table2 as b on a.g3 = b.g3 order by 1;
Correct Answer
C. proc sql; title 'Merged'; select coalesce(a.g3, b.g3) label='G3', z, r from table1 as a full join table2 as b on a.g3 = b.g3 order by 1;

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.

Rate this question:
9.

A PROC SQL inner join can combine
- A.
  
  A maximum of two tables or in-line views, but multiple joins can be chained together.
- B.
  
  A maximum of 32 tables or two in-line views.
- C.
  
  A maximum of 32 tables, which includes any tables referenced by an in-line view.
- D.
  
  A maximum of two tables and 32 columns.
Correct Answer
C. A maximum of 32 tables, which includes any tables referenced by an in-line view.

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.

Rate this question:
10.

Which statement about the use of table aliases is false?
- A.
  
  Table aliases must be used when referencing identical table names from different libraries.
- B.
  
  Table aliases can be referenced by using the keyword AS.
- C.
  
  Table aliases (or full table names) must be used when referencing a column name that is the same in two or more tables.
- D.
  
  Table aliases must be used when using summary functions.
Correct Answer
D. Table aliases must be used when using summary functions.

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.

Rate this question: