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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.