21 May

SET Operators: OUTER UNION

This is something that I’ve never seen before.  It’s a SAS operator, well, at least in the context of this article, maybe another platform has used it as some point.

What it does is concatenate tables.  It’s not complex, and I don’t see too many tricks to it, which is a pleasant change with SAS, so a couple of examples should suffice.

proc sql;
SELECT name, sex
FROM mysql.admit
WHERE sex = 'F'
OUTER UNION 
SELECT name, age
FROM mysql.admit
WHERE sex = 'F';
quit;

Name            Sex  Name                 Age
---------------------------------------------
Almers, C       F                           .
Bonaventure, T  F                           .
Johnson, R      F                           .
Reberson, P     F                           .
Eberhardt, S    F                           .
Nunnelly, A     F                           .
Oberon, M       F                           .
Quigley, M      F                           .
Takahashi, Y    F                           .
Ivan, H         F                           .
Wilcox, E       F                           .
                     Almers, C             34
                     Bonaventure, T        31
                     Johnson, R            43
                     Reberson, P           32
                     Eberhardt, S          49
                     Nunnelly, A           44
                     Oberon, M             28
                     Quigley, M            40
                     Takahashi, Y          43
                     Ivan, H               22
                     Wilcox, E             41

As you can see OUTER UNION simply appends the data and creates new columns for the second table. The other thing it does is set values to missing in the new, and old, rows. Bizarrely, the result set doesn’t seem to care if you have the same name. It just happily returns the new column. Fortunately, SAS has a solution for this: CORR.

CORR

I’m going to deal more with CORR in a future post but this is a useful place to put it because it’s about the only way that a OUTER UNION makes sense. What CORR does is to combine records that have the same column name. For example, we could change the prior query as follows:

proc sql;
SELECT name, sex
FROM mysql.admit
WHERE sex = 'F'
OUTER UNION CORR
SELECT name, age
FROM mysql.admit
WHERE sex = 'F';
quit;

Name            Sex       Age
-----------------------------
Almers, C       F           .
Bonaventure, T  F           .
Johnson, R      F           .
Reberson, P     F           .
Eberhardt, S    F           .
Nunnelly, A     F           .
Oberon, M       F           .
Quigley, M      F           .
Takahashi, Y    F           .
Ivan, H         F           .
Wilcox, E       F           .
Almers, C                  34
Bonaventure, T             31
Johnson, R                 43
Reberson, P                32
Eberhardt, S               49
Nunnelly, A                44
Oberon, M                  28
Quigley, M                 40
Takahashi, Y               43
Ivan, H                    22
Wilcox, E                  41

CORR will also happily duplicate records. For example if we modify the earlier examples to use the same query it will do the following:

proc sql;
SELECT name, age
FROM mysql.admit
WHERE sex = 'F'
OUTER UNION CORR
SELECT name, age
FROM mysql.admit
WHERE sex = 'F';
quit;

Name                 Age
------------------------
Almers, C             34
Bonaventure, T        31
Johnson, R            43
Reberson, P           32
Eberhardt, S          49
Nunnelly, A           44
Oberon, M             28
Quigley, M            40
Takahashi, Y          43
Ivan, H               22
Wilcox, E             41
Almers, C             34
Bonaventure, T        31
Johnson, R            43
Reberson, P           32
Eberhardt, S          49
Nunnelly, A           44
Oberon, M             28
Quigley, M            40
Takahashi, Y          43
Ivan, H               22
Wilcox, E             41

That’s pretty much it for a OUTER UNION. But, just for kicks, I did a cheesy version of it in MySQL. I decided to not get cute and did it with a UNION ALL.

mysql> SELECT name, age, '', ''
    -> FROM junk
    -> WHERE sex = 'F'
    -> UNION ALL
    -> SELECT '', '', name, age
    -> FROM junk
    -> WHERE sex = 'F';
+----------------+------+----------------+------+
| name           | age  |                |      |
+----------------+------+----------------+------+
| Almers, C      | 34   |                |      |
| Bonaventure, T | 31   |                |      |
| Johnson, R     | 43   |                |      |
| Reberson, P    | 32   |                |      |
| Eberhardt, S   | 49   |                |      |
| Nunnelly, A    | 44   |                |      |
| Oberon, M      | 28   |                |      |
| Quigley, M     | 40   |                |      |
| Takahashi, Y   | 43   |                |      |
| Ivan, H        | 22   |                |      |
| Wilcox, E      | 41   |                |      |
|                |      | Almers, C      | 34   |
|                |      | Bonaventure, T | 31   |
|                |      | Johnson, R     | 43   |
|                |      | Reberson, P    | 32   |
|                |      | Eberhardt, S   | 49   |
|                |      | Nunnelly, A    | 44   |
|                |      | Oberon, M      | 28   |
|                |      | Quigley, M     | 40   |
|                |      | Takahashi, Y   | 43   |
|                |      | Ivan, H        | 22   |
|                |      | Wilcox, E      | 41   |
+----------------+------+----------------+------+
22 rows in set (0.00 sec)

And, if you decide to use CORR, it’s can literally be the same thing as a UNION ALL.

mysql> SELECT name, age
    -> FROM junk
    -> WHERE sex = 'F'
    -> UNION ALL
    -> SELECT name, age
    -> FROM junk
    -> WHERE sex = 'F';
+----------------+------+
| name           | age  |
+----------------+------+
| Almers, C      |   34 |
| Bonaventure, T |   31 |
| Johnson, R     |   43 |
| Reberson, P    |   32 |
| Eberhardt, S   |   49 |
| Nunnelly, A    |   44 |
| Oberon, M      |   28 |
| Quigley, M     |   40 |
| Takahashi, Y   |   43 |
| Ivan, H        |   22 |
| Wilcox, E      |   41 |
| Almers, C      |   34 |
| Bonaventure, T |   31 |
| Johnson, R     |   43 |
| Reberson, P    |   32 |
| Eberhardt, S   |   49 |
| Nunnelly, A    |   44 |
| Oberon, M      |   28 |
| Quigley, M     |   40 |
| Takahashi, Y   |   43 |
| Ivan, H        |   22 |
| Wilcox, E      |   41 |
+----------------+------+
22 rows in set (0.00 sec)

Happy Hunting!