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.
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);
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.
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.;
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!!
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);
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;
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
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.
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)
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.
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.
: 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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: