Chapter 6 Set Operators 6 1 Introduction to

Chapter 6: Set Operators 6. 1: Introduction to Set Operators 6. 2: The EXCEPT Operator 6. 3: The INTERSECT Operator 6. 4: The UNION Operator 6. 5: The OUTER UNION Operator 1

Chapter 6: Set Operators 6. 1: Introduction to Set Operators 6. 2: The EXCEPT Operator 6. 3: The INTERSECT Operator 6. 4: The UNION Operator 6. 5: The OUTER UNION Operator 2

Objectives n 3 Describe SQL set operators and modifiers.

Types of Set Operators Set operators vertically combine rows from two result sets. There are four set operators: 4 Except Union Intersect Outer Union

Default Behavior of Set Operators EXCEPT INTERSECT UNION OUTER UNION 5 Columns are matched by position and must be the same data type. Column names in the final result set are determined by the first result set. All columns from both result sets are selected.

Set Operator Syntax General form of an SQL query using a set operator: SELECT … EXCEPT | INTERSECT | UNION | OUTER UNION <CORR> <ALL> SELECT … The set operator operates on the result sets produced by the two SELECT statements, not on the actual tables themselves. 6

Types of Set Operators EXCEPT Unique rows from the first table that are not found in the second table are selected. select * from one except select * from two; 7 . . .

Types of Set Operators INTERSECT Common unique rows from both tables are selected. select * from one intersect select * from two; 8 . . .

Types of Set Operators UNION Unique rows from both tables are selected with columns overlaid. select * from one union select * from two; 9 . . .

Types of Set Operators OUTER UNION n All rows from both tables, unique as well as non-unique, are selected. n Columns are not overlaid. select * from one outer union select * from two; 10

Modifiers You can use two modifiers to modify the behavior of set operators: n ALL n CORRESPONDING 11

Modifiers ALL n does not remove duplicate rows, and thus avoids an extra pass through the data. Use the ALL modifier for better performance when it is possible. n is not allowed in connection with an OUTER UNION operator. It is implicit. 12

Modifiers CORRESPONDING n overlays columns by name, instead of by position n removes any columns not found in both tables when used in EXCEPT, INTERSECT, and UNION operations n causes common columns to be overlaid when used in OUTER UNION operations n can be abbreviated as CORR. 13

14

6. 01 Poll By default the EXCEPT, INTERSECT, and UNION set operators remove duplicate rows from the query output. True False 15

6. 01 Poll – Correct Answer By default the EXCEPT, INTERSECT, and UNION set operators remove duplicate rows from the query output. True False 16

17

Chapter 6: Set Operators 6. 1: Introduction to Set Operators 6. 2: The EXCEPT Operator 6. 3: The INTERSECT Operator 6. 4: The UNION Operator 6. 5: The OUTER UNION Operator 18

Objectives n n 19 Describe the SQL process when you use the EXCEPT set operator and keywords. Use the EXCEPT set operator.

EXCEPT Unique rows from the first result set that are not found in the second result set are selected. 20

Business Scenario Create a report that displays the employee identification number and job title of the non-Sales staff employees. Considerations: 21 n The orion. Employee_organization table contains information about all current Orion Star employees. n The orion. Sales table contains information about current Sales employees only.

The EXCEPT Operator You need a query that returns information from rows that exist in orion. Employee_organization, but not in orion. Sales. The EXCEPT operator could be useful. orion. Employee_organization non-sales orion. Sales 22

Flow Diagram: EXCEPT Operator EXCEPT CORR Yes No Remove nonmatching columns. ALL No Remove duplicate rows. Remove matching rows. 23 End Yes

The EXCEPT Operator Display the unique rows in Table ONE that are not found in Table TWO. Table ONE X 1 1 1 2 3 4 6 24 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except select * from two; s 106 d 01 . . .

The EXCEPT Operator The SQL processor removes duplicate rows within the tables. Table ONE X 1 1 1 2 3 4 6 25 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except select * from two; s 106 d 01 . . .

The EXCEPT Operator The SQL processor creates an intermediate result set by returning the rows that are found only in Table ONE X 1 1 1 2 3 4 6 26 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except select * from two; Intermediate Results 1 1 2 4 6 a b c e g s 106 d 01 . . .

The EXCEPT Operator The column names are determined by Table ONE in the final result set. Table ONE X 1 1 1 2 3 4 6 27 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except select * from two; Final Results X 1 1 2 4 6 A a b c e g s 106 d 01 . . .

