SAS - How to know if previous PROC SQL modified a Database Table?

Question

With PROC SQL it's possible to connect to a Database (Db2 in my case) and execute Inserts, Deletes, etc.

If one such process causes no modifications to the target Table you will see a note like this in the Log:

NOTE: No data found/modified.

So it's clear that SAS checks for this after every such step.

Can I access this Information during the execution of a Program other than by parsing the Log on the fly? Perhaps some sort of automatic Macrovariable/Dataset that stores the Status of the last step?

EDIT: I'm using Pass Thru SQL with EXECUTE-Statements.

Are you using explicit pass thru code (EXECUTE statement in PROC SQL)? Or using pure SAS code and referencing the data via a libref? — Tom
@Tom I'm using Pass thru with EXECUTE statements. I'll edit my question to make that clearer. — Marco
What values do the SQLXRC and SQLXMSG automatic macro variables contain after your different types of SQL statements? Do those not provide what you want? If not is there some DB2 command that will tell you the results of the previous command? — Tom
@Tom Honestly I just didn't know about those variables. The good news is, yes, SQLXRC does the trick. It's equal to 100 when no data is found/modified and equal to 0 otherwise (unless, of course, there's a different error/warning). If you want you can post that as an answer I'll accept it. — Marco

Tom Tom · Accepted Answer · 2020-07-20T02:58:22

Check the automatic macro variables PROC SQL creates after remote execution.

SQLXMSG contains descriptive information and the DBMS-specific return code for the error that is returned by the pass-through facility.

Note: Because the value of the SQLXMSG macro variable can contain special characters (such as &, %, /, *, and ;), use the %SUPERQ macro function when printing the following value: %put %superq(sqlxmsg); For information about the %SUPERQ function, see SAS Macro Language: Reference.

SQLXRC contains the DBMS-specific return code that is returned by the pass-through facility.

SAS - How to know if previous PROC SQL modified a Database Table?

1 Answers