[Training Notes] Combining data in SAS PROC SQL

——————————-view data——————————————

##                                    dataA
## 
##                            Obs    ptid    ptage
## 
##                             1      101      37 
##                             2      102      45 
##                             3      103      38 
##                             4      104      56 
##  
##                                                                            
##  
##                                    dataB
## 
##                            Obs    ptid    ptsex
## 
##                             1      102      m  
##                             2      104      f  
##                             3      105      m  
##                             4      106      f

Part I

Join only matching records

WHERE

proc sql;     
   select * from ptd1, ptd2
      where ptd1.ptid=ptd2.ptid
  ;
quit;

##                       ptid     ptage      ptid  ptsex
##                   --------------------------------------
##                        102        45       102  m       
##                        104        56       104  f

ON

proc sql;    
   select * from ptd1 inner join ptd2
      on ptd1.ptid=ptd2.ptid;
quit;  

##                       ptid     ptage      ptid  ptsex
##                   --------------------------------------
##                        102        45       102  m       
##                        104        56       104  f

Left join

proc sql;     
   select * from ptd1 left join ptd2
      on ptd1.ptid=ptd2.ptid;
quit;

##                       ptid     ptage      ptid  ptsex
##                   --------------------------------------
##                        101        37         .          
##                        102        45       102  m       
##                        103        38         .          
##                        104        56       104  f

Right join

proc sql;     
   select * from ptd1 right join ptd2
      on ptd1.ptid=ptd2.ptid;
quit;

##                       ptid     ptage      ptid  ptsex
##                   --------------------------------------
##                        102        45       102  m       
##                        104        56       104  f       
##                          .         .       105  m       
##                          .         .       106  f

Full join

proc sql;     
   select * from ptd1 full join ptd2
      on ptd1.ptid=ptd2.ptid;
quit;

##                       ptid     ptage      ptid  ptsex
##                   --------------------------------------
##                        101        37         .          
##                        102        45       102  m       
##                        103        38         .          
##                        104        56       104  f       
##                          .         .       105  m       
##                          .         .       106  f

Cross join

proc sql;     
   select * from ptd1 cross join ptd2;
quit;

##                       ptid     ptage      ptid  ptsex
##                   --------------------------------------
##                        101        37       102  m       
##                        101        37       104  f       
##                        101        37       105  m       
##                        101        37       106  f       
##                        102        45       102  m       
##                        102        45       104  f       
##                        102        45       105  m       
##                        102        45       106  f       
##                        103        38       102  m       
##                        103        38       104  f       
##                        103        38       105  m       
##                        103        38       106  f       
##                        104        56       102  m       
##                        104        56       104  f       
##                        104        56       105  m       
##                        104        56       106  f

Union join

proc sql;     
   select * from ptd1 union join ptd2;
quit;

##                       ptid     ptage      ptid  ptsex
##                   --------------------------------------
##                          .         .       102  m       
##                          .         .       104  f       
##                          .         .       105  m       
##                          .         .       106  f       
##                        101        37         .          
##                        102        45         .          
##                        103        38         .          
##                        104        56         .

Part II

——————————-view data——————————————

##                                    ptd1
## 
##                            Obs    ptid     sex
## 
##                             1      101    male  
##                             2      102    female
##                             3      102    female
##                             4      103    male  
##  
##                                                                            
##  
##                                    ptd2
## 
##                            Obs    ptid    gender
## 
##                             1      101    male  
##                             2      102    female
##                             3      104    male

Producing Unique Rows from Both Queries (UNION)

proc sql;
title 'ptd1 UNION ptd2';
select * from ptd1
union
select * from ptd2;
quit;

##                               ptd1 UNION ptd2
## 
##                                 ptid  sex
##                             ------------------
##                                  101  male    
##                                  102  female  
##                                  103  male    
##                                  104  male

Producing Rows from Both Queries (UNION ALL)

proc sql;
title 'ptd1 UNION ALL ptd2';
select * from ptd1
union all
select * from ptd2;
quit;

##                             ptd1 UNION ALL ptd2
## 
##                                 ptid  sex
##                             ------------------
##                                  101  male    
##                                  102  female  
##                                  102  female  
##                                  103  male    
##                                  101  male    
##                                  102  female  
##                                  104  male

Producing Rows That Are in Only the First Query Result (EXCEPT)

proc sql;
title 'ptd1 EXCEPT ptd2';
select * from ptd1
except
select * from ptd2;
quit;

##                              ptd1 EXCEPT ptd2
## 
##                                 ptid  sex
##                             ------------------
##                                  103  male

Producing Rows That Are in Only the First Query Result (EXCEPT)

proc sql;
title 'ptd2 EXCEPT ptd1';
select * from ptd2
except
select * from ptd1;
quit;

##                              ptd2 EXCEPT ptd1
## 
##                                 ptid  gender
##                             ------------------
##                                  104  male

Producing Rows That Belong to Both Query Results (INTERSECT)

proc sql;
title 'ptd1 INTERSECT ptd2';
select * from ptd1
intersect
select * from ptd2;
quit;

##                             ptd1 INTERSECT ptd2
## 
##                                 ptid  sex
##                             ------------------
##                                  101  male    
##                                  102  female

Concatenating the Query Results (OUTER UNION)

proc sql;
title 'ptd1 OUTER UNION ptd2';
select * from ptd1
outer union
select * from ptd2;
quit;

##                            ptd1 OUTER UNION ptd2
## 
##                       ptid  sex           ptid  gender
##                   --------------------------------------
##                        101  male             .          
##                        102  female           .          
##                        102  female           .          
##                        103  male             .          
##                          .                 101  male    
##                          .                 102  female  
##                          .                 104  male

Concatenating the Query Results (OUTER UNION CORR)

proc sql;
title 'ptd1 OUTER UNION CORR ptd2';
select * from ptd1
outer union corr
select * from ptd2;
quit;

##                         ptd1 OUTER UNION CORR ptd2
## 
##                            ptid  sex       gender
##                        ----------------------------
##                             101  male              
##                             102  female            
##                             102  female            
##                             103  male              
##                             101            male    
##                             102            female  
##                             104            male

Producing Rows from the First Query or the Second Query

proc sql;
title 'ptd1 EXCLUSIVE UNION ptd2 and ptd2 EXCLUSIVE UNION ptd1';
(select * from ptd1
         except
select * from ptd2)
   union
(select * from ptd2
         except
 select * from ptd1);
quit;

##           ptd1 EXCLUSIVE UNION ptd2 and ptd2 EXCLUSIVE UNION ptd1
## 
##                                 ptid  sex
##                             ------------------
##                                  103  male    
##                                  104  male