Preserve Comment within Macro

[Patrick]

Hi all

I'm trying to run code in-database (Oracle) using a Hint. The code as such works BUT: The macro compiler removes the Hint as it looks like a comment (at least MPRINT doesn't print the Hint anymore).

Any suggestions what I would need to change in below code example to get the Hint passed to Oracle?

%macro doit();

  proc sql &cba_iem_sql_opt;

    connect to oracle as con1

    (&DBConnOpts PRESERVE_COMMENTS);

    execute

      (

        insert /*+ append */ into target_table

          select

            Col_A,

            Col_B

          from source_table

      )

      by con1;

    disconnect from con1;

  quit;

%mend;

%doit();

Thanks,

Patrick

[RW9]

Hi,

As far as I am aware there isn't a way to pass hints to OC.  They are not actually part of the SQL at all.

I would also question why you are uploading data directly to a DB.  My suggestion would be to create a table in a work area via passthrough.  Then have a DB function which takes this data and processes it into the database, i.e. does pre-checks, updates audit trails etc.

[Patrick]

As far as I am aware there isn't a way to pass hints to OC

It's pass-through SQL and the Oracle Hint is valid syntax. Option "PRESERVE_COMMENTS" has specifically been introduced to allow passing of hints to a data base (by not removing the Hint as a comment).

I would also question why you are uploading data directly to a DB.

I am not uploading anything. Both source and target are in Oracle (different schemas) and I want to have all of the processing in the data base. The target table is my staging table and I'm truncating it before loading any new data into it.

To ask the question I had to simplify the code. Please let's focus on my question which I believe is about the macro compiler removing the Hint as it "believes" it's a SAS comment.

[Tom]

Did you try using %STR() to mask the /* */ so that the macro doesn't "eat" them?

%macro xx ;

  %Str(/)*+ Oracle hint *%str(/)

%mend xx;

data _null_;

  put "%xx";

run;

[Patrick]

No, I didn't, but it looks like a good idea. The only thing I could think of so far is storing the whole hint in a macro variable and then use this macro variable - but I kind-of don't like the approach.

I'll give your suggestion a go once I'm on site again and will post the outcome here.

[Tom]

Try something like this:

%macro hint(str) ;

  %nrstr(/)*+ &str *%nrstr(/)

%mend hint;

...

execute

(insert %hint(append) into target_table

  select Col_A

       , Col_B

  from source_table

) by con1;

[Patrick · Accepted solution]

With @Tom's guidance and a bit more digging I was now able to make it work.

There were two issues in the code:

1. The place I had the "by con1":  SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

2. The macro compiler "eating" the hint: 7290 - Oracle hints are not passed when coded in a SAS macro

Below a code sample which would pass the hint to Oracle.

%macro doit();

  proc sql &cba_iem_sql_opt;

    connect to oracle as con1

    (&DBConnOpts PRESERVE_COMMENTS);

    execute by con1

      (

        insert %str(/)%str(*)+ append %str(*)%str(/) into target_table

          select

            Col_A,

            Col_B

          from source_table

      )

     ;

    disconnect from con1;

  quit;

%mend;

%doit();