Can I %include code within PROC SQL explicit pass-through?

[ScottBass]

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.

Thanks...

[Tom · Accepted solution]

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