I use SAS to run Oracle queries with SAS/Access for Oracle. Oracle has things called "hints" which direct the Oracle optimizer to take a different path than what the optimizer would come up with by itself. Here's the problem: Oracle hints rely on /* and */ to identify them. Example:
select /* + FULL(MH) */ account_num, cycle_date, balance, etc etc
from myschema.mytable
where blah blah
My Oracle code is within a macro for repeatability.
How do I pass the /* + FULL(MH) */ to the Oracle code without it disappearing? I tried %str(/)%str(*) + FULL(MH) %str(*)%str(/) but that doesn't work...
I think this covers what you are looking for.
Nice feature but sadly it doesn't seem to work within a Proc SQL select statement.
Are you letting SAS generate the Oracle code?
libname mylib oracle ... schema=myschema preserve_comments;
proc sql;
select /* + FULL(MH) */ account_num, cycle_date, balance
from mylib.mytable
;
quit;
Or are you passing the code explicitly to Oracle?
libname mylib oracle .... preserve_comments ;
proc sql;
connect using mylib;
select * from connection to mylib
(
select /* + FULL(MH) */ account_num, cycle_date, balance
from myschema.mytable
)
;
quit;
If the code is in a macro then you need to add some macro quoting to prevent the macro processor from throwing away the comments.
(
select /%str(*) + FULL(MH) %str(*)/ account_num, cycle_date, balance
from myschema.mytable
)
Good questions.
I'm passing the query, including any Oracle hints, as a macro variable string. That way I can re-use the code with many different problem queries that users send my way.
***********************************************************;
* DECLARE THE QUERY HERE ;
************************************************************;
%let the_query=
select @%str(*) + FULL(MHR) %str(*)@@
%bquote('&query_name_revision') as query_name, spm_loan_key, asset_date, balance, port_type
from is2.mortgage_history
where asset_date >= '31dec2019'
;
(note: I changed the slashes above to @ to escape the Communities invalid HTML message when I hit Post).
… then:
******************************************************;
* Extract data from the ORACLE table (code SQL below) ;
******************************************************;
proc sql;
******************************************************;
* Establish ORACLE connectivity via SAS/Access-Oracle ;
******************************************************;
connect to oracle (path=&ORACLE_OID_STRING user=&ORACLE_USER password="&ORAPASS" preserve_comments);
******************************************************;
* Run the query ;
******************************************************;
create table &out as
select * from connection to oracle
(
&the_query
);
quit;
Here is the resolved macro in the log after it ran (it ignored the hints again)
create table OUTLIB.u01bri_IS2_hints_pl1394_data as select * from connection to oracle ( select 'hints_1394' as query_name, spm_loan_key,
asset_date, balance, port_type from is2.mortgage_history where asset_date >= '31dec2019' );
NOTE: Table OUTLIB.U01BRI_IS2_HINTS_PL1394_DATA created, with 19391212 rows and 5 columns.
I committed a typo in my code above. It was as I tried to replace the slashes with "@"s to escape the invalid HTML checker on this site. I added one too many of these "@"s. Should be:
select %str(*) + FULL(MHR) %str(*)/
OK, still trying to work through this... thought I'd try the actual slashes again but that didn't work. Do the @-to-slash in your head here : )
select @%str(*) + FULL(MHR) %str(*)@@
OK, weird... I'm pretty sure I put just one "@" there at the end. I'll try again, this time with "S" instead of forward-slash
select S%str(*) + FULL(MHR) %str(*)S
Does your program work correctly if you hard code your SQL with the hint included and not define it in a macro variable?
preserve_comments works and I've used it in the past.
May be first execute some test SQL where everything including the hints is hard-coded and make this work.
When passing in the syntax via a SAS macro variable where you've used %str() and other quoting functions to create the string then eventually use %unquote(&macvar).
@DougZ wrote:
I committed a typo in my code above. It was as I tried to replace the slashes with "@"s to escape the invalid HTML checker on this site. I added one too many of these "@"s. Should be:
select %str(*) + FULL(MHR) %str(*)/
How are you pasting in the text of your code that you are getting HTML errors?
Just click on the Insert Code or Insert SAS Code button and paste the text into the pop-up window.
First question is does it work without the macro (or the macro variables)? Just run the PROC SQL step directly.
proc sql;
connect to oracle (path=&ORACLE_OID_STRING user=&ORACLE_USER password="&ORAPASS" preserve_comments);
create table &out as
select * from connection to oracle
(select /* + FULL(MHR) */
'XXXX' as query_name, spm_loan_key, asset_date, balance, port_type
from is2.mortgage_history
where asset_date >= '31dec2019'
);
quit;
If that works then what happens when you put it in a file a %INCLUDE it?
If that works what happens if you wrap that code that has the %include into a macro definition and then execute the macro?
If that works then instead of macro this macro that just generate the code you want to run to a file and %include it.
Yes, it works without the macro. But when I try to put that query into a file a %INCLUDE it, Oracle rejects it - doesn't like "%include" as a word I think...
MPRINT(QUERY_TEST_TEMPLATE): proc sql noprint;
MPRINT(QUERY_TEST_TEMPLATE): connect to oracle (path=u01bri_pte user=n550513 password="{SAS002}878424311C65F34222798BEB2159842C" preserve_comments);
MPRINT(QUERY_TEST_TEMPLATE): execute ( explain plan for %include './test_query_w_hints.txt' ) by oracle;
ERROR: ORACLE execute error: ORA-00911: invalid character.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
@DougZ wrote:
Yes, it works without the macro. But when I try to put that query into a file a %INCLUDE it, Oracle rejects it - doesn't like "%include" as a word I think...
MPRINT(QUERY_TEST_TEMPLATE): proc sql noprint; MPRINT(QUERY_TEST_TEMPLATE): connect to oracle (path=u01bri_pte user=n550513 password="{SAS002}878424311C65F34222798BEB2159842C" preserve_comments); MPRINT(QUERY_TEST_TEMPLATE): execute ( explain plan for %include './test_query_w_hints.txt' ) by oracle; ERROR: ORACLE execute error: ORA-00911: invalid character. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
You need to make the include file have the full EXECUTE command. Not hard to do. Say you have a dataset name CODE with a variable name SQL that has the code to run in the remote database.
filename execute temp;
data _null_:
set code end=eof;
file execute;
if _n_=1 then put 'EXECUTE BY ORACLE (';
put sql ;
if eof then put ');' ;
run;
proc sql noprint;
connect to oracle (path=u01bri_pte user=n550513 password="{SAS002}878424311C65F34222798BEB2159842C" preserve_comments);
%include execute ;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: