Compare SQL join with data step match-merge

When All of the Values Match

When all of the values of the BY variable match and there are no duplicate BY variables (columns), you can use a PROC SQL inner join to produce the same results as a DATA step match-merge.

data s1;

  input x a $;

  cards;

      1 a

                  2 b

                  3 c

                  ;

data s2;

  input x b $;

  cards;

      1 x

                  2 y

                  3 z

                  ;

proc sql;

   select s1.*, b

   from s1,s2

   where s1.x=s2.x

   ;

quit;

data merge1;

   merge s1 s2;

   by x;

run;

proc print data=merge1;

run;

When Only Some of the Values Match

When only some of the values of the BY variable match, you can use a PROC SQL full outer join to produce the same result as a DATA step match-merge. Unlike the DATA step match-merge, however, a PROC SQL outer join does not overlay the two common columns by default. To overlay common columns, you must use the COALESCE function in the PROC SQL full outer join.

data s1;

  input x a $;

  cards;

      1 a

                  2 b

                  4 d

                  ;

data s2;

  input x b $;

  cards;

                  2 x

                  3 y

                  5 v

                  ;

proc sql;

   select coalesce(s1.x,s2.x) as x, a, b

   from s1 full join s2

   on s1.x=s2.x

   order by x

   ;

quit;

data merge1;

   merge s1 s2;

   by x;

run;

proc print data=merge1;

run;

PROC SQL joins do not require sorted or indexed tables.	proc sql; select table1.x, a, b from table1 full join table2 on table1.x = table2.x; where table-1 is sorted by column X and table-2 is not
PROC SQL joins do not require that the columns in join expressions have the same name.	proc sql; select table1.x, lastname, status from table1, table2 where table1.id = table2.custnum;
PROC SQL joins can use comparison operators other than the equal sign (=).	proc sql; select a.itemnumber, cost, price from table1 as a, table2 as b where a.itemnumber = b.itemnumber and a.cost>b.price;