The EXCEPT Operator with ALL Display the rows (duplicates included) that are found in Table ONE, but not in Table TWO. Table ONE X 1 1 1 2 3 4 6 28 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except all select * from two; s 106 d 02 . . .

The EXCEPT Operator with ALL The SQL processor creates an intermediate result set by returning the rows that are found only in Table ONE X 1 1 1 2 3 4 6 29 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except all select * from two; Intermediate Results 1 1 1 2 4 6 a a b c e g s 106 d 02 . . .

The EXCEPT Operator with ALL The column names are determined by Table ONE in the final result set. Table ONE X 1 1 1 2 3 4 6 30 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except all select * from two; Final Results X 1 1 1 2 4 6 A a a b c e g s 106 d 02 . . .

The EXCEPT Operator with CORR Display the unique rows that exist in Table ONE and not in Table TWO, based on same-named columns. Table ONE X 1 1 1 2 3 4 6 31 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except corr select * from two; s 106 d 03 . . .

The EXCEPT Operator with CORR The SQL processor eliminates any columns not found in both tables. Table ONE X 1 1 1 2 3 4 6 32 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except corr select * from two; s 106 d 03 . . .

The EXCEPT Operator with CORR The SQL processor eliminates duplicate rows. Table ONE X 1 1 1 2 3 4 6 33 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except corr select * from two; s 106 d 03 . . .

The EXCEPT Operator with CORR The SQL processor creates an intermediate result set by returning rows that are found only in Table ONE X 1 1 1 2 3 4 6 34 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except corr select * from two; Intermediate Results 4 6 s 106 d 03 . . .

The EXCEPT Operator with CORR The SQL processor creates an intermediate result set by returning rows that are found only in Table ONE X 1 1 1 2 3 4 6 35 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one except corr select * from two; Final Results X 4 6 s 106 d 03

36

6. 02 Quiz What are the results when you combine ALL with CORR? Table ALPHA X 1 1 3 4 5 A x y z v w Table BETA X 1 2 3 3 5 B x y z v v select * from alpha except all corr select * from beta; Run the program s 106 a 01 and review the results. 37 s 106 a 01

6. 02 Quiz – Correct Answer What are the results when you combine ALL with CORR? Table BETA Table ALPHA X 1 1 3 4 5 A x y z v w X 1 2 3 3 5 X 1 4 38 B x y z v v select * from alpha except all corr select * from beta; Final result set

Step 1: Using ALL with CORR Step 1 CORR specifies that only same-named columns be used. ALL specifies that all values of X be used, including duplicates. Table ALPHA X 1 1 3 4 5 39 A x y Z v w Table BETA X 1 2 3 3 5 B x y z v v select * from alpha except all corr select * from beta; . . .

Step 2: Using ALL with CORR Step 2 EXCEPT specifies that only X values found in ALPHA and not in BETA are used. Table ALPHA X 1 1 3 4 5 Table BETA A x y Z v w X 1 2 3 3 5 X 1 4 40 B x y z v v select * from alpha except all corr select * from beta; Final result set

Business Scenario (Review) Create a report that displays the employee identification number and job title of the employees who are not Sales staff. orion. Employee_organization non-sales proc sql; select Employee_ID, Job_Title from orion. Employee_organization except all select Employee_ID, Job_Title from orion. Sales; quit; orion. Sales 41 s 106 d 04

The EXCEPT Operator Partial PROC SQL Output (10 out of 259) Non-Sales Staff Employees Employee_ID Job_Title ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 120101 Director 120104 Administration Manager 120105 Secretary I 120106 Office Assistant II 120107 Office Assistant III 120108 Warehouse Assistant II 120109 Warehouse Assistant I 120110 Warehouse Assistant III 120111 Security Guard II 120112 Security Guard I 42

43

6. 03 Quiz Answer the questions about the following program: select Employee_ID, Job_Title from orion. Employee_organization except all select Employee_ID, Job_Title from orion. Sales; 1. Why is the CORR keyword not used in this example? 2. Would adding the CORR keyword to this example change the outcome? 44 s 106 a 02

6. 03 Quiz – Correct Answer the questions about the following program: select Employee_ID, Job_Title from orion. Employee_organization except all select Employee_ID, Job_Title from orion. Sales; 1. Why is the CORR keyword not used in this example? Both SELECT lists specify the same column names in the same order, so CORR is not necessary. 45

