topic Re: Macro function to append a string in another macro varible in SAS Programming

Macro function to append a string in another macro varible

David_Billa — Mon, 24 May 2021 10:47:46 GMT

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?

Re: Macro function to append a string in another macro varible

Kurt_Bremser — Mon, 24 May 2021 11:03:39 GMT

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") !!

Re: Macro function to append a string in another macro varible

Kurt_Bremser — Mon, 24 May 2021 11:08:27 GMT

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)

Re: Macro function to append a string in another macro varible

David_Billa — Mon, 24 May 2021 11:40:27 GMT

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.

Re: Macro function to append a string in another macro varible

David_Billa — Mon, 24 May 2021 11:53:42 GMT

@Kurt_Bremser something like below.

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

Re: Macro function to append a string in another macro varible

Tom — Mon, 24 May 2021 16:58:18 GMT

@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))
...