I'm currently trying to automate through SAS DI a piece of code that I've written in SAS EG.
While it runs without any issue in SAS EG, DI is returning an error on a SQL statement that is executed on my Teradata server.
the error is the following:
Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -,
'.', /, , =, , =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT,
IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.
and this is the line that Is causing the error:
SELECT CAST(EV_ID as VARCHAR(20)) AS EV_ID.
This cast statement is necessary as EV_ID is a BIGINT and SAS wouldn't be able create a table with this field otherwise.
The full code is the following:
proc sql;
connect to teradata (user=&tera_usr pass="&tera_pw" server=tdprod);
CREATE TABLE OVERALL_SAMPLE_TF AS SELECT * FROM connection to teradata(
SELECT A.EV_ID, A.CreatedDate, A.CompletionDate, A.SortCode,
A.SenderBIC, A.ReceiverBIC, A.NumberOfHitsPerAlert,
A.Level1ExecutionUser, A.Level2ExecutionUser, A.Final_Level,
A.Currency, A.Iban, A.Account_Number, A.T_Transaction, A.Last_Operator,
A.MessageID, A.Message_Type, A.Nature, A.Decision_Type, A.Branch_Contact,
A.Other_Bank_Contact, A.CompletionDate_TIMESTAMP,
A.Amount,
B.Franchise, B.Sub_Franchise, B.Sub_Sub_Franchise
FROM
(SELECT X.*,Y.DIV_ID
FROM
( SELECT CAST(EV_ID as VARCHAR(20)) AS EV_ID,
CreatedDate, CompletionDate, SortCode,
SenderBIC, ReceiverBIC, NumberOfHitsPerAlert,
Nonblocking, Level1ExecutionUser, Level2ExecutionUser, Final_Level,
Currency, Iban, Account_Number, T_Transaction, Last_Operator,
MessageID, Message_Type, Nature, Decision_Type, Branch_Contact,
Other_Bank_Contact, CompletionDate_TIMESTAMP,
Amount
FROM BAC_AML.EVENT_STATUS_ALERT_TF WHERE
CompletionDate >= ADD_MONTHS(current_date - EXTRACT(DAY FROM current_date)+1, -1)
AND CompletionDate <= (current_date - extract(day from current_date)+1)-1
AND CompletionDate NE '99991231' (date, format 'yyyymmdd')
AND Final_Level>0
AND UPPER(DECISION_TYPE) IN ('PASS', 'PASS_L1', 'FAIL', 'UNQUEUE', 'RECHECKED')
QUALIFY ROW_NUMBER() OVER (PARTITION BY EV_ID
ORDER BY CompletionDate_TIMESTAMP DESC) = 1
) AS X
LEFT JOIN
(SELECT CAST(EV_ID as VARCHAR(20)) AS EV_ID, CAST(DIV_ID as VARCHAR(20)) AS DIV_ID
FROM BAC_AML.TF_FRANCHISE
) AS Y
ON X.EV_ID=Y.EV_ID
) AS A
LEFT JOIN
(SELECT CAST(FR_KEY as VARCHAR(20)) AS FR_KEY,
L1_Empos_Name, L2_Emp_Posn_Name, L3_Emp_Posn_Name,
L4_Emp_Posn_Name, Franchise, Sub_Franchise, Sub_Sub_Franchise,
ORG_MODEL_LINK
FROM BAC_AML.FRANCHISE_LOOKUP
WHERE UPPER(FRANCHISE) IN ('PBB','CPB','ULSTER','NWM')
) AS B
ON B.FR_KEY=A.DIV_ID;
);
disconnect from teradata;
quit;
Please would you be able to help?
thank you.
