What's the difference between sqlobs et sqlxobs?

[xxformat_com]

Any correction or precisions is welcome.

As far I can understand, SQLX... macro variables are macro variables associated with Pass-Through facilities of the SQL procedure.

So if the data of an Excel file is imported using the SQL Pass-Through facilities for Excel engine, one use the value of the SQLXRC and not SQLRC before disconnecting. The value of SQLXRC different from SQLRC e.g. if the Excel worksheet does not exist, SQLXRC=-543884569 wile SQLRC possible values are 0, 4, 8....28. But as far as I can see both SQLOBS and SQLXOBS work and (fairly logically) returns the same value. So I assume that's the reason why I can only find documentation about SQLOBS, not SQLXOBS.

Which of the two macro variables SQLOBS/SQLXOBS is recommended by SAS?

Where are specified the rules to interpret SQLXRC?

[jimbarbour]

I believe there can be a difference between SQLOBS and SQLXOBS.  SQLXOBS is the number of rows returned by the external database.  SQLOBS is the number of rows returned by SAS.  They can differ if an OUTOBS parameter or other limiting option/statement is used.  Let me confirm this.

Jim

[jimbarbour]

Well, bizarrely, both my SQLOBS and SQLXOBS are returning zeroes even though I have rows.  I assume that this is due to the particular ODBC driver that I'm using.  If I get time, I'll try it on another database.

However if you do a query something like this, you should be able to see if SQLOBS and SQLXOBS are different on your system:

PROC SQL OUTOBS=4;
  CONNECT ...

  CREATE TABLE ... AS
    SELECT * FROM CONNECTION TO ..
        SELECT X, Y, Z FROM ...

DISCONNECT...
QUIT;

Jim

[xxformat_com]

Hi,

Actually I have already tried it with connect to excel. Both values are the same and not equal to zero and correct for as long as you keep within the connection and the WHERE dataset option is not used on the output dataset. But you're right OUTOBS= has an effect on SQLOBS but not on SQLXOBS. The value has to be stored in another macro variable to be accessible outside of the connection. But it is not because it is valid for one example, that it is the rule and no specific scenarios exists.

That's why I was looking for SAS Online Documentation references.

[jimbarbour · Accepted solution]

I was looking for SAS Online Documentation references

@xxformat_com,

I think there's the issue right there.  SQLXOBS is an undocumented feature of SAS.  I just looked in "Carpenter's Complete Guide to the SAS Macro Language, Third Edition," and on page 105, under a sub-heading "SQLXOBS,"  it says the following:  "Undocumented, but seems to be associated with the number of observations associated with the pass-through (it is created even if there is no pass-through)"

So, in answer to your original two questions:

---

Which of the two macro variables SQLOBS/SQLXOBS is recommended by SAS?

Where are specified the rules to interpret SQLXRC?

---

SQLOBS is clearly the one to use since SQLXOBS is undocumented and not officially part of SAS. SQLXOBS is "use at your own risk."

I'm not sure there are any specified SAS rules to intperpret SQLXRC (note the emphasis on "SAS") since it is something being passed back from the DBMS.  I typically look up the meaning in the documentation for the database not in any SAS language documentation.  For example, if I'm working with Oracle and want to know the meaning of a particular value of SQLXRC, I go look at the Oracle documentation.

Jim

[Tom]

Did you check if it is in the documentation for SAS/Access to .....? 

Things like this are documented in the documentation for the product where they live, not in the base documentation.

[Tom]

Make sure to test the macro variables before running any other statement. Like the DISCONNECT statement your code has.

[Tom]

If you don't select from the remote database then SQLXOBS will be zero.

Example:

168  create table x as
169    select * from connection to oracle (select 1 as x from dual)
170  ;
NOTE: Table WORK.X created, with 1 rows and 1 columns.

171  %put &=sqlobs &=sqlxobs;
SQLOBS=1 SQLXOBS=1
172  create table y as select * from x;
NOTE: Table WORK.Y created, with 1 rows and 1 columns.

173  %put &=sqlobs &=sqlxobs;
SQLOBS=1 SQLXOBS=0