SAS EG 4.1 SQL REGEXP_LIKE Help

[dc88310]

Hi Everyone!

Thanks for helping!

I have SAS SQL code I am currently writing and I am banging my head against a solid brick wall right now. The code is below.

In the code... you will see a regexp_like statement. This is where I am having difficulty. As a background... I am scraping text. Some of the text i am scraping are words and some are like any text values with dollar amounts, so regexp works best for the various searches i am trying to do. The field i am scraping is a free text field. Since there are thousands of people that send messages, case is often times an issue. I need to ignore all case in the text in order to get the messages i am querying. In the case that i am currently working on, "helium" is my keyword for searching.

I have basic SQL to pull the data i need. I have 3 parameters setup in EG 4.1... Start Date, End Date and Search String. I have setup the parameters on the code piece to search for each item. Everything runs fine until until i add the piece to make the search case insensative, then i get

 ORA-00907: missing right parenthesis....

Here is my code:

data _null_;
      call symput("Start_Date",put(&START_DATE,date9.));
      call symput("End_Date",put(&END_DATE,date9.));
run;

%put Start Date Equal To: &START_DATE;
%put End Date Equal To: &END_DATE;

Proc SQL noprint;
connect to oracle (user='XXXXXX' orapw='********' path=XXXXX preserve_comments);
option missing = '';
create table work.TEMP_PCTEXTSC as select * from connection to oracle 
  ( SELECT/*+ INDEX (AO ODR_PK) USE_NL(AO)  */
        distinct AM.DT_CRTD,
	  AM.USR_CRTD,
	  AO.ODR_NUMBER,
	  AM.Message_Text as ORIGINAL_MESSAGE,
	  AM.NETWORK_CODE,
	  AD.STE_CODE,
	  ZIP_ZIP_CODE
	  	  FROM  ATS_ORDERS             AO,
				ATS_Messages		   AM,
				ATS_ADDRESSES		   AD
     WHERE AM.DT_CRTD >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))
     AND   AM.DT_CRTD <  trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)
	 AND   REGEXP_LIKE (AM.MESSAGE_TEXT,(%STR(%'&Search_String%','i'))
	 AND   AO.ODR_NUMBER = AM.ODR_ODR_NUMBER
     AND   AO.ODR_NUMBER = AD.ODR_ODR_NUMBER 
     AND   AO.VERSION    = AM.ODR_VERSION
	 AND   AM.MSG_TYPE IN ('ASK','ANS')
	 and   AM.DIRECTION = 'I' 
	 AND   AO.Express_Product_Indicator = 'N'
	 AND   AO.CPY_ABBR <> ALL ('030','042','430','431','549','510','042')
                       
  );
Disconnect from Oracle;
Quit;

If I take out the case insensative piece, 'i', it works fine.

 AND   REGEXP_LIKE (AM.MESSAGE_TEXT,(%STR(%'&Search_String%')))

and this works great as well:

REGEXP_LIKE (AM.MESSAGE_TEXT,'helium','i')

It's when i have the parameter from SAS in there along with the case sensative statement 'i' that it throws the error.

Does anyone have any idea what is wrong with my Syntax. From my research, it is definately some sort of Syntax error but i cannot for the life of me figure out where in the statement it is or a different placement. I have been able to find no examples online anywhere of a regexp_like statement with a user input parameter. Your help is desperately appreciated.

DC

 

[dc88310]

I found the answer finally... the line of code that worked was:

 REGEXP_LIKE (AM.MESSAGE_TEXT,%unquote(%str(%')&Search_String%str(%')),'i')