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 .  ... View more

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; ... View more

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. ... View more

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. ... View more