BookmarkSubscribeRSS Feed
leisas
Calcite | Level 5


I would need help with how to correctly use multiplle macros in where clause

For instnace, I create some macros

%LET Amantadi = ('17520', '17521', '17530');

%LET Rimantad = ('00730');

%LET Tamiflu = ('98980', '98981', '26729', '73441');

%LET Relenza = ('92221');

If I refer them in WHERE clause, say, WHERE GCN in (&Amantadi,&Rimantad,  &Tamiflu,  &Relenza), an error message was generated.

If I create another macro to combine the 4,  %let GCNs = (&Amantadi,&Rimantad,  &Tamiflu,  &Relenza);  the result look like ('17520', '17521', '17530'), ('00730'),('98980', '98981', '26729', '73441'), ('92221');

so how to do it correctly as coding like this WHERE GCN in ('17520', '17521', '17530'), ('00730'),('98980', '98981', '26729', '73441'), ('92221');

Any suggestion helps.

Leigh R.

12 REPLIES 12
Reeza
Super User

Lists don't have to be comma delimited in a where clause in a data step. I'm not sure in a SQL step...

%LET Amantadi = '17520' '17521' '17530';

%LET Rimantad = '00730';

%LET Tamiflu = '98980' '98981' '26729' '73441';

%LET Relenza = '92221';



WHERE GCN in (&Amantadi. &Rimantad.   &Tamiflu.  &Relenza);

leisas
Calcite | Level 5

Reeza,

Thank you for responding my questions.

As you advised, I changed code without brackets

%LET Amantadi = '17520', '17521', '17530';

%LET Rimantad = '00730';

%LET Tamiflu = '98980', '98981', '26729', '73441';

%LET Relenza = '92221';


and coded in WHERE clause

AND GCNs IN (&Amantadi,&Rimantad,&Tamiflu,&Relenza)


The log message is

ERROR: Error Code: -102  ASA Error -131: Syntax error near '17520'


I tried the other way

%LET Amantadi = '17520', '17521', '17530';

%LET Rimantad = '00730';

%LET Tamiflu = '98980', '98981', '26729', '73441';

%LET Relenza = '92221';

%LET GCNS = &Amantadi,&Rimantad,&Tamiflu,&Relenza;

%PUT GCNS=&GCNS;

Log result was correct, but when I ran the pgm with code

AND GCNS IN &GCNS

the log message still "ERROR: Error Code: -102  ASA Error -131: Syntax error near '17520'

I expect the macros can result in WHERE clause as

WHERE GCNS in ('17520','00730','98980','92221','17521','17530','98981','26729','73441')   ----- by the way, I pasted wrong WHERE clause value in my original email. Astounding was correct, my original WHERE clause won't work.

At this point, any idea why I got the error message?

Thank you all for any suggestions.



Reeza
Super User

Smiley Happy You removed the commas from the macro variables but you left them in the where clause, between the different macro variables Remove them all and the errors will go away. I put periods to make sure things resolve but others would say that's incorrect.

%LET GCNS = &Amantadi. &Rimantad. &Tamiflu. &Relenza.;

leisas
Calcite | Level 5

I still got the same error message Smiley Sad

I copied my code here, please see if I misunderstood you.

%LET Amantadi = '17520', '17521', '17530';

%LET Rimantad = '00730';

%LET Tamiflu = '98980', '98981', '26729', '73441';

%LET Relenza = '92221';

%LET GCNS = &Amantadi. &Rimantad. &Tamiflu. &Relenza.;

/*%LET GCNS = &Amantadi,&Rimantad,&Tamiflu,&Relenza;*/

%LET HC = ('A', 'J', 'K', 'L', 'S');

%PUT GCNS=&GCNS;

. . . . . . . . . . . . . .

. . . . . . . . . . . . . .

. . . . . . . . . . . . . .

.. . . . . . . . . . . . . .

WHERE

PAID_DATE_YEAR_MONTH = '201212'                                                                                                                                                                                                                                                         

AND GCNS IN &GCNS

Log: ERROR: Error Code: -102  ASA Error -131: Syntax error near '17520'

Thank you so much!!

Reeza
Super User

It should be the following.  But is this embedded in a macro? If so you may need to turn on the minoperator depending on your versions of SAS. 

AND GCNS IN (&GCNS);

Quentin
Super User

Hi,

Sounds odd.  I woudl try simpler case and see if you can get it working.

Below works for me.

%LET Amantadi = 'Alfred','Alice';
%LET Rimantad = 'Mary','Philip';

proc print data=sashelp.class;
 where name in (&Amantadi,&Rimantad);
run;
The Boston Area SAS Users Group (BASUG) is hosting our in person SAS Blowout on Oct 18!
This full-day event in Cambridge, Mass features four presenters from SAS, presenting on a range of SAS 9 programming topics. Pre-registration by Oct 15 is required.
Full details and registration info at https://www.basug.org/events.
Tom
Super User Tom
Super User

Why are you including the commas?  It will be a lot easier to combine the macro variables (especially when sometimes one or more is empty) if you just use spaces between the values.  SAS does not need the commas.

67   data check;

68    set sashelp.class;

69    where sex in ('F' 'M');

70   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.

      WHERE sex in ('F', 'M');

NOTE: The data set WORK.CHECK has 19 observations and 5 variables.

NOTE: DATA statement used (Total process time):

      real time           0.72 seconds

      cpu time            0.03 seconds

Astounding
PROC Star

Leigh,

As you have indicated, the first step to solving any sort of problem like this is to understand what the generated SAS statement should look like.  In this case, you have the wrong picture.  The WHERE statement at the bottom of your post would not work.

If you can control what your macro variables look like, then Reeza is 100% correct.  However, if the macro variables are cast in concrete including all the parentheses, you could always try:

where GCN in &Amantadi or GCN in &Rimantad or GCN in &Tamiflu or GCN in &Relenza;

Good lucki.

PaigeMiller
Diamond | Level 26

However, if the macro variables are cast in concrete including all the parentheses, you could always try:

Or use text manipulation functions to remove the parentheses (and any other unwanted characters)

--
Paige Miller
leisas
Calcite | Level 5

Thank you, Astounding.

Sorry, I pasted wrong WHERE clause value in my original email. but You are correct,

I expect the macros can give me the same result as WHERE GCNS in ('17520','00730','98980','92221','17521','17530','98981','26729','73441').

So far I have not get it worked yet.

Thank you for any suggestions.

ballardw
Super User

It may be time to post the code showing how you are using the where clause. Not all are where clauses are treated the same.

art297
Opal | Level 21

: FWIW, I would leave the commas in.  Your error message appears to be a sybase error and, according to its documentation, commas are supposed to be used.  In SAS, itself, they don't have to be there in either the data step or proc sql, but will work either way.

What it looks like you are missing, though (from the code you posted), are the parentheses.

I don't have access to sybase, but the following runs correctly on sas:

%LET Amantadi = '17520','17521','17530';

%LET Rimantad = '00730';

%LET Tamiflu = '98980','98981','26729','73441';

%LET Relenza = '92221';

data have;

  input CGNs $;

  cards;

26729

15217

00731

00730

26000

26729

;

data want;

  set have;

  where CGNs in (&Amantadi,&Rimantad,&Tamiflu,&Relenza);

run;

proc sql;

  create table want as

    select *

      from have

        where CGNs in (&Amantadi,&Rimantad,&Tamiflu,&Relenza)

  ;

quit;

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 12 replies
  • 2148 views
  • 0 likes
  • 8 in conversation