topic Re: Help cannot figure out why i am getting: ERROR 22-322: Syntax error, expecting one of the follo in SAS Programming

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

Gayle — Wed, 12 May 2021 03:08:26 GMT

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;

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

japelin — Wed, 12 May 2021 04:25:10 GMT

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?

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

ChrisNZ — Wed, 12 May 2021 07:13:02 GMT

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.

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

Kurt_Bremser — Wed, 12 May 2021 11:15:00 GMT

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.

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

Tom — Wed, 12 May 2021 12:37:59 GMT

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.

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

Gayle — Wed, 12 May 2021 13:32:04 GMT

i did and the same error occurs

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

Gayle — Wed, 12 May 2021 14:51:39 GMT

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

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

Gayle — Wed, 12 May 2021 15:06:45 GMT

let me try to that thanks!

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

Tom — Wed, 12 May 2021 15:08:49 GMT

@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.

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

Gayle — Wed, 12 May 2021 15:28:28 GMT

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

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

Gayle — Wed, 12 May 2021 16:13:37 GMT

ok thank you will try that