proc sql 中的"WITH DATA UNIQUE PRIMARY INDEX"错误(来自 Teradata)

Question

我正在使用SAS通过连接到TeraData来检索数据(原始代码是用TeraData编写的，并因此复制到SAS平台。但是我收到以下错误：

**) WITH DATA UNIQUE PRIMARY INDEX (x_account_number)
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, (, ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, 
INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.**  

这是我的代码：

PROC SQL ;
CONNECT TO teradata AS ConDbms (MODE=teradata) ;
CREATE TABLE Step_1 AS                             
SELECT *
FROM Connection to ConDbms
(
SELECT Account_Id,
Customer_Id
FROM T0101_Customer_Account_Relat 
WHERE Cust_Acct_Relat_Type_Code = 101 
AND Cancellation_Date > Date 
AND account_id NOT IN (SELECT account_id 
FROM T0100_Account 
WHERE Account_Close_Date < DATE)
)WITH DATA UNIQUE PRIMARY INDEX (Account_Id, Customer_Id)
DISCONNECT FROM ConDbms ;
QUIT ;

Answer 1

这是特定于数据库的 SQL，无法被 SAS 解析器识别，因此可以使用 SAS EXECUTE 语句在 Teradata 中创建表Step_1：

PROC SQL ;
CONNECT TO teradata AS ConDbms (MODE=teradata) ;
EXECUTE( CREATE TABLE Step_1 AS
( SELECT Account_Id, Customer_Id FROM T0101_Customer_Account_Relat
WHERE Cust_Acct_Relat_Type_Code = 101 AND Cancellation_Date > Date
AND account_id NOT IN (
SELECT account_id FROM T0100_Account WHERE Account_Close_Date < DATE)
) WITH DATA UNIQUE PRIMARY INDEX (Account_Id, Customer_Id)
) BY ConDbms;
DISCONNECT FROM ConDbms ; 
QUIT ;

或者，要将Step_1创建为 SAS 表，请不要使用 Teradata 特定的 DDL 子句

PROC SQL ;
CONNECT TO teradata AS ConDbms (MODE=teradata) ;
CREATE TABLE Step_1 AS                             
SELECT *
FROM Connection to ConDbms
(
SELECT Account_Id,
Customer_Id
FROM T0101_Customer_Account_Relat 
WHERE Cust_Acct_Relat_Type_Code = 101 
AND Cancellation_Date > Date 
AND account_id NOT IN (SELECT account_id 
FROM T0100_Account 
WHERE Account_Close_Date < DATE)
);
DISCONNECT FROM ConDbms ;
QUIT ;