topic Re: Preserve Comment within Macro in SAS Data Management

Preserve Comment within Macro

Patrick — Mon, 22 Jun 2015 10:13:39 GMT

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

Re: Preserve Comment within Macro

RW9 — Mon, 22 Jun 2015 10:42:46 GMT

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.

Re: Preserve Comment within Macro

Patrick — Mon, 22 Jun 2015 11:17:50 GMT

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.

Re: Preserve Comment within Macro

Tom — Mon, 22 Jun 2015 13:36:18 GMT

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;

Re: Preserve Comment within Macro

Patrick — Mon, 22 Jun 2015 14:16:23 GMT

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.

Re: Preserve Comment within Macro

Tom — Mon, 22 Jun 2015 14:57:27 GMT

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;

Re: Preserve Comment within Macro

Patrick — Tue, 23 Jun 2015 06:38:28 GMT

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