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

[Gayle]

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';

[SASKiwi]

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. 

[Tom]

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?

[Gayle]

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

[Tom]

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;

[Gayle]

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

[SASKiwi · Accepted solution]

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

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

[Gayle]

Thank you! that resolved the variables