Your SAS programs, embedded in web apps and elsewhere

How to get around phrase longer than 262 characters

Reply
N/A
Posts: 0

How to get around phrase longer than 262 characters

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!
SAS Super FREQ
Posts: 8,743

Re: How to get around phrase longer than 262 characters

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
N/A
Posts: 0

Re: How to get around phrase longer than 262 characters

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!
N/A
Posts: 0

Re: How to get around phrase longer than 262 characters

Ask a Question
Discussion stats
  • 3 replies
  • 248 views
  • 0 likes
  • 2 in conversation