SAS: Join or Merge Tables

Note: Be careful to use merge if the the byvar are not unique in both tables, especially when the vars are numeric and can be aggregated .

Compare of SQL method and merge method

Inner Join

SELECT var
FROM table1 as a
INNER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if a and b;

Left Join

SELECT var
FROM table1 as a
LEFT JOIN as b
ON a.var=b.var; 

merge table1 (in=a) table2;
by var;
if a;

Left Join exclude Inner Join

SELECT var
FROM table1 as a
LEFT JOIN table as b
ON a.var=b.var;
WHERE b.var IS NULL;

merge table1 (in = a) table2 (in=b);
by var;
if a and not b;

Right Join

SELECT var
FROM table1 as a
RIGHT JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if b;

Right Join exclude Inner Join

SELECT var
FROM table1 as a
RIGHT JOIN table as b
ON a.var=b.var

WHERE a.var IS NULL;

merge table1 (in = a) table2 (in=b);
by var;
if b and not a;

Full Join

SELECT var
FROM table1 as a
FULL OUTER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if a or b;

or

merge table1 (in = a) table2 (in=b);
by var;

Full Join Inner Join

SELECT var
FROM table1 as a
FULL OUTER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);

by var;
if a ne b; /* only work on 2 table merge*/