BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ScottBass
Rhodochrosite | Level 12

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...


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

 

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 2358 views
  • 3 likes
  • 2 in conversation