Calling a Macro variable from a macro variable

[jlcalbre]

I have several lists of ID numbers that are stored in macro variables.  I have a macro that executes an SQL query and when I call the macro, I need to be able to specify which list to use.  If I just put the macro variable in the macro call, I get an error that says more arguments have been passed than are specified.  An example of what I have is below.

%let list1 = 101,102,103,104,105;

%let list2 = 106,107,108,109,110;

%macro test(list);

proc sql;

connect to mysql(server);

create table table1 as select * from connection to mysql (

select * from dataset where id in (&list));

disconnect from mysql;

quit;

%mend;

%test(&list1);

Hopefully this makes sense with what I am trying to do.  Anyone have an idea of how this can work?

[ieva]

Try using

%test(list1);

if you add & before that, macro variable is resulved already in this step, that is why it fails.

[Tom]

For this to work the macro code will need to change to :

... in (&&&list) ...

[Tom]

The problem is getting the commas past the macro call.

You can quote them.

  %test(%str(&list1));

You should also just test whether you need the commas at all.  You do not need them for the IN operator in normal SAS, but they might be required by MYSQL.

In SAS code you can write :

data new;

   set old;

  where id in (101 102 103);

run;

[jlcalbre]

The commas are rquired since this is a pass through to mysql.  The quoting worked perfectly.  I figured there was something simple I was missing.  Thanks!

[Linlin]

change

%let list1 = 101,102,103,104,105;

%let list2 = 106,107,108,109,110;

to

%let list1 = %str(101,102,103,104,105);

%let list2 = %str(106,107,108,109,110);

[SASJedi]

The problem is that when you call the macro, the commas in the macro variable make it look as if there are too many parameters for the macro:

%let list1 = 101,102,103,104,105;
%test(&list1);

This will resolve to:

%test(101,102,103,104,105);

and that looks like a macro call with several parameters, not just one.  You will need to mask the commas using an execution-time macro quoting function like SUPERQ.  If you choose SUPERQ, your code would look like this:

 
%let list1 = 101,102,103,104,105;
%let list2 = 106,107,108,109,110;
 
%macro test(list);
 
proc sql;
connect to mysql(server);
create table table1 as select * from connection to mysql (
select * from dataset where id in (&list));
disconnect from mysql;
quit;
 
%mend;
 
%test(%SUPERQ(list1));

I have tested this solution, and it works fine in SAS9.3 on Windows 7.