DATA Step, Macro, Functions and more

Single quotes in macro variable string for oracle

Reply
Occasional Contributor
Posts: 5

Single quotes in macro variable string for oracle

Hi All,

 

I am facing a problem with single quote in my macro variable below are the details .

 

 

 

I need query the orcale table using some parameters in the where condition .I have city is the column in the oracle table .in the city column i have text is like San'juan  when i am  passing this value to oracle .It is giving me the errror when i am passing this value to orcale using passthrough query .

 

%LET city= '%SAN'JUAN%' ;

%put &city. ;

 

PROC SQL ;
connect to oracle (user=&user orapw=&password path=&path buffsize=5000);
CREATE TABLE CCRPWEEK AS
SELECT * FROM CONNECTION TO ORACLE
(SELECT * FROM WKS_PAGE_LEVEL_TBL W
WHERE  (UPPER(city) LIKE  &city. ));
quit;

 

Can any one please let me know how we can handle this issue .

 

Thanks in advance ,

Ramesh

Super User
Super User
Posts: 7,060

Re: Single quotes in macro variable string for oracle

Posted in reply to Ramzreddy

You need balance your quotes. Double up the embedded quotes.

%LET city= '%SAN''JUAN%';
Trusted Advisor
Posts: 1,929

Re: Single quotes in macro variable string for oracle

[ Edited ]
Posted in reply to Ramzreddy

Ramzreddy wrote:

Hi All,

 

I am facing a problem with single quote in my macro variable below are the details .

 

 

 

I need query the orcale table using some parameters in the where condition .I have city is the column in the oracle table .in the city column i have text is like San'juan  when i am  passing this value to oracle .It is giving me the errror when i am passing this value to orcale using passthrough query .

 

%LET city= '%SAN'JUAN%' ;

%put &city. ;

 


Why do you have % signs inside the quotes?

 

If you want to match San'juan, this won't do it, because it will match %san'juan% (if it worked at all, which it doesn't)

 

You probably want something like:

 