6. 03 Quiz – Correct Answer the questions about the following program: select Employee_ID, Job_Title from orion. Employee_organization except all select Employee_ID, Job_Title from orion. Sales; 1. Why is the CORR keyword not used in this example? Both SELECT lists specify the same column names in the same order, so CORR is not necessary. 2. Would adding the CORR keyword in this example change the outcome? No, adding CORR produces the same results. 46

Using the CORR Keyword This demonstration illustrates the use of the CORR keyword with the EXCEPT set operator. 47 s 106 d 04 a

The EXCEPT Operator This query can easily become an in-line view used to determine how many managers, who are not Sales staff, are employed at Orion Star. proc sql; select count(*) 'No. Non-Sales Managers' from (select distinct Manager_ID from orion. Employee_organization except all select Employee_ID from orion. Sales) ; quit; A manager might have multiple direct reports, so use the 48 DISTINCT keyword in the first part of the query. You can confidently use the ALL keyword because the first query returns distinct values, and the Sales table contains no duplicate records. s 106 d 05

The EXCEPT Operator PROC SQL Output No. Non-Sales Managers ƒƒƒƒƒ 48 49

50

6. 04 Poll By default, the EXCEPT set operator selects all the rows from the first result set that are not in the second result set. True False 51

6. 04 Poll – Correct Answer By default, the EXCEPT set operator selects all the rows from the first result set that are not in the second result set. True False By default, the EXCEPT operator eliminates duplicate rows first. It selects only unique rows from the first result set that are not in the second result set. 52

53

Chapter 6: Set Operators 6. 1: Introduction to Set Operators 6. 2: The EXCEPT Operator 6. 3: The INTERSECT Operator 6. 4: The UNION Operator 6. 5: The OUTER UNION Operator 54

Objectives n n 55 Describe the SQL process when using the INTERSECT set operator and keywords. Use the INTERSECT set operator.

INTERSECT Common unique rows from both result sets are selected. 56

Business Scenario Orion Star frequently hires experienced Sales staff at higher levels on the assumption that they will be more productive than inexperienced personnel. Create a report that displays the employee identification number of current Level III and Level IV Sales staff hired in 2004, who made at least one sale by the end of 2005. Considerations: 57 n The orion. Order_fact table contains information on all sales. n The orion. Sales table contains information about current Sales employees, including job titles and hire dates.

The INTERSECT Operator You need a query that returns information from rows that exist in both orion. Sales and orion. Order_fact. The INTERSECT operator could be useful. orion. Sales sales by Sales staff orion. Order_fact 58

Flow Diagram: INTERSECT Operator INTERSECT CORR Yes No Remove nonmatching columns. ALL No Remove duplicate rows. Save matching rows. 59 End Yes

The INTERSECT Operator Display the unique rows common to Table ONE and Table TWO. Table ONE X 1 1 1 2 3 4 6 60 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one intersect select * from two; s 106 d 06 . . .

The INTERSECT Operator The SQL processor removes duplicate rows within the tables. Table ONE X 1 1 1 2 3 4 6 61 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one intersect select * from two; s 106 d 06 . . .

The INTERSECT Operator The SQL processor creates an intermediate result set by returning the rows that are found in both tables. Table ONE X 1 1 1 2 3 4 6 62 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one intersect select * from two; Intermediate Results 3 v s 106 d 06 . . .

The INTERSECT Operator The column names are determined by Table ONE in the final result set. Table ONE X 1 1 1 2 3 4 6 63 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one intersect select * from two; Final Results X A 3 v s 106 d 06

64

6. 05 Quiz Submit the program s 106 a 03 and review the results. Add the ALL keyword to the second PROC SQL step and resubmit. Will the addition of the ALL keyword have any effect on the output? Table AAA Table BBB X A select * X B 1 a from aaa 1 a intersect all 1 a 1 b select * 2 z 2 c from bbb; 3 z 3 v 4 e 6 g 65 3 v 5 w s 106 a 03

6. 05 Quiz – Correct Answer Will the addition of the ALL keyword have any effect on the output? Yes. There are duplicate rows common to both tables. The ALL keyword will include the duplicate rows. Table AAA X 1 1 1 2 3 4 6 66 A a a b c v e g Table BBB X 1 1 2 3 3 5 B a a z z v w select * from aaa intersect all select * from bbb; Final Results X A 1 a 3 v s 106 a 03

