08-10-2016 11:34 AM
I have a query text created by a upstream process that I don't have any control. I use the table that has a column holding that query text
Simple Example --
(select x,y,z where a.abc = (NEW) and b.xyz = (OLD))
But the query text column is infact very complicated normally around 1000 characters or so and has so many sub-select and where conditions.
The issue I have is the values for the where conditions are not quoted. Since I am moving them to SAS and proc SQL needs a quote on the where conditions if it is a string value, I wanted to translate this query to add quotes to all the character variable values --- unless if there is a workaround without adding any single or double quote. In that query text numeric values are being coded as strings, but in that case I don't want any quotes on them, rather I want to convert them to numeric values.
Can someone show a simple way to accomplish this? my co-workers are trying bunch of regex and tranwrd etc., but I thought that is complicated. Thanks!
08-10-2016 11:58 AM
So parentheses sometimes remain as parentheses, and sometimes get changed to quotes. I hope you don't have any data values that should contain parentheses inside the quotes!
Here's an approach ... it's just a little tedious so I'm leaving the details to you.
The parentheses that should be changed all follow an equal sign. You should be able to use the INDEX function to locate occurrences of "= (" or "=(". Change that parenthesis to a quote. Then find the very next right-hand parenthesis (plenty of ways to do that, since INDEX already told you the position of the left-hand parenthesis) and change that to a quote.
08-10-2016 11:59 AM
Any chance you are working with the author of this post: https://communities.sas.com/t5/SAS-Procedures/need-help-to-modify-the-records-in-a-variable/m-p/2907...
The topic looks to be very similar.
08-10-2016 03:45 PM
Before you start editing the variable that contains those values you might need to investigate the current assigned length of the variable and the lengths of the contents. If you replace single characters such as ( and ) with (" or ") then the assigned length gets longer. If you do not have sufficient space remainging in the variable then the result will likely get truncated.
Example: variable X has property of length 52 and the current value is: (select x,y,z where a.abc = (NEW) and b.xyz = (OLD)). That contains 52 chracters. If you attempt to assign a new value of
(select x,y,z where a.abc = ("NEW") and b.xyz = ("OLD"))
then there are now 56 characters and 4 will not fit. The result is likely to be
(select x,y,z where a.abc = ("NEW") and b.xyz = ("OL
which is likely to not be the desired result.