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...
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
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.