topic Re: Macros in SAS Programming

Macros

leisas — Fri, 11 Jan 2013 19:45:49 GMT

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.

Re: Macros

Reeza — Fri, 11 Jan 2013 20:03:49 GMT

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

Re: Macros

Astounding — Fri, 11 Jan 2013 20:24:58 GMT

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.

Re: Macros

PaigeMiller — Fri, 11 Jan 2013 20:50:33 GMT

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)

Re: Macros

leisas — Fri, 11 Jan 2013 20:55:36 GMT

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.

Re: Macros

Reeza — Fri, 11 Jan 2013 21:00:32 GMT

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

Re: Macros

leisas — Fri, 11 Jan 2013 21:01:42 GMT

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.

Re: Macros

Quentin — Fri, 11 Jan 2013 21:08:37 GMT

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;

Re: Macros

ballardw — Fri, 11 Jan 2013 21:18:52 GMT

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.

Re: Macros

leisas — Fri, 11 Jan 2013 21:28:27 GMT

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!!

Re: Macros

Tom — Fri, 11 Jan 2013 21:30:11 GMT

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

Re: Macros

Reeza — Fri, 11 Jan 2013 21:33:48 GMT

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

Re: Macros

art297 — Fri, 11 Jan 2013 22:00:58 GMT

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