BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I am making a SQL phrase which is longer that 262 characters and get the following message:

WARNING: The quoted string currently being processed has become more than 262 characters long. You may have unbalanced quotation marks.

Here is the statement causing the problem. My question is, how do I get around the problem?
OR POSITION_DEFINITION.POSITION_CLASS_DESC LIKE "POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Vice' OR POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS
'Chan'","POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Dir' OR POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Vice Pres' OR POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS
'VC'","POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Dean' OR POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Controller' OR POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Bursar' OR
POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Treasurer' OR POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Assoc Dir' OR POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS
'Chief'","POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Chair' OR POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Chair' ","POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Mgr' OR
POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Manager'

Incidentally, this is in a macro within a stored process.

Thank you in advance!
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
One thing you could do is alias your table POSITION_DEFINITION so that instead of having POSITION_DEFINITION.POSITION_CLASS_DESC you would have something like: PD.POSITION_CLASS_DESC.

I generally do this in my FROM clause

[pre]
from POSITION_DEFINITION as PD;
[/pre]

This would save you a bunch of characters.

One thing that troubles me in your generated code is this:
[pre]
OR POSITION_DEFINITION.POSITION_CLASS_DESC LIKE
"POSITION_DEFINITION.POSITION_CLASS_DESC CONTAINS 'Vice' ...
[/pre]

...because it seems as if the quoted string following the LIKE is what you REALLY want and what appears before the LIKE may be leftover from some earlier testing. So when you do get your quoted string under 262 characters, I wonder whether you will really get the results you think you will get or whether your macro is actually generating a valid WHERE clause.

cynthia
deleted_user
Not applicable
Thanks Cynthia, for the aliasing hint and for the eagle eyes on the code! I think the glitch on the CONTAINS and LIKE bits in my code are from the old adage of "Can't see the forest for the trees" syndrome. I will definitely use your aliasing hint and have stopped development work on my project, to review (SLOWLY...) all of my code to date.

Thanks again!

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
  • 1007 views
  • 0 likes
  • 2 in conversation