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