Hello,
I initialized the following macro variables such that these are their formats:
%let cycles= 9; /*this is num 6.*/
%let cycle_dates = 09NOV2019; /*this is date9.*/
using these I'm trying to pull data from teradata:
Proc SQL;
CONNECT TO teradata (user="%sysget(USER)" password="xx." tdpid=xx mode=teradata);
CREATE TABLE abc AS Select * From connection to teradata
(SELECT top 10 * FROM xx
where CYCL IN (&cycles.) and CYCL_DT IN (&cycle_dates.) );
DISCONNECT FROM TERADATA;
QUIT;
the formats for teradata variables are as follows:
CYCL: num 11.
CYCL_DT: date9.
I am getting this error even though the formats match:
ERROR: Teradata prepare: Syntax error, expected something like an 'OR' keyword or a 'TO' keyword or ',' between an integer and the
word 'NOV2019'. SQL statement was: SELECT top 10 * FROM xx
where CYCL IN (9) and CYCL_DT IN (09NOV2019).
there are no other notes or warning messages with this.
Would appreciate helping me understand what I'm doing wrong.
Thank you.
Try the below syntax for selecting 10 records from Teradata.
Proc SQL; CONNECT TO teradata (user="%sysget(USER)" password="xx." tdpid=xx mode=teradata); CREATE TABLE abc AS Select * From connection to teradata (SELECT * FROM xx where CYCL IN (&cycles.) and CYCL_DT IN (&cycle_dates.) sample 10 ); DISCONNECT FROM TERADATA; QUIT;
See if the code runs without the macro variables first.
Proc SQL; CONNECT TO teradata (user="%sysget(USER)" password="xx." tdpid=xx mode=teradata); CREATE TABLE abc AS Select * From connection to teradata (SELECT * FROM xx where CYCL =9 and CYCL_DT ='2019-11-09' sample 10 ); DISCONNECT FROM TERADATA; QUIT;
You are setting the macro variable cycle_dates to a string that looks like how SAS would PRINT the value of a date variable (IF the DATE9. format was attached to it.). You could use that in SAS code if you enclosed it in quotes and added the letter D so that is recognized as a date literal. SAS requires that the quoted string used in a date literal is valid for the DATE INformat.
Since you are using it to generate part of your Teradata code you want the macro variable to have a string that looks like how Teradata would expect your to represent a date value in code.
%let cycle_dates = '2019-11-09' ;
Hi @AJ_Brien Since you are using the Pass through facility to Teradata the SAS Syntax is not valid within the Query (Between connect and Disconnect statements).
where CYCL_NO IN (9) and CYCL_DT IN ('09NOV2019'd));
When you are attempting to open the data residing in Teradata using a Library within the client, SAS tries to apply its native formats to display the data in the best possible way.
However in your case the query is passed to the Teradata in its original form so you cannot apply the sas constructs within the query.
Please try this syntax and see if it works.
where CYCL in (9) and CYCL_DT in ('2019-11-09')
You may also consider changing your query to make use of the SAS Formats , here is an example.
libname teralib teradata server=server user=userid pwd=password;
Proc SQL; CREATE TABLE WORK.Bacardi AS SELECT * FROM teralib.xx(obs=10) where CYCL_NO IN (9) and CYCL_DT IN ('09NOV2019'd)); QUIT;
You have an extra closing round bracket at the end of your WHERE statement that possibly shouldn't be there.
Also please post complete SAS logs of your source code AND notes and errors. You are making it hard for us to see what is going on when you post just the bits you think are the problem.
You can't just stick a random WHERE statement it the middle of no where. And you cannot use Teradata style date literals outside of the code that you are explicitly sending to Teradata.
Thank you all for help!
Turned out there was an issue on my SAS teradata connect which is why the date even in the right format wasn't going through.
Appreciate everyone's input. Thank you so much 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: