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!
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;
Did you try where CUSTOMER_KEY like 'OR%'
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.
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.