3 weeks ago
I've written a code generation data step which generates code to create a view in SQL Server. It uses metadata stored in Excel for column names, types, etc. The view contains hundreds of columns.
I won't bother posting the view generation code; suffice it to say in creates syntactically correct code for SQL Server.
When I try to execute the code:
filename code temp; *** data _null_ step to generate code within the code temporary file ; proc sql noprint; connect using pdcdev; execute ( %include code ) by pdcdev; quit;
I get this error:
ODBC_603: Executed: on connection 9 %include code ERROR: CLI execute error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '%'.
I thought the macro processor resolved all macro references within explicit pass-through...including %include???
The workaround is easy enough, just move the explicit pass-through code to within the code generation step.
But I'm wanting to get a better understanding of the interaction of explicit pass-through and %include.
3 weeks ago - last edited 2 weeks ago
No. The %INCLUDE needs to be at a statement boundary and for SQL passthru the SAS statement that is being compiled/run is the SELECT or EXECUTE statement, not the SQL statement that is being sent as passthru code.
Now you could put the BEGINNING of the SAS SQL statement in another file and include them both. You could also put the ending in a third file, but that is not needed.
Here is trivial example.
filename part1 temp; filename part2 temp; data _null_; file part1 ; put 'select * from connection to td ('; file part2 ; put 'select 5 as x' ; stop; run; proc sql ; %tdconnect; %include part1 part2 / source2 ; ); quit;
Which retrieves the one record query result and has SAS log that looks like this.
120 %include part1 part2 / source2 ; NOTE: %INCLUDE (level 1) file PART1 is file /scratch/SAS_workE856002B6767/#LN00041. 121 +select * from connection to td ( NOTE: %INCLUDE (level 1) ending. NOTE: %INCLUDE (level 1) file PART2 is file /scratch/SAS_workE856002B6767/#LN00042. 122 +select 5 as x NOTE: %INCLUDE (level 1) ending. 123 ); TERADATA_0: Prepared: on connection 0 select 5 as x TERADATA_1: Executed: on connection 0 select 5 as x TERADATA: trget - rows to fetch: 1 Summary Statistics for TERADATA are: Total row fetch seconds were: 0.000003 Total SQL execution seconds were: 0.002740 Total SQL prepare seconds were: 0.002438 Total seconds used by the TERADATA ACCESS engine were 0.088820