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';
Your macro variables need to be changed like this to resolve correctly to test1 and control1:
&&test&i.
,&&control&i.
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.
Where is the rest of the definition of that macro?
Where is the code that actually called that macro?
%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?
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
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;
Thank you ! I was wondering why this was printing despite entries.
Your macro variables need to be changed like this to resolve correctly to test1 and control1:
&&test&i.
,&&control&i.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.