topic Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql in SAS Programming

Macro Variable Value containing apostrophes used as on-Condition in proc sql

Criptic — Wed, 07 Feb 2018 12:38:09 GMT

Hello everybody,

as you can maybe guess from the title I'm a little bit lost and don't really know what to search for so here it goes:

I have a STP which takes as one of its arguments a on-condition. That condition can be freely entered by the user.

And a simple condition, e.g. a.key_value eq b.key_value, works perfectly fine. The problem arises when I want to a join like, e.g. a.key_value eq b.key_value and b.birthday eq '07FEB2018'd, I get the following error-message:

NOTE: Line generated by the macro variable "JOINCONDITION".
362 a.key_value eq b.key_value and b.birthday eq '07FEB2018'd
_
22
_
200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, ANY, BTRIM, CALCULATED, CASE,
INPUT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE, USER.

ERROR 200-322: The symbol is not recognized and will be ignored.

The input from the user is stored in a macro variable called JOINCONDITION and proc sql that gets executed is inside of macro.

When I do the exact same thing inside of SAS-Coding and not as part of a STP it works fine and I don't get why.

Coding inside STP:

/* For the user input let's say it is again a.key_value eq b.key_value and b.birthday eq '07FEB2018'd */

%macro test();
 /* Somethings that are done beforehand */
proc sql;
create table aaa as
select b.open, a.*
from mydata as a
left join myotherdata as b
on &JOINCONDITION;
run;
%mend test;

%test();

Now if I take that coding and paste into a "normal" SAS-coding and set the macro variable JOINCONDITION with %let it works fine:

%let JOINCONDITION = a.key_value eq b.key_value and b.birthday eq '07FEB2018'd;

%macro test();
 /* Somethings that are done beforehand */
proc sql;
create table aaa as
select b.open, a.*
from mydata as a
left join myotherdata as b
on &JOINCONDITION;
run;
%mend test;

%test();

Can somebody give me any pointers? I tried &JOINCONDITION. didn't make a difference. Enclosing in " " just created a cartesian product. Any help would be much appreciated.

Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql

PaigeMiller — Wed, 07 Feb 2018 12:42:44 GMT

use

%nrbquote(&joincondition)

Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql

Criptic — Wed, 07 Feb 2018 13:01:05 GMT

@PaigeMiller Thank you for your reply. Sadly the ERROR message stays the same.

Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql

Kurt_Bremser — Wed, 07 Feb 2018 13:08:14 GMT

Are you working on some jail time and a big fat liability suit? Because that's what's going to happen as soon as a user injects code that deletes all your tables or enters fraudulent data into a critical dataset.

NEVER, EVER LET USERS ENTER CODE INTO A PROCESS THAT DOES NOT RUN UNDER THEIR OWN USERID!!!

Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql

Criptic — Wed, 07 Feb 2018 13:11:55 GMT

@Kurt_Bremser Thank you for the concern but this is a non-issue with this as STP-Server-User is not allowed to write, update or delete data in the library it can access.

Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql

Criptic — Wed, 07 Feb 2018 13:14:25 GMT

And the input is validated for semicolons, the words proc, sql, update, drop, delete, alter, etc.

Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql

Astounding — Wed, 07 Feb 2018 14:25:08 GMT

As an educated guess, here is what I would try inside the macro:

on %unquote(&JOINCONDITION);

It seems like your process somehow quotes characters in &JOINCONDITION, and SQL is notoriously poor at unquoting them in time for correct parsing.

Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql

ballardw — Wed, 07 Feb 2018 16:00:41 GMT

Please post logs, especially with errors, into a code box opened using the forum {I} menu icon.

Note that the main message window reformats text and reduces the information in the error message.

For instance from your post:

ERROR 200-322: The symbol is not recognized and will be ignored.

The _ character was originally underneath a position in the code where SAS determined the error.

Also post the entire proc or data step generating the error. Sometimes the actual cause is something earlier in the code that wasn't ended correctly but the error isn't triggered until later in the code.

Since the message says expecting values like BTRIM, CALCULATED and such I suspect that there is something else going on. I might guess that for some reason the whole macro variable is not being considered as a join condition but just some string not resolving. You might mention HOW the users set that and which interface, Web Report Studio or something else as that is likely doing something to the entered values.

Proc SQL wants a QUIT not a RUN.

Re: Macro Variable Value containing apostrophes used as on-Condition in proc sql

Criptic — Thu, 08 Feb 2018 05:40:37 GMT

@Astounding Well this was one educated guess! Worked like a charm, thank you very much!