backnextLearning Path
   

Using the INTERSECT Set Operator

The set operator INTERSECT does both of the following:

  • selects unique rows that are common to both tables
  • overlays columns.

Table 1

Diagram of 2 vertical intersecting circles with intersecting portion highlighted

 Table 2


Let’s see how INTERSECT works when used alone and with the keywords ALL and CORR.


Using the INTERSECT Operator Alone

The INTERSECT operator compares and overlays columns in the same way as the EXCEPT operator, by column position instead of column name. However, INTERSECT selects rows differently and displays in output the unique rows that are common to both tables. The following PROC SQL set operation uses the INTERSECT operator to combine the tables One and Two, which were introduced previously:

 

proc sql;
   select *
      from one
   intersect
   select *
      from two;
 One
X A
1 a
1 a
1 b
2 c
3 v
4 e
6 g
Two
X B
1 x
2 y
3 z
3 v
5 w

X A
3 v


Tables One and Two have only one unique row in common and this row is displayed in the output. (This is the same row that was eliminated in the earlier example that contained the EXCEPT operator.)


  Copyright © 2003 SAS Institute Inc., Cary, NC, USA. All rights reserved.
Terms of Use & Legal Information | Privacy Statement
backnext