The INTERSECT Operator with CORR Display the unique rows common to Table ONE and Table TWO, based on the same-named columns. Table ONE X 1 1 1 2 3 4 6 67 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one intersect corr select * from two; s 106 d 07 . . .

The INTERSECT Operator with CORR The SQL processor eliminates any columns not found in both tables. Table ONE X 1 1 1 2 3 4 6 68 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one intersect corr select * from two; s 106 d 07 . . .

The INTERSECT Operator with CORR The SQL processor eliminates duplicate rows and rows that are not common to Table ONE and Table TWO. Final Table ONE Table TWO Results X 1 1 1 2 3 4 6 69 A a a b c v e g X 1 2 3 3 5 B x y z v w select * from one intersect corr select * from two; X 1 2 3 s 106 d 07

Business Scenario (Review) Create a report that displays the employee identification number of current Level III and Level IV Sales staff hired in 2004, who made at least one sale by the end of 2005. orion. Sales sales by Sales staff proc sql; select Employee_ID from orion. Sales where year(Hire_date)=2004 and scan(Job_Title, -1) in ("III", "IV") intersect all select distinct Employee_ID from orion. Order_fact where year(Order_date) le 2005; quit; orion. Order_fact 70 s 106 d 08

The INTERSECT Operator PROC SQL Output Employee ID ƒƒƒƒƒƒ 120179 71

72

Exercise This exercise reinforces the concepts discussed previously. 73

Chapter 6: Set Operators 6. 1: Introduction to Set Operators 6. 2: The EXCEPT Operator 6. 3: The INTERSECT Operator 6. 4: The UNION Operator 6. 5: The OUTER UNION Operator 74

Objectives n n 75 Describe the SQL process when you use the UNION set operator and keywords. Use the UNION set operator.

UNION Both result sets are combined, and then unique rows are selected with columns overlaid. 76

Business Scenario The management team needs a payroll report for Level I, II, and III Orion Star employees. The UNION operator could be useful here. Below is a sketch of the desired report: Payroll Report for Level I, II, and III Employees ____________________ Total Paid to ALL Level I Staff 1, 234, 567 Total Paid to ALL Level II Staff 1, 456, 789 Total Paid to ALL Level III Staff 2, 123, 456 77

Flow Diagram: UNION Operator UNION CORR Yes No Concatenate tables. Remove nonmatching columns. ALL No Remove duplicate rows. 78 End Yes

The UNION Operator Display all of the unique rows from both Table ONE and Table TWO. Table ONE X 1 1 1 2 3 4 6 79 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one union select * from two; s 106 d 09 . . .

The UNION Operator The SQL processor creates an intermediate result set by concatenating and sorting Table ONE and Table TWO. Intermediate Results Table ONE Table TWO X 1 1 1 2 3 4 6 80 A a a b c v e g X 1 2 3 3 5 B x y z v w select * from one union select * from two; 1 1 2 2 3 3 3 4 5 6 a a b x c y v v z e w g s 106 d 09 . . .

The UNION Operator The SQL processor removes duplicate rows from the intermediate result. Intermediate Results Table ONE Table TWO X 1 1 1 2 3 4 6 81 A a a b c v e g X 1 2 3 3 5 B x y z v w select * from one union select * from two; 1 1 2 2 3 3 3 4 5 6 a a b x c y v v z e w g s 106 d 09 . . .

The UNION Operator Final Result Set Table ONE X 1 1 1 2 3 4 6 82 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one union select * from two; Final Results X 1 1 1 2 2 3 3 4 5 6 A a b x c y v z e w g s 106 d 09

The UNION Operator with CORR Display all of the unique rows of same-named columns in Table ONE and Table TWO. Table ONE X 1 1 1 2 3 4 6 83 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one union corr select * from two; s 106 d 10 . . .

The UNION Operator with CORR The SQL processor creates an intermediate result set by concatenating and sorting data from same-named columns. Intermediate Table ONE Table TWO Results X 1 1 1 2 3 4 6 84 A a a b c v e g X 1 2 3 3 5 B x y z v w select * from one union corr select * from two; X 1 1 2 2 3 3 3 4 5 6 s 106 d 10 . . .

The UNION Operator with CORR The SQL processor eliminates duplicate rows. Table ONE X 1 1 1 2 3 4 6 85 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one union corr select * from two; Intermediate Results X 1 1 2 2 3 3 3 4 5 6 s 106 d 10 . . .

