SAS/Connect, libname engine, invisible tables

[Haikuo]

Dear Friends and Colleagues,

I am new to SAS/Connect, but I don’t think it is overwhelmingly complex after playing with it for 3 days. However, I stumped upon the following wired scenario.

Here is some background.

PC SAS: 9.3, Server SAS: 9.2

What I was doing is to fire up SAS server from my PC SAS using SAS/Connect, and then through SAS server to access some Oracle data via ODBC engine. I knew for a fact there are bunch of Oracle tables under certain Schema, and I want to have a directory view of them (hence pass-through is not suitable for this purpose), So here goes the first step (after remote SAS serve has been signon):

426  rsubmit;

NOTE: Remote submit to SRVRREM commencing.

1    libname test odbc

NOTE: Libref TEST was successfully assigned as follows:

      Engine:        ODBC

      Physical Name: xxxx

2            dsn='xxxx'

3            uid='xxxxx'

4            pw=XXXXXXXXXX

5            schema=xxxdata;

6    /*      preserve_tab_names=yes*/

7    /*      preserve_col_names=yes;*/

NOTE: Remote submit to SRVRREM complete.

Everything seems normal until......

427  rsubmit;

NOTE: Remote submit to SRVRREM commencing.

8    proc datasets lib=test;

                                  Directory

                            Libref         TEST

                            Engine         ODBC

                            Physical Name  edwprod

                            Schema/Owner   pcrdata

WARNING: No matching members in directory.

9    contents directory data=_all_;

10   quit;

WARNING: No matching members in directory.

NOTE: The PROCEDURE DATASETS printed page 1.

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           3.46 seconds

      cpu time            0.04 seconds

NOTE: Remote submit to SRVRREM complete.

There is nothing in there. This also has been confirmed using SAS/connect remote library service, SAS explorer can only find a empty library. Now wired thing happened.....

428  rsubmit;

NOTE: Remote submit to SRVRREM commencing.

11   proc sql inobs=10;

12      create table tst as

13         select * from test.dep_ar  /*known existing table under this schema*/

14   ;

WARNING: Only 10 records were read from TEST.DEP_AR due to INOBS= option.

NOTE: Table WORK.TST created, with 10 rows and 45 columns.

14 !  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.09 seconds

      cpu time            0.00 seconds

NOTE: Remote submit to SRVRREM complete.

Proc SQL can pull data from a phantom table in that library.

429  rsubmit;

NOTE: Remote submit to SRVRREM commencing.

15   data tst1;

16     set test.dep_ar (obs=10); /*known existing table in this schema*/

17    run;

NOTE: There were 10 observations read from the data set TEST.DEP_AR.

NOTE: The data set WORK.TST1 has 10 observations and 45 variables.

NOTE: DATA statement used (Total process time):

      real time           0.04 seconds

      cpu time            0.01 seconds

NOTE: Remote submit to SRVRREM complete.

So does data step! What is going on? Is it SAS/Connect that some of its starting-up default scripts needs tweak, or is it this generic ODBC engine? I am totally lost.  Please help!

Haikuo

[Haikuo]

Bump before involving SAS tech support.

I got one feedback from SAS-L, while the mystery along with my trouble remains.

Thanks,

Haikuo

[LarryWorley]

Hi Hai Kuo,

Perhaps you have a situation where the Oracle objects in the schema you use are all synonyms.

I have seen the same behavior when using Access to Oracle.  In that situation, the libname option, synonyms=yes, would allow SAS to see synonyms as 'tables' in proc contents and in libname windows.

Using Access to ODBC, I do not see a similar option.

Larry

[Haikuo]

Thanks, Larry. I got a similar response from Friedegg @ SAS-L. Yes, you are right, ACCESS to ODBC is very limited comparing to ACCESS to Oracle that I used to have.

Haikuo

[LarryWorley]

Haikuo,

Have you looked at configuration options (in ODBC, not SAS) for the ODBC DSN?  Perhaps there is an Oracle specific option there which does the same thing as the show_synonyms option.

Also depending upon how you need to access the list of 'tables', you could use the sys schema within oracle to get a list of synonyms for your Oracle userid. 

Larry

[Haikuo]

Hi Larry,

Good idea. I am looking into DSN definition scripts and hopefully can find something to add in to show synonyms.

I am not the owner of any schemas on that server, sys comes back empty handed.

Thanks,

Haikuo

[SandorSzalma · Accepted solution]

Hi Haikuo,

would you give a try with schemaname (schema=XXXDATA) written in uppercase?

Regards,

Sándor

[Haikuo]

Hi Sandor,

Although I think the naming convention of both Oracle and SAS ignore cases, I will give a shot after my Oracle server back online. Thank you!

Haikuo

[Haikuo]

Hi Sandor,

You are just awesome! No, you are beyond awesome. After I CAPPed the schema name, everything go back to normal. I can see all the Oracle tables, and use them as SAS tables. Now, I think you owe us an explanation: Why?

Thank you so very much!

Haikuo

[SandorSzalma]

Hi Haikou,

Oracle stores this information in uppercase.

SAS/ACCESS for Oracle knows it and make the conversion.

SAS/ACCESS for ODBC does not know what database you try to connect and does not change the schema name.

Regards,

Sándor

[Haikuo]

Thanks, Sandor. I guess this is the trade-off of being cheap. In my organization, ODBC comes with BASE for free, while Oracle engine requires additional licensing.

Haikuo

[bnarang]

Hi Hai.Kuo/SandorSzalma

I have a similar problem.

I read this post but did not know where to apply the schemas and all. Can you please help me out. I have detailed out the full syntax.

Many Thanks

[shenghuani]

libname indata odbc noprompt =  "server=***;DRIVER=NETEZZASQL;Trusted Connection=yes;DATABASE=***;UID=*; PWD=***" SCHEMA=***;

The value for SCHEMA should be uppercase.

[shenghuani]

By using the sample codes I provided, you can access Netezza using ODBC (which is free). You can save money not to buy SAS/ACCESS interface to Netezza, if you use SAS Office Analytics.