DATA Step Match-Merge
|
PROC SQL Inner Join
|
---|---|
data merged; merge one two; by x; run; proc print data=merged noobs; title 'Table Merged'; run; |
proc sql; title 'Table Merged'; select one.x, a, b from one, two where one.x = two.x order by x; |
DATA Step Match-Merge
|
PROC SQL Full Outer
Join
|
---|---|
data merged; merge three four; by x; run; proc print data=merged noobs; title 'Table Merged'; run; |
proc sql; title 'Table Merged'; select three.x, a, b from three full join four on three.x = four.x order by x; |
General form, COALESCE
function in a basic SELECT clause:
SELECT COALESCE (column-1<,...column-n>)
column-1 through column-n
are the names of two
or more columns to be overlaid. The COALESCE function requires that
all arguments have the same data type.
|
Advantage
|
Example
|
---|---|
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; |