The UNION Operator with CORR Table ONE X 1 1 1 2 3 4 6 86 A a a b c v e g Table TWO X 1 2 3 3 5 B x y z v w select * from one union corr select * from two; Final Results X 1 2 3 4 5 6 s 106 d 10

Business Scenario (Review) The management team needs a payroll report for the Level I, II, and III Orion Star employees. n n The orion. Staff table contains the job title and salary information for all Orion Star employees. Use the UNION set operator to combine the results from each query that calculates the total paid to all Level I, II, and III employees. Payroll Report for Level I, II, and III Employees ____________________ Total Paid to ALL Level I Staff 1, 234, 567 Total Paid to ALL Level II Staff 1, 456, 789 Total Paid to ALL Level III Staff 2, 123, 456 87

The UNION Operator proc sql; select 'Total Paid to ALL Level I Staff', sum(Salary) format=comma 12. from orion. Staff where scan(Job_Title, -1, ' ')='I' union select 'Total Paid to ALL Level II Staff', sum(Salary) format=comma 12. from orion. Staff where scan(Job_Title, -1, ' ')='II' union select 'Total Paid to ALL Level III Staff', sum(Salary) format=comma 12. from orion. Staff where scan(Job_Title, -1, ' ')='III'; quit; 88 s 106 d 11

The UNION Operator PROC SQL Output Payroll Report for Level I, II, and III Employees ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ Total Paid to ALL Level I Staff 3, 582, 630 Total Paid to ALL Level II Staff 3, 569, 580 Total Paid to ALL Level III Staff 2, 296, 425 89

Set Operators and Keywords: Flow Diagrams EXCEPT CORR INTERSECT Yes No CORR Remove nonmatching columns. ALL No UNION Yes Remove nonmatching columns. No Concatenate tables. ALL Yes Remove nonmatching columns. No No Yes Remove duplicate rows. CORR Yes Remove duplicate rows. ALL No Remove matching rows. Save matching rows. Remove duplicate rows. End End 90 Yes

91

6. 06 Poll Is it more or less efficient to use the ALL keyword in a set operation? More efficient Less efficient 92

6. 06 Poll – Correct Answer Is it more or less efficient to use the ALL keyword in a set operation? More efficient Less efficient No de-duplication is required. 93

94

Chapter 6: Set Operators 6. 1: Introduction to Set Operators 6. 2: The EXCEPT Operator 6. 3: The INTERSECT Operator 6. 4: The UNION Operator 6. 5: The OUTER UNION Operator 95

Objectives n n n 96 Describe SQL OUTER UNION set operators and keywords. Use the OUTER UNION set operators. Compare the SQL set operators to traditional SAS programming tools.

Business Scenario Write a query to display the employee ID numbers, job titles, and salaries for all Administrative staff. The data that you need is in four separate data sets with identical structures. The OUTER UNION operator could be useful here. 97

OUTER UNION n n 98 All rows from both result sets, unique as well as non-unique, are selected. Columns are not overlaid.

The OUTER UNION Operator Display all data values from Table ONE and Table TWO. Table ONE X A 1 a 1 b 2 c 3 v 4 e 6 g 99 Table TWO X 1 2 3 3 5 B x y z v w select * from one outer union select * from two; Final Results X 1 1 1 2 3 4 6. . . A a a b c v e g X. . . . 1 2 3 3 5 B x y z v w s 106 d 12

The OUTER UNION Operator with CORR Display all data values from Table ONE and Table TWO, but overlay common columns. Table ONE X A 1 a 1 b 2 c 3 v 4 e 6 g 100 Table TWO X 1 2 3 3 5 B x y z v w select * from one outer union corr select * from two; Final Results X 1 1 1 2 3 4 6 1 2 3 3 5 A a a b c v e g B x y z v w s 106 d 13

Business Scenario (Review) Write a query to display the employee ID numbers, job titles, and salaries for all Administrative staff. Considerations: The data that you need is in four separate data sets with identical structures: n n 101 work. Admin_III work. Admin_IV

The OUTER UNION Operator with CORR The OUTER UNION operator with the CORR keyword might be useful here. proc sql; select * from work. Admin_I outer union corr select * from work. Admin_III outer union corr select * from work. Admin_IV; 102 s 106 d 14

