This code presents a macro that generates an EquiJoin condition. i.e. an SQL Join condition where both tables have one or more columns that must have equal values in the join condition. For Example, if both tables contain the key columns KeyA KeyB and KeyC, then a join
would be something like: t1.KeyA=t2.KeyA and t1.KeyB=t2.KeyB and t1.KeyC=t2.KeyC
The macro is generic but was designed to be used with the macro %ut_varlist I posted recently.
The full source code is attached, but usage examples are shown here.
I welcome feedback!
Exampe 1 : Simple call
%let join_cond = %ut_sql_equijoin(in_cols = ABC DEF XYZ
, intab1 = t1
, intab2 = t2) ;
Example 2 : Hardcoded-call embedded in Proc SQL code
* First create a couple of temporary tables to use in the macro call ;
data a(keep=abc_id xyz_id aaa) b(keep=abc_id xyz_id bbb) ;
do abc_id = 1 to 10;
do xyz_id = 4 to 14;
aaa = abc_id + xyz_id;
bbb = xyz_id * abc_id;
output;
end;
end;
run;
proc sql noprint;
create table joined
as select a.*,
b.bbb
from a
left join
b
on %ut_sql_equijoin(
in_cols = abc_id xyz_id
, intab1 = a
, intab2 = b)
;
quit;
Example 3: This example has no knowledge of the columns in the tables other than that the join keys have a suffix "_id".
The join selects creates a join on these _Id columns and selects all columns from both tables
proc sql noprint;
create table joined
as select a.*,
%ut_varlist(table=b, add_prefix=b., newdlm=comma
, exclude=%ut_varlist(table=a) )
from a, b
where %ut_sql_equijoin(
in_cols = %ut_varlist(table=a, contain=_ID, contain_pos=END, select=%ut_varlist(table=b) )
, intab1 = a
, intab2 = b)
;
quit;
The generated code looks like:
proc sql noprint;
create table joined as select a.*,
MPRINT(UT_VARLIST): b.bbb
from a, b where
MPRINT(UT_SQL_EQUIJOIN): (a.abc_id = b.abc_id) AND (a.xyz_id = b.xyz_id)
;
quit;
