ERROR: Teradata prepare:macro variable dates

[AJ_Brien]

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.

[r_behata]

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;

[AJ_Brien]

Thank you, I tried that. But got the same error.

[r_behata]

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;

[Tom]

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' ;

[AJ_Brien]

Thank you for your reply. I tried the following:

Proc SQL;
CONNECT TO teradata (user="%sysget(USER)" password="xx" tdpid=xx mode=teradata);
CREATE TABLE WORK.Bacardi AS
Select * From connection to teradata
(SELECT top 10 * FROM xx
where CYCL_NO IN (9) and CYCL_DT IN ('09NOV2019'd));
DISCONNECT FROM TERADATA;
QUIT;

that gave me this error:
ERROR: Teradata prepare: Syntax error, expected something like an 'OR' keyword or a 'TO' keyword or ',' between a string or a
Unicode character literal and the word 'd'.

The format of CYCL_DT in the teradata table is num date9.
Is that not the format I'm passing this in? The CYCL_DT values in the table look like these:
09NOV2019
10NOV2019......

I then tried changing to this: CYCL_DT IN ('2019-11-09'd))

But I still got the same error.

I then did this: CYCL_DT IN ("2019-11-09"d)), so I got really random results and the warning message: WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.

[r_behata]

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;

[AJ_Brien]

Thank you for your input, I really appreciate the guidance.

I tried doing this: where CYCL in (9) and CYCL_DT in ('2019-11-09')

but I end up getting random results, seems like my where condition is completely ignored. I only get this warning, and no errors or notes:

where CYCL_NO IN (9) and CYCL_DT IN ('2019-11-09'));
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.

The issue is definitely in the CYCL_DT condition because just doing the CYCL_NO alone works.

I have a bunch of code written using pass through, hence preferred following the same method for this query too.

[SASKiwi]

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.

[Tom]

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.

[AJ_Brien]

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 🙂