BookmarkSubscribeRSS Feed
Venkat4
Quartz | Level 8

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!

3 REPLIES 3
Astounding
PROC Star

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.

 

Good luck.

ballardw
Super User

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.

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1143 views
  • 0 likes
  • 3 in conversation