Iterate through vector and convert elements to unquoted variable names

The following macro makes an inner join between two tables containing one column from each table in addition to the joining column :

%macro ij(x=,y=,to=".default",xc=,yc=,by=);
  %if &to   = ".default" %then %let to = &from;
  PROC SQL;
    CREATE TABLE &to AS
    SELECT t1.&xc, t2.&yc, t1.&by
    FROM &x t1 INNER JOIN &y t2
    ON t1.&by = t2.&by;
  RUN;
%mend;

I want to find a way to use several columns in &xc, &yc and &by. As I don't think I can use vectors of variables.

My idea is to pass parameters as vectors of strings instead of simple variables, for example xc = {"col1" "col2"} and loop through them using %let some_var= %sysfunc(dequote(&some_string)); to convert them back to variables.

Applied on xc only it would become something like:

%macro ij(x=,y=,to=".default",xc=,yc=,by=);
  %if &to   = ".default" %then %let to = &from;
  PROC SQL;
    CREATE TABLE &to AS
    SELECT 
    %do i = 1 %to %NCOL(&xc)
      %let xci = %sysfunc(dequote(&xc[1]));
      t1.&xci,
    %end;
    t2.&yc, t1.&by
    FROM &x t1 INNER JOIN &y t2
    ON t1.&by = t2.&by;
  RUN;
%mend;

But this loop fails. How could I make it work ?

Note: this is a simplified example, my ultimate ambition is to build join macros that would be as little verbose as possible and integrate data quality checks.

Solution

Really this would be much easier to code use SAS dataset options instead of building complicated macro logic.

proc sql ;
  create table want2 as
  select *
  from sashelp.class(keep=name age)
  natural inner join sashelp.class(keep=name height weight)
  ;
quit;

I would suggest learning how to use data step code instead of SQL code. For most normal data manipulations it is clearer and simpler. Say you wanted to combine IN1 and IN2 on the variable ID and keep the variable A and B from IN1 and the variable X and Y from the IN2.

data out ;
  merge in1 in2 ;
  by id ;
  keep id a b x y ;
run;

Second I would resist the urge to generate too complex a web of macro code. It will make the programs harder to understand for the next programmer. Including yourself two weeks later. Your particular example does not look like something that is worth coding as a macro. You are not really typing less information, just using a few commas in place of where your SQL code would have had keywords like FROM or JOIN.

Now to answer your actual question. To pass in a list of values to macro use a delimited list. When at all possible use space as the delimiter, but especially avoid using comma as the delimiter. This will be easier to type, easier to pass into the macro and easier to use since it matches the SAS language as you can see in the data step above. If you really need to generate code like SQL syntax that uses commas then have the macro code generate them where needed.

%macro ij
(x=    /* First dataset name */
,y=    /* Second dataset name */
,by=   /* BY variable list */
,to=   /* Output dataset name. If empty use data step to generate DATAn work name */
,xc=   /* Variable list from first dataset */
,yc=   /* Variable list from second dataset */
);
%if not %length(&to) %then %do;
* Let SAS generate a name for new dataset ;
  data ; run;
  %let to=&syslast ;
  proc delete data=&to; run;
%end;
%if not %length(&xc) %then %let xc=*;
%if not %length(&yc) %then %let yx=*;
%local i sep ;
proc sql ;
 create table &to as
   select
%let sep= ;
%do i=1 %to %sysfunc(countw(&by)) ;
  &sep.T1.%scan(&by,&i)
  %let sep=,;
%end;
%do i=1 %to %sysfunc(countw(&xc)) ;
  &sep.T1.%scan(&xc,&i)
%end;
%do i=1 %to %sysfunc(countw(&yc)) ;
  &sep.T2.%scan(&yc,&i)
%end;
   from &x T1 inner join &y T2 on
%let sep= ;
%do i=1 %to %sysfunc(countw(&by)) ;
  &sep.T1.%scan(&by,&i)=T2.%scan(&by,&i)
  %let sep=,;
%end;
 ;
quit;
%mend ij  ;

Try it:

options mprint;
%ij(x=sashelp.class,y=sashelp.class,by=name,to=want,xc=age,yc=height weight);

SAS LOG:

MPRINT(IJ):   proc sql ;
MPRINT(IJ):   create table want as select T1.name ,T1.age ,T2.height ,T2.weight from sashelp.class
T1 inner join sashelp.class T2 on T1.name=T2.name ;
NOTE: Table WORK.WANT created, with 19 rows and 4 columns.

MPRINT(IJ):   quit;