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