PROC SQL - using CONTAINS and Variables

[EbethF]

hello, I am struggling with syntax for my PROC SQL statement. I am pulling data from a very large database table from an ODBC link, and want to specify records based on the first two letters of a text field.

PROC SQL;

     CONNECT TO ODBC (DSN='CFD' USER=sa PWD=Password);

     CREATE TABLE SRORD_DETAIL AS SELECT * FROM CONNECTION TO ODBC (select SRORD_DET_ID,SRORDER_ID,LINE_NUMBER,

PROD_ID, PACKSIZE_ID, QTTY_ORDERED, COMMENT,

CUSTOMER_KEY

FROM SRORD_DETAIL

where CUSTOMER_KEY CONTAINS ‘OR’ );

    QUIT;

I can't seem to make the last WHERE statement work for syntax, I get this error:

ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'CONTAINS'. :

       [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

(example of a record I want to keep in the image attached.)

help appreciated!

[SuryaKiran]

Did you try where CUSTOMER_KEY like 'OR%'

[PGStats]

Note:

select SRORD_DET_ID, SRORDER_ID, LINE_NUMBER, PROD_ID, PACKSIZE_ID, QTTY_ORDERED, COMMENT, CUSTOMER_KEY
FROM SRORD_DETAIL
where CUSTOMER_KEY CONTAINS ‘OR’

in your code is NOT a proc SQL query. It is a query that is transmitted by proc SQL to your SQL Server. It must obey the syntax rules of SQL Server.

[EbethF]

hmmm, okay. thank you for that.

[EbethF · Accepted solution]

thanks to both of you, a hybrid of those hints worked with this:

PROC SQL;

CONNECT TO ODBC (DSN='CFD' USER=sa PWD=Password);

CREATE TABLE SRORD_DETAIL AS SELECT * FROM CONNECTION TO ODBC(select SRORD_DET_ID,SRORDER_ID,LINE_NUMBER,

PROD_ID,

PACKSIZE_ID,

QTTY_ORDERED,

COMMENT,

CUSTOMER_KEY

from srord_detail

WHERE CUSTOMER_KEY LIKE ('OR%'));

QUIT;

[s_lassen]

SQL Server does not have the CONTAINS operator like SAS SQL. To get the same result as with SAS "contains 'OR'" you will have to use "like '%OR%'". But it sounds like using "like 'OR%'" gives you what you want, so you should probably use that, as it is much faster.