Macros

[leisas]

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.

[Reeza]

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]

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]

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]

I still got the same error message

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]

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]

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;

[Tom]

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]

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]

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)

[leisas]

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]

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]

: 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;