topic Re: Use a macro list value to define dynamically list of keys in SQL join in SAS Programming

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

bebess — Wed, 25 Jul 2018 10:05:44 GMT

%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;

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

RW9 — Wed, 25 Jul 2018 10:08:31 GMT

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.

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

Kurt_Bremser — Wed, 25 Jul 2018 11:24:02 GMT

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

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

bebess — Wed, 25 Jul 2018 11:34:56 GMT

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?

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

RW9 — Wed, 25 Jul 2018 11:55:58 GMT

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.

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

Tom — Wed, 25 Jul 2018 14:52:52 GMT

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;

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

bebess — Wed, 25 Jul 2018 15:19:55 GMT

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