topic Re: Macro function to create an "EquiJoin" Condition in SAS Programming

Macro function to create an "EquiJoin" Condition

DavePrinsloo — Sat, 21 Mar 2020 20:06:02 GMT

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;

Re: Macro function to create an "EquiJoin" Condition

Tom — Sat, 21 Mar 2020 20:15:56 GMT

Is this supposed to do something different than NATURAL join?

What does the macro do when one or more of the key variables is not present on one or more of the datasets being joined? Does it generate an error message or dose it just generate the SAS code anyway and let SAS generate an error when it tries to compile it?

Re: Macro function to create an "EquiJoin" Condition

DavePrinsloo — Sat, 21 Mar 2020 20:56:31 GMT

it could do something other than a natural join. For instance, if both tables contain a column like "creation_timestmp" then that should not be used in the join criteria. But maybe my knowledge of the natural join is not perfect!
It was also used inside a another macro that did the checking,.

It could also be used to for a slightly more generic solution to https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-or-DATA-STEP-union-with-list-of-var-in-quot-on-quot/m-p/633279#M77866