%let city=SAN%nrstr(%')JUAN;
%put &city;

  and later

 

WHERE  (UPPER(city) LIKE  %nrstr(%')%nrbquote(&city)%nrstr(%'));
Frequent Contributor
Posts: 83

Re: Single quotes in macro variable string for oracle

[ Edited ]
Posted in reply to PaigeMiller

In Oracle the % signs are valid for matching 0 or more characters in a LIKE clause. Link.

 

Edited to add:

Also ensuring proper quoting on SQL passthrough between Oracle and SAS is a nightmare, I've wasted way too much time on this so I finally decided that in most cases the best way to handle any troublesome strings is to just create temporary Oracle tables containing the string I want to reference and access it that way rather than having SAS touch it at all.

Trusted Advisor
Posts: 1,929

Re: Single quotes in macro variable string for oracle

[ Edited ]

Sven111 wrote:

In Oracle the % signs are valid for matching 0 or more characters in a LIKE clause. Link.


Ah, got it, that helps me understand.

 

Then

 

%let city=%nrstr(%%)san%nrstr(%')juan%nrstr(%%);
%put &city;

 

Edited to add: Also ensuring proper quoting on SQL passthrough between Oracle and SAS is a nightmare, I've wasted way too much time on this so I finally decided that in most cases the best way to handle any troublesome strings is to just create temporary Oracle tables containing the string I want to reference and access it that way rather than having SAS touch it at all.

 

I believe the code I just gave will work, I haven't use the LIKE operator in Oracle which would require the % sign, but for other quoting I've gotten the SAS code passthrough to work as shown.

Frequent Contributor
Posts: 83

Re: Single quotes in macro variable string for oracle

Posted in reply to PaigeMiller

I may give it another shot next time it comes up and see if I can get it to work, but a while back I was dealing with this issue it wasn't nearly as straightforward a scenario as the original posters'.  I was dealing with many 50+ char strings that could have any printable ASCII character in practically any combination and as soon as I got one of the characters escaped or quoted properly, it broke on two others.  Just creating a temporary table in Oracle with the rows containing the strings I was interested proved to be a much easier and faster solution and I've generally just done it that way ever since.  Obviously this is a more manual process, but for my workflow it does the job well enough.

PROC Star
Posts: 102

Re: Single quotes in macro variable string for oracle

Posted in reply to PaigeMiller

PaigeMiller wrote:

Sven111 wrote:

In Oracle the % signs are valid for matching 0 or more characters in a LIKE clause. Link.


Ah, got it, that helps me understand.

 

Then

 

%let city=%nrstr(%%)san%nrstr(%')juan%nrstr(%%);
%put &city;

 

Edited to add: Also ensuring proper quoting on SQL passthrough between Oracle and SAS is a nightmare, I've wasted way too much time on this so I finally decided that in most cases the best way to handle any troublesome strings is to just create temporary Oracle tables containing the string I want to reference and access it that way rather than having SAS touch it at all.

 

I believe the code I just gave will work, I haven't use the LIKE operator in Oracle which would require the % sign, but for other quoting I've gotten the SAS code passthrough to work as shown.


I think your code will still cause problems, as the macro-quoted single quote will become unquoted when passed to Oracle, if not before. It seems to me that Tom's answer is the way to go:

%LET city= '%SAN''JUAN%';

Regards,

Søren

Occasional Contributor
Posts: 5

Re: Single quotes in macro variable string for oracle

Hi ,

 

I appreciate for yoor response on this post .

 

Basically i am using city as a prompt for stored process user can give any value with single quotes so i can not hadrd code and double the quotes in my scenario because some user may give 'Rapid'city' as a prompt this issue i faced for couple of strings 

 

I have to handle the situation like if get single quote in the string that should i use to pass in the oracle the table using pass through I also can not use temporary table since it is a huge transaction table.

 

Thanks again for your help.

PROC Star
Posts: 102

Re: Single quotes in macro variable string for oracle

Posted in reply to Ramzreddy
Ramzreddy wrote:

Hi ,

 

I appreciate for yoor response on this post .

 

Basically i am using city as a prompt for stored process user can give any value with single quotes so i can not hadrd code and double the quotes in my scenario because some user may give 'Rapid'city' as a prompt this issue i faced for couple of strings 

 

I have to handle the situation like if get single quote in the string that should i use to pass in the oracle the table using pass through I also can not use temporary table since it is a huge transaction table.

 

Thanks again for your help.


So the problem is that you have to prepare you macro variable after receiving a string with unmatched quotes from a user prompt.

I would try something like this:

data _null_;
city=upcase(symget('city'));
if substrn(city,1,1)="'" then
city=substrn(city,2); /* remove initial quote, if present */
if substrn(city,length(city))="'" then
substr(city,length(city))=' '; /* remove terminal quote, if present */
city=tranwrd(city,"'","''"); /* double remaining quotes */
city=cats("'%",city,"%'"); /* add quoting and wildcards */
call symputx('city',city);
run;

After which, your macro variable should be changed from 'Rapid'city' to '%RAPID''CITY%'. It is much easier to do this in a datastep than to mess with macro quoting.

 

Regards,

Søren

Occasional Contributor
Posts: 5

Re: Single quotes in macro variable string for oracle

I am not getting through this code below is the error .

 

1
2 %let city='Rapid'city';
-------
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.

3
4 %put &city.;
5
6 data _null_;
7 city=upcase(symget(&city.));
8 if substrn(city,1,1)="'" then
9 city=substrn(city,2); /* remove initial quote, if present */
10 if substrn(city,length(city))="'" then
11 substr(city,length(city))=' '; /* remove terminal quote, if present */
12 city=tranwrd(city,"'","''"); /* double remaining quotes */
13 city=cats("'%",city,"%'"); /* add quoting and wildcards */
14 call symputx('city',city);
15 run;
16
17
18 %put &city.;
ERROR: Open code statement recursion detected.

Occasional Contributor
Posts: 5

Re: Single quotes in macro variable string for oracle

Posted in reply to Ramzreddy

Attached the log 

 

2
3 data test;
4 city=upcase(Rapid'
-
388
4 ! city);
ERROR 388-185: Expecting an arithmetic operator.

5 if substrn(city,1,1)="'" then
6 city=substrn(city,2); /* remove initial quote, if present */
7 if substrn(city,length(city))="'" then
8 substr(city,length(city))=' '; /* remove terminal quote, if present */
9 city=tranwrd(city,"'","''"); /* double remaining quotes */
10 city=cats("'%",city,"%'"); /* add quoting and wildcards */
11 call symputx('city',city);
12 run;
4 city=upcase(Rapid'
-
76
4 ! city);
ERROR 76-322: Syntax error, statement will be ignored.

13 data test;
14 city=upcase(Rapid'city);
15 if substrn(city,1,1)="'" then
16 city=substrn(city,2); /* remove initial quote, if present */
17 if substrn(city,length(city))="'" then
18 substr(city,length(city))=' '; /* remove terminal quote, if present */
19 city=tranwrd(city,"'","''"); /* double remaining quotes */
20 city=cats("'%",city,"%'
-
49
20 ! "); /* add quoting and wildcards */
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.

21 call symputx('city',city);
22 run;

Super User
Super User
Posts: 7,060

Re: Single quotes in macro variable string for oracle

Posted in reply to Ramzreddy

You need to tell the users to enter valid values.  If they are adding the quotes around the value then they need to double the quotes inside the value.

 

Are they entering them into prompts in a stored process?  If so it should handle the macro quoting needed so they should NOT need to add the quotes around the outside.  In that case you can add the quotes yourself.  So if you have the macro variable MVAR you can add the single quotes using the QUOTE() function.

%let mvar=%sysfunc(quote(%superq(mvar),%str(%')));

If you really did end up with a value like in your example with three single quote characters then you might need to add more complex logic to convert that into a valid value.  So in your example you could use SUBSTR() to remove the first and last character and then apply the QUOTE() function.

 

Occasional Contributor
Posts: 5

Re: Single quotes in macro variable string for oracle

Thanks for you response TOM,

 

We have provided the drop down to the users to select the value in Stored process prompt .This value needs to pass to Oracle table on where condition .We can not change the text if we change the where condition will not satisfy and will get zero records .

 

 

We have a data with single quotes in the table .Once the value resolve in the macro with the single my code getting error .

 

I tried with you query it still not working . 

Super User
Super User
Posts: 7,060

Re: Single quotes in macro variable string for oracle

[ Edited ]
Posted in reply to Ramzreddy

If you are providing the value for the prompts then make sure you provide a valid value.   If the list of values includes the outer quotes like in your original example then any embedded quotes need to be doubled up.

So if the intent was to make a string that could be used with LIKE keyword to find any occurance of the word "don't" then the value the user should select is:

'%don''t%'

 

But if instead you are just letting the user pick values that are in some dataset then do NOT add any quotes in the user prompts.  Instead you can add the quotes later in the SAS code that processes the values.

 

So let the user pick between values like

SAN'JUAN
NEW YORK
LONDON

Then in your SAS program add the quotes to convert the macro variable into a string literal that Oracle will understand.  So if you just want to add the quotes you could do:

%let mvar=%sysfunc(quote(%superq(mvar),%str(%')));

Or you could also add the % for the LIKE operation at that time.

%let mvar=%sysfunc(quote(%str(%%)%superq(mvar)%str(%%),%str(%')));

If you are using an old version of SAS and the QUOTE() function does not support the optional second parameter then use TRANWRD() function to double up the embedded quotes.

%let mvar=%qsysfunc(tranwrd(%superq(mvar),%str(%'),%str(%'%')));
%let mvar=%unquote(%bquote('&mvar'));
Super User
Posts: 10,035

Re: Single quotes in macro variable string for oracle

Posted in reply to Ramzreddy
Not sure if this could work.


WHERE  (UPPER(city) LIKE  %unquote(%superq(city)) ))

Ask a Question
Discussion stats
  • 14 replies
  • 368 views
  • 0 likes
  • 6 in conversation