The OUTER UNION Operator Results PROC SQL Output Employee Annual Employee ID Employee Job Title Salary ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 120105 Secretary I $27, 110 120992 Office Assistant I $26, 940 120993 Office Assistant I $26, 260 120994 Office Administrator I $31, 645 120106 Office Assistant II $26, 960 120662 Secretary II $27, 045 120749 Office Assistant II $26, 545 120995 Office Administrator II $34, 850 121147 Secretary II $29, 145 120107 Office Assistant III $30, 475 120267 Secretary III $28, 585 120667 Office Assistant III $29, 980 120799 Office Assistant III $29, 070 121146 Secretary III $29, 320 120266 Secretary IV $31, 750 120996 Office Assistant IV $32, 745 103

SQL versus Traditional SAS Programming The following programs produce the same report: data three; set one two; run; proc print data=three noobs; run; proc sql; select * from one outer union corr select * from two; quit; proc append base=one data=two; run; proc print data=one noobs; run; 104 s 105 d 15

105

6. 07 Multiple Choice Poll What DATA step statement yields the same results as OUTER UNION CORR? a. MERGE b. APPEND c. SET d. STACK 106

6. 07 Multiple Choice Poll – Correct Answer What DATA step statement yields the same results as OUTER UNION CORR? a. MERGE b. APPEND c. SET d. STACK 107

SQL Set Operators versus the DATA Step Key Points SQL DATA Step Number of tables processed simultaneously Limited to two tables Not limited by SAS; limited only by system resources. Column handling Depends on the SET All columns from all operator and keywords data sets are included in output data set(s), unless specified using data set options or program logic. Duplicate row handling Depends on the SET All rows are output operator and keywords unless specified using data set options or program logic. 108 Also consider PROC APPEND for vertical table combination.

Exercise This exercise reinforces the concepts discussed previously. 109

Chapter Review 1. How many rows will this query produce? proc sql; select * from table 1 INTERSECT select * from table 2 ; quit; 110 Table 1 ID 1 2 3 Var Abc Def Ghi Table 2 ID 1 2 3 Var Abc Zxy Ghi

Chapter Review Answers 1. How many rows will this query produce? Two Table 1 Table 2 proc sql; ID Var select * 1 Abc from table 1 2 Def 2 Zxy INTERSECT 3 Ghi select * from table 2 ; PROC SQL Output quit; ID Var ƒƒƒƒƒ 1 Abc 3 Ghi 111

Chapter Review 2. How many rows will this query produce? proc sql; select * from table 1 EXCEPT select * from table 2 ; quit; 112 Table 1 ID 1 2 3 Var Abc Def Ghi Table 2 ID 1 2 3 Var Abc Zxy Ghi

Chapter Review Answers 2. How many rows will this query produce? One Table 1 Table 2 proc sql; ID Var select * 1 Abc from table 1 2 Def 2 Zxy EXCEPT 3 Ghi select * from table 2 ; PROC SQL Output quit; ID Var ƒƒƒƒƒ 2 Def 113

Chapter Review 3. Will the addition of the CORR and ALL keywords change the number of rows that this query produces? proc sql; select * from table 1 EXCEPT CORR ALL select * from table 2 ; quit; 114 Table 1 ID 1 2 3 Var Abc Def Ghi Table 2 ID 1 2 3 Var Abc Zxy Ghi

Chapter Review Answers 3. Will the addition of the CORR and ALL keywords change the number of rows that this query produces? No proc sql; select * from table 1 EXCEPT CORR ALL select * from table 2 ; quit; 115 Table 1 ID 1 2 3 Var Abc Def Ghi Table 2 ID 1 2 3 PROC SQL Output ID Var ƒƒƒƒ 2 Def Var Abc Zxy Ghi

Chapter Review 4. How many columns will this query produce? proc sql; select * from table 1 OUTER UNION CORR select * from table 2 ; quit; 116 Table 1 ID 1 2 3 Var 1 Abc Def Ghi Table 2 ID 1 2 3 Var 2 Abc Zxy Ghi

Chapter Review Answers 4. How many columns will this query produce? Three Table 1 Table 2 proc sql; ID Var 1 ID Var 2 select * 1 Abc from table 1 2 Def 2 Zxy OUTER UNION CORR 3 Ghi select * from table 2 PROC SQL Output ; ID Var 2 quit; ƒƒƒƒƒƒƒƒ 1 Abc 2 Def 3 Ghi 1 Abc 2 Xyz 3 Ghi 117