Macro function to append a string in another macro varible

[David_Billa]

I've the code as follows. In this code, t1. and t2. string is not appending to the value of macro variable Meta_Columns_map as I except. I believe we've to add some macro function before cats so that join condition will correctly.

%let Meta_Columns_map=Date,Purpose,Type,Name;

Proc sql;
    Create table Trans_Table as 
        Select t1.*,t2.ID as ID
            from Input t1 left join Temp1 t2 on (cats(t1.&Meta_Columns_map.)=cats(t2.&Meta_Columns_map.))
    ;
Quit;

Error which I got is,

MPRINT(ITBB_LOADER):   Proc sql;
MPRINT(ITBB_LOADER):   Create table Trans_Table as Select t1.*,t2.ID as ID from work.Extract1 t1 left join Temp1 t2 on 
(cats(t1.Date,Purpose,Type,Name)=cats(t2.Date,Purpose,Type,Name)) ;
ERROR: Ambiguous reference, column Purpose is in more than one table.
ERROR: Ambiguous reference, column Type is in more than one table.
ERROR: Ambiguous reference, column Name is in more than one table.

Excepted join condition is (cats(t1.Date,t1.Purpose,t1.Type,Name)=cats(t2.Date,t2.Purpose,t2.Type,Name)) ;

In real life I do have many fields to in join condition and it may change often and that's why I was asked to use only this approach and not any other method. Any help?

[Kurt_Bremser]

Make it unambiguous for the table aliases:

%let Meta_Columns_map_t1=t1.Date,t1.Purpose,t1.Type,t1.Name;
%let Meta_Columns_map_t2=t2.Date,t2.Purpose,t2.Type,t2.Name;

proc sql;
create table Trans_Table as 
  select t1.*,t2.ID as ID
  from Input t1 left join Temp1 t2
  on (cats(&Meta_Columns_map_t1.)=cats(&Meta_Columns_map_t2.))
;
quit;

Note that using CATS like this might create false matches

cats("12","1") = cats("1","21") !!

[Kurt_Bremser]

With a change to your map variable and some macro coding, you can try this:

%let Meta_Columns_map=Date Purpose Type Name;

%macro do_sql;
proc sql;
create table Trans_Table as 
  select t1.*,t2.ID as ID
  from Input t1 left join Temp1 t2 on
%do i = 1 to %sysfunc(countw(&Meta_Columns_map.,));
  %if &i. > 1 %then %do;
  and 
  %end;
  t1.%scan(&Meta_Columns_map.,&i.) = t2.%scan(&Meta_Columns_map.,&i.) 
%end;
;
quit;
%mend do_sql;
%do_sql

(Untested)

[David_Billa]

Thanks. Can I accomplish it without Looping? Once upon a time I achived it using quoting function in one single line but I'm unable to recall it fully which I done in the past.

[David_Billa]

@Kurt_Bremser something like below.

%quote(t1.(cats(t1.&Meta_Columns_map.)))=%quote(t2.(cats(t2.&Meta_Columns_map.)))

[Tom · Accepted solution]

---

@David_Billa wrote:

Thanks. Can I accomplish it without Looping? Once upon a time I achived it using quoting function in one single line but I'm unable to recall it fully which I done in the past.

---

Use TRANWRD().

%let Meta_Columns_map=Date,Purpose,Type,Name;
%let t1=t1.%sysfunc(tranwrd(%superq(Meta_Columns_map),%str(,),%str(,t1.)));
%let t2=t2.%sysfunc(tranwrd(%superq(Meta_Columns_map),%str(,),%str(,t2.)));
...
on (catx('|',&t1) = catx('|',&t2))
...