SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Preserve Comment within Macro

Accepted Solution Solved
Reply
Respected Advisor
Posts: 4,173
Accepted Solution

Preserve Comment within Macro

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


Accepted Solutions
Solution
‎06-23-2015 02:38 AM
Respected Advisor
Posts: 4,173

Re: Preserve Comment within Macro

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();

View solution in original post


All Replies
Super User
Super User
Posts: 7,955

Re: Preserve Comment within Macro

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.

Respected Advisor
Posts: 4,173

Re: Preserve Comment within Macro

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.

Super User
Super User
Posts: 7,046

Re: Preserve Comment within Macro

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;

Respected Advisor
Posts: 4,173

Re: Preserve Comment within Macro

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.

Super User
Super User
Posts: 7,046

Re: Preserve Comment within Macro

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;

Solution
‎06-23-2015 02:38 AM
Respected Advisor
Posts: 4,173

Re: Preserve Comment within Macro

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();

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1086 views
  • 3 likes
  • 3 in conversation