Help cannot figure out why i am getting: ERROR 22-322: Syntax error, expecting one of the following

[Gayle]

I put in the MPRINT option and it shows the macro statement.  I cannot figure out why i am getting the errors. can anyone spot the errors

MPRINT(FINALLOAD):   connect to teradata as eixx (username="bdmdxxx"
password="{SAS002}AXX" tdpid=TDP1 mode=teradata
fastload=yes);
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.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
MPRINT(FINALLOAD):   create table EIWORK05.CMIHistKygjtest as select * from connection to eiwp
(select MAX(baseid) as maxhistky FROM ( SELECT COALESCE(MAX(hist_ky), 0) AS baseid FROM
HISTORYgjtest UNION all SELECT COALESCE(MAX(hist_ky), 0) AS baseid FROM
History_gjtestwae ) as foo ) WITH DATA NO PRIMARY INDEX;
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT, SET, VALUES.
ERROR 200-322: The symbol is not recognized and will be ignored.
ERROR 22-322: Syntax error, expecting one of the following: ;, VALUES.
ERROR 76-322: Syntax error, statement will be ignored.
MPRINT(FINALLOAD):   insert into HISTORYgjtest ("hist_ky,
cust_id, cust_id_type, cell_ky,as_of_dt,      insrt_dt_tm") SELECT (select maxhistky from) +
pr.hist_ky_add as hist_ky, "pr.cust_id,pr.cust_id_type,pr.cell_ky," (SELECT AS_OF_DT from
WAE_CALENDAR where MO_AGO = 0) as as_of_dt, CAST(CURRENT_TIMESTAMP(0) AS
TIMESTAMP(0)) AS insrt_dt_tm FROM (select "cust_id,cust_id_type,cell_ky," rank(cust_id ||
cust_id_type || cast(cell_ky as varchar(16))asc) as hist_ky_add from
CHD_LOAD_W16 ) pr;

[japelin]

If possible, please provide the code as well.
From the output location in the error log, it seems to be an error in the connect to statement, do you get the same error by executing just this statement in proc sql?

[Gayle]

let me try to that thanks!

[Gayle]

ok I commented out everything but the connection and it works. so I know it it not that! thanks I will continue in this way so I can see what is creating the error. Thanks for the suggestion

[ChrisNZ]

We don't see enough to comment on the connection error.

About the query, after formatting it a bit (why the random letter cases?) it looks like you wan to run this:

create table EIWORK05.CMIHistKygjtest as 
select * 
from connection to eiwp
 ( select MAX(baseid) as maxhistky 
   FROM ( SELECT COALESCE(MAX(hist_ky), 0) AS baseid 
          FROM HISTORYgjtest 
            UNION all 
          SELECT COALESCE(MAX(hist_ky), 0) AS baseid 
          FROM History_gjtestwae 
        ) as foo 
 )
 WITH DATA NO PRIMARY INDEX;

The part in blue is Teradata code, the part in black is SAS code. SAS doesn't support the  WITH  keyword.

[Kurt_Bremser]

Compare this:

connect to teradata as eixx

with this:

select * from connection to eiwp

You need to use the same connection name in both statements.

[Gayle]

i did and the same error occurs

[Tom]

It might be easier to see the issue without the macro wrapper.  SAS will definitely be able to place the error message more closely to the statement that is causing issue.

But just reformatting the line from the SAS log is looks clear you have added TERADATA syntax into your SAS code.

create table EIWORK05.CMIHistKygjtest as 
select * from connection to eiwp
(select MAX(baseid) as maxhistky 
 FROM
  ( SELECT COALESCE(MAX(hist_ky), 0) AS baseid 
    FROM HISTORYgjtest 
    UNION all 
    SELECT COALESCE(MAX(hist_ky), 0) AS baseid 
    FROM History_gjtestwae 
  ) as foo 
) WITH DATA NO PRIMARY INDEX;

This part:

WITH DATA NO PRIMARY INDEX

is Teradata SQL and is not going be understood by the SAS CREATE TABLE statement.

[Gayle]

do you know what I can put instead of:
WITH DATA NO PRIMARY INDEX what is the equivalent

[Tom]

---

@Gayle wrote:
do you know what I can put instead of:
WITH DATA NO PRIMARY INDEX what is the equivalent

---

Just remove it. 

The WITH DATA part is quirky Teradata syntax, without it the CREATE TABLE statement makes the table based on the variables the query generates but does not actual write the query results into the new table. 

The NO PRIMARY INDEX is needed in Teradata because of how they partition the data into multi storage/compute nodes for parallelization.  Since this query only extracts one observation it is not even needed if you were making the table in Teradata.

[Gayle]

ok thank you will try that