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

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

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

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.


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 5,987

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

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.

View solution in original post


All Replies
Respected Advisor
Posts: 2,161

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

use

 

%nrbquote(&joincondition)

--
Paige Miller
Contributor
Posts: 68

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

[ Edited ]
Posted in reply to PaigeMiller

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

Super User
Posts: 8,590

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 68

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

Posted in reply to KurtBremser

@KurtBremser 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.

Contributor
Posts: 68

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

And the input is validated for semicolons, the words proc, sql, update, drop, delete, alter, etc.
Solution
2 weeks ago
Super User
Posts: 5,987

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

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.

Contributor
Posts: 68

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

Posted in reply to Astounding
@Astounding Well this was one educated guess! Worked like a charm, thank you very much!
Super User
Posts: 12,148

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

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:

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 133 views
  • 1 like
  • 5 in conversation