Use SQL hints in PROC SQL?

[SAS-Nutzer]

How can I use a SQL (Oracle) hint in Proc SQL? I try

proc sql;
  select /*+ parallel (16) */ xy...

but it doesn't seem to work. Is it possible to use hints in Proc SQL, or must I use an explicit pass-through for that?

[Tom · Accepted solution]

Search:  https://www.google.com/search?q=%40sas.com+oracle+hints

You need to tell SAS you want to pass comments to Oracle by including the preserve_comments option when connecting, otherwise *surprise* comments are treated as comments by SAS and stripped out of the code that runs.

https://support.sas.com/kb/19/205.html

For implicit passthru you can pass hints with the dataset option, ORHINTS=.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1uutrqh4za0sgn1c26jj7z1k6m9.htm

[SAS-Nutzer]

I had the strange problem that the preserve_comments didn't work, neither when specifying it in a Libname and then USING that Libname in explicit passthrough as described here (example 2):

LIBNAME xyz ORACLE ... PRESERVE_COMMENTS=YES;

proc sql;
connect using xyz
...
quit;

nor when using the preserve_comments in Pass-Through directly as described here :

proc sql;
      connect to oracle as mycon(user=testuser
                 password=testpass preserve_comments)

What DID work for me, however, was masking the comment in the explicit passthrough, this is what I ended up using: 

proc sql;
	connect using mycon;
	select * from connection to mycon
	(
	select %STR(/)%STR(*)+PARALLEL(16) %STR(*)%STR(/) xyz...
        );
	disconnect from mycon;
quit;

The masked comment is equal to /*+parallel(16) */

I took this format somewhat from here . This is the only way that worked for me.

[Tom]

Did you accidently wrap your SAS code into a macro?  The macro processor also considers /*...*/ as a comment to be ignored when generating the SAS code to run.

Note you don't have to protect every character. Just protect the / before the * to prevent the macro processor from seeing the start of a block comment.

Notice how the MPRINT lines from the %MYSQL does not show the "hint" but the from %MYSQL2 it does.

783   proc sql;
784   connect to oracle;
ERROR: ORACLE connection error: ORA-12560: TNS:protocol adapter error.
785   select * from connection to oracle
786   (select /*+ oracle junk */ from mytable)
787   ;
ERROR: ORACLE connection error: ORA-12560: TNS:protocol adapter error.
788   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


789   %macro mysql;
790   proc sql;
791   connect to oracle;
792   select * from connection to oracle
793   (select /*+ oracle junk */ from mytable)
794   ;
795   quit;
796   %mend;
797
798   options mprint;
799   %mysql;
MPRINT(MYSQL):   proc sql;
MPRINT(MYSQL):   connect to oracle;
ERROR: ORACLE connection error: ORA-12560: TNS:protocol adapter error.
MPRINT(MYSQL):   select * from connection to oracle (select from mytable) ;
ERROR: ORACLE connection error: ORA-12560: TNS:protocol adapter error.
MPRINT(MYSQL):   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds



800
801   %macro mysql2;
802   proc sql;
803   connect to oracle;
804   select * from connection to oracle
805   (select %str(/)*+ oracle junk */ from mytable)
806   ;
807   quit;
808   %mend;
809
810   options mprint;
811   %mysql2;
MPRINT(MYSQL2):   proc sql;
MPRINT(MYSQL2):   connect to oracle;
ERROR: ORACLE connection error: ORA-12560: TNS:protocol adapter error.
MPRINT(MYSQL2):   select * from connection to oracle (select /*+ oracle junk */ from mytable) ;
ERROR: ORACLE connection error: ORA-12560: TNS:protocol adapter error.
MPRINT(MYSQL2):   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

[doogie64]

Although many folks who respond about preserve_comments are looking at the code snippet given, in real world situations these SQL statements are often part of SAS macros.  To ensure the comments for parallelism get passed, you should try escaping them as follows and compare the run-times to see if it makes a difference, such as:

proc sql;

    connect to ....  ....readbuff=8000 preserver_comments=Y ..... ;

    create work.mytable(compress=yes) as select * from connection to Oracle (

       select %str(/)%str(*)+ PARALLEL(4) %str(*)%str(/)

                  field1, field2, field3, etc.

         from oracle_table_source

       where abc = blah

    );

   disconnect from Oracle;

   quit;

[Tom]

You don't need to protect both the / and *.  Just protect one so the result does not look like an actual block comment to the macro processor.

So any of these will work:

%str(/)*+ PARALLEL(4) *%str(/)
/%str(*)+ PARALLEL(4) %str(*)/
%str(/)*+ PARALLEL(4) %str(*)/
/%str(*)+ PARALLEL(4) *%str(/)