cryptic error message from fedsql.execDirect

acordes — Mon, 17 Feb 2025 17:48:46 GMT

If I run the code like in an isolated manner, it works.

%let outcas=casuser;
%let plant=XYZ;
%let installation1=OP10;

proc cas;
source ETL_AC;
		create table public.X_&plant1._ICS_&installation1._PROC_TR2{options replace=true} as 
			select a.*, b.Mean as mean_recipe, b.Std as std_recipe,
						c.Mean as mean_batch_id, c.Std as std_batch_id,
						d._NDis_ as _NDis_batch_id,
						e._NDis_ as _NDis_recipe		
               from &incas..X_&plant1._ICS_&installation1._PROC_TR a 
					left join &outcas..SUMMARY_A1 b 
						on a.RECIPE=b.RECIPE and a._NAME_=b.column
					left join &outcas..SUMMARY_A2 c 
						on a.batch_id=c.batch_id and a._NAME_=c.column
					left join &outcas..NDIST_A2 d 
						on a.batch_id=d.batch_id and a._NAME_=d._column_
					left join &outcas..NDIST_A1 e 
						on a.recipe=e.recipe and a._NAME_=e._column_    ;
endsource;
fedSQL.execDirect / query=ETL_AC;
quit;

But the same code inside my flow throws out an error message which I don't understand at all.

ERROR: Syntax error at or near ".S"

262  +
263  +proc cas;
264  +source ETL_AC;
265  +create
SYMBOLGEN:  Macro variable PLANT1 resolves to XYZ
SYMBOLGEN:  Macro variable INSTALLATION1 resolves to OP10
265 !+         table public.X_&plant1._ICS_&installation1._PROC_TR{options replace=true} as
266  +select a.*, b.Mean as mean_recipe, b.Std as std_recipe,
267  +c.Mean as mean_batch_id, c.Std as std_batch_id,
268  +d._NDis_ as _NDis_batch_id,
269  +e._NDis_ as _NDis_recipe
SYMBOLGEN:  Macro variable INCAS resolves to public
SYMBOLGEN:  Macro variable PLANT1 resolves to XYZ
SYMBOLGEN:  Macro variable INSTALLATION1 resolves to OP10
270  +               from &incas..X_&plant1._ICS_&installation1._PROC_TR a
271  +left
SYMBOLGEN:  Macro variable OUTCAS resolves to CASUSER
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
271 !+          join &outcas..SUMMARY_A1 b
272  +on a.RECIPE=b.RECIPE and a._NAME_=b.column
273  +left
SYMBOLGEN:  Macro variable OUTCAS resolves to CASUSER
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
273 !+          join &outcas..SUMMARY_A2 c
274  +on a.batch_id=c.batch_id and a._NAME_=c.column
275  +left
SYMBOLGEN:  Macro variable OUTCAS resolves to CASUSER
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
275 !+          join &outcas..NDIST_A2 d
276  +on a.batch_id=d.batch_id and a._NAME_=d._column_
277  +left
SYMBOLGEN:  Macro variable OUTCAS resolves to CASUSER
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
277 !+          join &outcas..NDIST_A1 e
278  +on a.recipe=e.recipe and a._NAME_=e._column_    ;
279  +endsource;
280  +fedSQL.execDirect / query=ETL_AC;
281  +quit;
NOTE: Active Session now MYSESSION.
ERROR: Syntax error at or near ".S"
ERROR: The action stopped due to errors.
NOTE: PROCEDURE CAS used (Total process time):
      real time           0.05 seconds
      cpu time            0.01 seconds

Re: cryptic error message from fedsql.execDirect

Tom — Mon, 17 Feb 2025 18:03:39 GMT

Possibly related the message about macro quoting? Macro quoting can confuse the SAS syntax parser. Try removing the macro quoting.

Perhaps add this statement before your PROC CAS statement.

%let outcas=%unquote(&outcas);

Re: cryptic error message from fedsql.execDirect

acordes — Mon, 17 Feb 2025 18:34:15 GMT

That fixes the problem Tom 🙂

topic cryptic error message from fedsql.execDirect in SAS Programming

cryptic error message from fedsql.execDirect

Re: cryptic error message from fedsql.execDirect

Re: cryptic error message from fedsql.execDirect