CODEWITHSUNDEEP
sqlmacrossasunion
SMW
SMW

Reputation: 502

SAS Macro RSUBMIT UNION query

I have been reworking a large job based on some information regarding UNION queries vs. IN/OR methods. I have been able to cut down my RSUBMIT queries quite well by moving IN statements into UNION queries, as the previous IN statement via one query took 35 minutes while the 8 union queries took 9 minutes.

As some of these IN statements involve more and more values, I attempted to use the macro facility in order to shorten the code, but I am having an issue.

The image below is a snippet of the macro (individual query), and you will see my issue right away.

enter image description here

*The macro is being compiled within the RSUBMIT, so we "should be good"...but the obvious red text is telling me that this is some type of illegal syntax based on the UNION statement.

I ran the section of the code just to see, and it does in fact, run...but ONLY for the FIRST query that is represented by the first parameter of the macro (in this case the 'IDRPR' string), then fails to execute all subsequent macro calls for subsequent parameters/queries.

I did attempt to wrap each "UNION" statement in a pair of parentheses in order to promote legal syntax. At first glance, it seems like it may run correctly since I get no red text in the enhanced editor -

enter image description here

But the same issue as above occurs...only one execution for the first macro call. What am I missing here, or how could this be executed in better, more efficient fashions?

Upvotes: 0

Views: 136

Answers (2)

Tom
Tom

Reputation: 51566

You can't start a SQL statement with the keyword UNION. Remove the extraneous semi-colons.

Upvotes: 1

Quentin
Quentin

Reputation: 6378

The syntax highlighter in SAS is a useful guide, but there are situations (especially with macro code) in which it is wrong. The best way to test if code is valid is to run it.

I don't know DB2, but I suspect one problem in your original code is the semicolons you have after each macro call.

Try coding %OPEN_IDR('IDR_PR') without the a semicolon after the macro call. The semicolons are probably breaking your DB2 query syntax.

Upvotes: 1

Related Questions