23 May

SET Operators: The SAS Special

SAS has a couple of twists worth mentioning in a bit more detail.

ALL

The difference between ALL, and an operator without it, is that ALL returns all records and does not uniquify (my spell check says that isn’t a word, but, we know better) the result set.  This includes duplicate records in either result set that it applies to.

It cannot be used with an OUTER UNION.

CORR / CORRESPONDING

Corr is something I’d never seen before and depending on the operator it behaves differently.

  • EXCEPT, UNION and INTERSECT: It removes any column that does not have the same name.
  • OUTER UNION: It overlays columns with the same name.

Corr will also use an alias when it’s assigned to a column.

Here are some examples of CORR in action.

This is a UNION and CORR removes both the age and the sex columns from the report.

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

Name
--------------
Almers, C     
Bonaventure, T
Eberhardt, S  
Ivan, H       
Johnson, R    
Nunnelly, A   
Oberon, M     
Quigley, M    
Reberson, P   
Takahashi, Y  
Wilcox, E

CORR can be used with ALL. Here, because we use CORR the age and sex columns are removed.

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

Name
--------------
Almers, C     
Bonaventure, T
Eberhardt, S  
Ivan, H       
Johnson, R    
Nunnelly, A   
Oberon, M     
Quigley, M    
Reberson, P   
Takahashi, Y  
Wilcox, E  
Almers, C     
Bonaventure, T
Eberhardt, S  
Ivan, H       
Johnson, R    
Nunnelly, A   
Oberon, M     
Quigley, M    
Reberson, P   
Takahashi, Y  
Wilcox, E

Now, so it’s clear, here is an OUTER JOIN with CORR, written as CORRESPONDING.

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

Name                 Age  Sex
-----------------------------
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              .  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

Here is a link that goes into a lot more detail on the keywords and SET operators from the SAS support site.

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001361224.htm

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!