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?
@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))
...
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") !!
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)
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.
@Kurt_Bremser something like below.
%quote(t1.(cats(t1.&Meta_Columns_map.)))=%quote(t2.(cats(t2.&Meta_Columns_map.)))
@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))
...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.