topic Re: PROC SQL - using CONTAINS and Variables in SAS Programming

PROC SQL - using CONTAINS and Variables

EbethF — Thu, 17 May 2018 20:11:14 GMT

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!

Re: PROC SQL - using CONTAINS and Variables

SuryaKiran — Thu, 17 May 2018 20:17:52 GMT

Did you try where CUSTOMER_KEY like 'OR%'

Re: PROC SQL - using CONTAINS and Variables

PGStats — Thu, 17 May 2018 20:48:28 GMT

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.

Re: PROC SQL - using CONTAINS and Variables

EbethF — Thu, 17 May 2018 20:55:44 GMT

hmmm, okay. thank you for that.

Re: PROC SQL - using CONTAINS and Variables

EbethF — Thu, 17 May 2018 21:39:09 GMT

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;

Re: PROC SQL - using CONTAINS and Variables

s_lassen — Fri, 18 May 2018 12:23:12 GMT

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.