*hand25.txt;

DATA ONE;
 INPUT X A $ C $;
CARDS;
1 A X
2 B Y
4 D Z
;
DATA TWO;
 INPUT X B $ D $;
CARDS;
2 X A
3 Y B
5 V C
;

PROC SQL;                           *outer left join;
 SELECT *
   FROM ONE LEFT JOIN TWO
   ON ONE.X=TWO.X;

PROC SQL;                           *outer left join;
 SELECT ONE.X, A, C, B, D
   FROM ONE LEFT JOIN TWO
   ON ONE.X=TWO.X;

PROC SQL;                           *outer right join;
 SELECT *
   FROM ONE RIGHT JOIN TWO
   ON ONE.X=TWO.X;

PROC SQL;                           *outer full join;
 SELECT *
   FROM ONE FULL JOIN TWO
   ON ONE.X=TWO.X;

DATA THREE; MERGE ONE TWO; BY X;             *compare to SAS merge;
PROC PRINT;
RUN;

PROC SQL;                                            *overlay two columns;
 SELECT COALESCE(ONE.X, TWO.X) LABEL='X', A , B
  FROM ONE FULL JOIN TWO
  ON ONE.X=TWO.X;