Joining Datasets via Proq Sql - Using Multiple Variables Inserted by User

[MikeXue]

Hello, 

Currently I have the following code: 

proc sql;
create table merged as select a.*, b.*
from LB as a full outer join ISSLB as b
on a.variablea1 = b.variableb1 and a.variablea2 = b.variable2 and a.variableaX = b.variablebY....

run; 

I would like to give users to ability to enter as many variable pairs they would like to join by. The main thing is this can vary from 3-6 variable pairs. So I would like to have a way to allow users to be able to insert all the variable pairs they want to join by. 

How can I make this work? 

Thanks,

Mike

[Reeza]

Untested but rough idea. 

You'll need to figure out a way to get those join conditions in as it seems like in your example the variable names are different in each table, which means you need to specify the field in each table. Most likely macro quoting functions will be required. 

You can wrap this in a macro if you'd like or leave it as open code if you're SAS 9.4M4+

%let var2join = var1=varA1 var3=Var3b var8=var8c;
%let numVars = %sysfunc(countw(&var2join));

proc sql;
create table want as
select * 
from have
join ... on %sysfunc(scan(&var2join, 1))
%do i=2 to &numVars;
and %sysfunc(scan(&var2join, &i))
%end;
;
quit;

---

@MikeXue wrote:

Hello, 

Currently I have the following code: 

proc sql;
create table merged as select a.*, b.*
from LB as a full outer join ISSLB as b
on a.variablea1 = b.variableb1 and a.variablea2 = b.variable2 and a.variableaX = b.variablebY....

run; 

I would like to give users to ability to enter as many variable pairs they would like to join by. The main thing is this can vary from 3-6 variable pairs. So I would like to have a way to allow users to be able to insert all the variable pairs they want to join by. 

How can I make this work? 

Thanks,

Mike

---

[Tom]

How are the users going to give the list?  Are you talking about writing a macro that the user can call?
Why not just have the macro accept the ON (aka WHERE) condition?

%mymacro
(left=LB
,right=ISSLB
,where=a.variablea1 = b.variableb1 and a.variablea2 = b.variable2
  and a.variableaX = b.variablebY
)