topic Macro in base sas in SAS Programming

Macro in base sas

kajal_30 — Tue, 17 Sep 2019 15:26:27 GMT

proc sql;
select name into : table
from country.DETAIL
where ID = 34
and CD = "abc"
;
quit;

proc sql;
select name into : table
from country.DETAIL
where ID = &first_id.
and CD = "abc"
;
quit;

First query is running fine and second throwing error:

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT,
GE, GET, GROUP, GT, GTT, HAVING, LE, LET, LT, LTT, NE, NET, OR, ORDER, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

can you please help why hard coding macro variable value is not giving any error but macro variable is giving error

Re: Macro in base sas

Jagadishkatam — Tue, 17 Sep 2019 15:31:52 GMT

Ideally it should not give error, could you please check the if the macro variable first_id is having the values

Please execute below code and check if it resolves to any value

%put &first_id;

Re: Macro in base sas

Kurt_Bremser — Tue, 17 Sep 2019 15:45:46 GMT

Please run the code again with

options symbolgen;

and then post the whole log, using the {i} button.

Re: Macro in base sas

ErikLund_Jensen — Tue, 17 Sep 2019 16:04:21 GMT

Hi @kajal_30

You get this error if the macro variable first_id exists, but is assigned an empty value. It resolves to blank in the select, and the where-statement becomes where ID = and CD = "abc", which is illegal syntax.

Re: Macro in base sas

Astounding — Tue, 17 Sep 2019 18:56:51 GMT

We can't see how &FIRST_ID was created. If that was done using macro quoting functions, it may contain unprintable characters that SQL doesn't detect and remove in time. If that's what happened, you need to remove those characters yourself:

where id = %unquote(&first_id.)