topic Re: ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the e in SAS Programming

ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the end o

Gayle — Mon, 20 Jun 2022 19:32:36 GMT

I am gettting the error below but cannot figure out why.  Please look and advise the code is below.

ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the end of the request.



%put &=controlcellobs.;
%macro cntlcellentrytest;
        %put &=controlcellobs.;
        %if &controlcellobs > 0 %then
                %do;
                        %put "here in do";
                 proc sql;
                 connect to teradata as eiwp (username=&a. password=&b. tdpid=&EIWP_TDPID. mode=teradata fastload=yes);
                         select test, control into :test1 -, :control1 -  from work.controlcell;
                        %put _global_;
                        %do i=1 %to sqlobs;
                           execute(INSERT INTO work_05.CHD_TEST_X_CONTROL_test
                           (cell_ky, comparison_ky, as_of_dt, insrt_dt_tm)
                           SELECT
                           &test.&i.,
                           &control.&i.,
                           as_of_dt,
                           CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0))
                           FROM
                          (SELECT AS_OF_DT
                           FROM CORE_V2.WAE_CALENDAR
                           WHERE MO_AGO = 0) etl)   by eiwp;
                        quit;
                        %end;
                
                %end;
        %else;
                %put 'No entries in control cells';

Re: ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the e

SASKiwi — Mon, 20 Jun 2022 21:50:52 GMT

Rerun your program after adding - options mprint; - at the top. Then we can see the actual statements being executed. Not a SAS problem but a Teradata syntax problem.

Re: ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the e

Tom — Mon, 20 Jun 2022 21:57:45 GMT

Where is the rest of the definition of that macro?
Where is the code that actually called that macro?

Try aligning the indentation so it is a little easier to see what the code is doing.
Try using a smaller tab size also so that the lines do not get so far shifted to the right that they are hard for humans to scan and align.
It also helps to indent the actual SAS code the macro is generating independently of the MACRO code that is used to generate it.

%macro cntlcellentrytest;
%put &=controlcellobs.;
%if &controlcellobs > 0 %then %do;
  %put "here in do";
proc sql;
connect to teradata as eiwp (username=&a. password=&b. tdpid=&EIWP_TDPID. mode=teradata fastload=yes);
select test
     , control 
  into :test1 -
     , :control1 -  
  from work.controlcell
;

  %do i=1 %to sqlobs;
execute
 (INSERT INTO work_05.CHD_TEST_X_CONTROL_test(cell_ky, comparison_ky, as_of_dt, insrt_dt_tm)
  SELECT
     &test.&i.
    ,&control.&i.
    ,as_of_dt
    ,CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0))
  FROM
      (SELECT AS_OF_DT
        FROM CORE_V2.WAE_CALENDAR
        WHERE MO_AGO = 0
      ) etl
 )   
by eiwp;
quit;
  %end;
%end;
%else;
%put 'No entries in control cells';
/* What goes here? */
%mend cntlcellentrytest;

%let controlcellobs=what value should this have?
%put &=controlcellobs.;
%cntlcellentrytest;

Now it is much easier to see that the QUIT statement that ends the PROC SQL step is INSIDE the %DO loop. So if SQLOBS is larger than 1 you will have problems with the SAS code you generated.

Also why is the last %PUT statement being run unconditionally? Should that be inside of a %ELSE block?

Do you really have a TERADATA schema named WORK_05?

Re: ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the e

Gayle — Mon, 20 Jun 2022 22:56:12 GMT

Thanks i changed the quit to be outside of the loop. . The %PUT at the else just informs of a an empty controlcell dataset. I indicate below where the macro is invoked. when i run this it is not resolving the test and control macro variables. How do I get those to resolve?

error messages:

WARNING: Apparent symbolic reference TEST not resolved.
SYMBOLGEN:  Macro variable I resolves to 18
WARNING: Apparent symbolic reference CONTROL not resolved.
SYMBOLGEN:  Macro variable I resolves to 18
MPRINT(CNTLCELLENTRYTEST):   execute(INSERT INTO p_wae_u_work_05.CHD_TEST_X_CONTROL_test (cell_ky, comparison_ky, as_of_dt,
insrt_dt_tm) SELECT &test.18, &control.18, as_of_dt, CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0)) FROM (SELECT AS_OF_DT FROM
CORE_V2.WAE_CALENDAR WHERE MO_AGO = 0) dtl) by eiwp;

code:

%macro cntlcellentrytest;
        %put &=controlcellobs.;
        %if &controlcellobs > 0 %then
                %do;
                        %put "here in do";
                 proc sql;
                  select test, control into :test1 -, :control1 -  from work.controlcell;
                 connect to teradata as eiwp (username=&a. password=&b. tdpid=&EIWP_TDPID. mode=teradata fastload=yes);
                        * select test, control into :test1 -, :control1 -  from work.controlcell;
                        %put _global_;
                        %do i=1 %to &controlcellobs;
                           execute(INSERT INTO p_wae_u_work_05.CHD_TEST_X_CONTROL_test
                           (cell_ky, comparison_ky, as_of_dt, insrt_dt_tm)
                           SELECT
                           &test.&i,
                           &control.&i,
                           as_of_dt,
                           CAST(CURRENT_TIMESTAMP(0) AS TIMESTAMP(0))
                           FROM
                          (SELECT AS_OF_DT
                           FROM WAE_D_CORE_V2.WAE_CALENDAR
                           WHERE MO_AGO = 0) etl)   by eiwp;
                        %end;
                        quit;
                %end;
        %else;
                %put 'No entries in control cells';

%mend cntlcellentrytest;

%cntlcellentrytest;   ****here is where the macro is invoked

Re: ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the e

SASKiwi — Mon, 20 Jun 2022 23:16:50 GMT

Your macro variables need to be changed like this to resolve correctly to test1 and control1:

     &&test&i.
    ,&&control&i.

Re: ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the e

Gayle — Mon, 20 Jun 2022 23:25:57 GMT

Thank you! that resolved the variables

Re: ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the e

Tom — Mon, 20 Jun 2022 23:46:57 GMT

Why are you still printing the no entries message even when there are entries? Why did you indent the %PUT statement at a different level than the %ELSE and the %IF statement? Just indenting the code does mean that it only runs when the %ELSE clause runs. To do that you need to actually include it in the %ELSE clause.

 %else %put NOTE: No entries in control cells.;

Or use a %DO /%END block.

%else %do;
    %put NOTE: No entries in control cells.;
%end;

Re: ERROR: Teradata execute: Syntax error: expected something between the 'SELECT' keyword and the e

Gayle — Tue, 21 Jun 2022 03:00:45 GMT

Thank you ! I was wondering why this was printing despite entries.