BookmarkSubscribeRSS Feed
jlcalbre
Calcite | Level 5

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?

6 REPLIES 6
ieva
Pyrite | Level 9

Try using

%test(list1);

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

Tom
Super User Tom
Super User

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

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

Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
SAS Super FREQ

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.

Check out my Jedi SAS Tricks for SAS Users

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1238 views
  • 0 likes
  • 5 in conversation