topic Re: SAS/Connect, libname engine, invisible tables in Administration and Deployment

SAS/Connect, libname engine, invisible tables

Haikuo — Thu, 27 Sep 2012 13:14:31 GMT

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;

Re: SAS/Connect, libname engine, invisible tables

Haikuo — Fri, 28 Sep 2012 14:51:04 GMT

Bump before involving SAS tech support.

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

Thanks,

Haikuo

Re: SAS/Connect, libname engine, invisible tables

LarryWorley — Fri, 28 Sep 2012 16:24:21 GMT

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

Re: SAS/Connect, libname engine, invisible tables

Haikuo — Fri, 28 Sep 2012 16:32:16 GMT

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

Re: SAS/Connect, libname engine, invisible tables

LarryWorley — Fri, 28 Sep 2012 16:41:21 GMT

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

Re: SAS/Connect, libname engine, invisible tables

Haikuo — Fri, 28 Sep 2012 17:17:08 GMT

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

Re: SAS/Connect, libname engine, invisible tables

SandorSzalma — Thu, 08 Nov 2012 10:02:21 GMT

Hi Haikuo,

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

Regards,

Sándor

Re: SAS/Connect, libname engine, invisible tables

Haikuo — Thu, 08 Nov 2012 20:03:27 GMT

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

Re: SAS/Connect, libname engine, invisible tables

Haikuo — Fri, 09 Nov 2012 17:17:35 GMT

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

Re: SAS/Connect, libname engine, invisible tables

SandorSzalma — Mon, 12 Nov 2012 07:07:35 GMT

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

Re: SAS/Connect, libname engine, invisible tables

Haikuo — Mon, 12 Nov 2012 13:43:06 GMT

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

Re: SAS/Connect, libname engine, invisible tables

bnarang — Thu, 03 Apr 2014 12:26:52 GMT

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

Re: SAS/Connect, libname engine, invisible tables

shenghuani — Fri, 23 Jun 2017 17:55:08 GMT

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

The value for SCHEMA should be uppercase.

Re: SAS/Connect, libname engine, invisible tables

shenghuani — Fri, 23 Jun 2017 18:03:44 GMT

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.