Use a macro list value to define dynamically list of keys in SQL join

[bebess]

%let example=Gender Age;

proc sql;

create table want as

select *

from table1 as t1

inner join table2 as t2

/* i want to use &example in order to define dynamically the key of my join*/

on t1.gender=t2.gender

and t1.Age=t2.Age

/*******/

;

quit;

[RW9]

Not easily, you would need macro code to loop over each word in the list, then generate out the on part of it.  I would question why?  If your rules are strict enough to allow a join like that, it is strict enough to just write the join and not need a macro list.  Otherwise what happens if the variable isn't in both, if its different types, if the join is correct for the data etc.  Far more problems will be created than solved by this.

[bebess]

I have all controls before doing the join , i am sure i have the same data structure between tables ( same variables with same type and format ... ) .
Do i need to generate by a loop the syntax : on a.first_item=b.item and a.second.item=B.item in a different macro variable then use it my sql code?

[RW9 · Accepted solution]

Yes, as a start:

%do i=1 to ...
  %let tmp=%scan(&example.,&i);
    on t1.&tmp.=t2.&tmp.
  %end;
%end;
...

Really not worth doing though as risky, even if you have done loads of pre-checks, the logic of the join based on your data may not be what is wanted.

[Kurt_Bremser]

Note that a join like this is bad programming practice, as it will unvariably throw a  WARNING for a variable that already exists.

[Tom]

It is much easier if you just use SAS code instead of SQL code.

%let example=Gender Age;
data want ;
  merge table1 (in=in1) table2 (in=in2);
  by &example;
  if in1 and in2;
run;

[bebess]

Yes thanks it could be another solution , but the merge involve a sort of the 2 table , more time consuming i think ?