%include inside Oracle Connection

[david27]

Hello,

I have this:

/*This works*/
proc sql;    
  connect to oracle (user=&usr
password=&psswd
path=&path
buffsize=5000); 

create table temp as
select * from connection to Oracle(
select count(*)
from schema.table
);
disconnect from oracle;
quit;

But if I add a %include and have the query inside that, then it does NOT work. Why?

/*This works*/
proc sql;    
  connect to oracle (user=&usr
password=&psswd
path=&path
buffsize=5000); 

create table temp as
select * from connection to Oracle(
%include "/path/to/code/code.sas";
);
disconnect from oracle;
quit;

[PaigeMiller · Accepted solution]

Anything inside of the parentheses in

from connection to Oracle( )

must be valid code that Oracle understands. I don't think Oracle understands %include.

[david27]

Thank You

[Tom]

---

@PaigeMiller wrote:

Anything inside of the parentheses in

from connection to Oracle( )

must be valid code that Oracle understands. I don't think Oracle understands %include.

---

It is not that ORACLE does not understand %INCLUDE.

The issue is that the SAS macro processor only recognizes %INCLUDE at a statement boundary.  You cannot use it after you have already begun the CREATE or SELECT statement.

[Reeza]

Anything between the parenthesis below (highlighted in red) are passed directly to the Oracle server. The Oracle server has no knowledge of SAS so SAS command are not valid in that section. You are using SQL pass through which takes the code, passes it to the Oracle server, Oracle executes the commands and returns the results based on the query. 

proc sql;    
  connect to oracle (user=&usr
password=&psswd
path=&path
buffsize=5000); 

create table temp as
select * from connection to Oracle(
%include "/path/to/code/code.sas";
);
disconnect from oracle;
quit;

[david27]

Thank You.

[Tom]

Try using a utility to read the file and emit the lines as code.

For example you could try MODE=3 option of this macro:  %fread() 

But don't call the file a SAS file if all it contains is SQL code.

create table temp as
select * from connection to Oracle(
%fread("/path/to/code/code.sql",mode=3)
);

Note: Do not expect SAS to honor line breaks in your SQL file.  So do not use those goofy -- end of the line comments.  If you want to have comments in your SQL code use real /* */ block comments.

[yabwon]

One other option would be to put it all in the include, like:

filename Head TEMP;
filename Body "/path/to/code/code.sas";
filename Tail TEMP;

data _null_;
file Head; input; put _infile_;
cards4;
proc sql;    
  connect to oracle (user=&usr
password=&psswd
path=&path
buffsize=5000); 
create table temp as
select * from connection to Oracle(
;;;;
run;

data _null_;
file Tail; input; put _infile_;
cards4;
);
disconnect from oracle;
quit;
;;;;
run;

%include Head Body Tail / source2;

Bart

[Tom]

That works as well.

Note you just need to have the beginning of the SAS statement in the PRE file because the %INCLUDE statement cannot be called in the middle of a SAS statement. There is no need for a POST file, you can just have that part of the statement in the program itself.

Here is an example you can try.  The is example is just using SASHELP dataset to demonstrate the idea.  Just update the prefix code to reference Oracle if you need.

options parmcards=code;
filename code temp;

parmcards;
select * from sashelp.class
;

options parmcards=pre;
filename pre temp;

parmcards;
create table xx as (
;

proc sql;
%include pre code / source2;
);
quit;

Result

347  proc sql;
348  %include pre code / source2;
NOTE: %INCLUDE (level 1) file PRE is file
      C:\Users\...\#LN00096.
349 +create table xx as (
NOTE: %INCLUDE (level 1) ending.
NOTE: %INCLUDE (level 1) file CODE is file
      C:\Users\...\#LN00095.
350 +select * from sashelp.class
NOTE: %INCLUDE (level 1) ending.
351  );
NOTE: Table WORK.XX created, with 19 rows and 5 columns.

352  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds