Architecting, installing and maintaining your SAS environment

SAS/Connect, libname engine, invisible tables

Accepted Solution Solved
Reply
Respected Advisor
Posts: 3,124
Accepted Solution

SAS/Connect, libname engine, invisible tables

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


Accepted Solutions
Solution
‎11-08-2012 05:02 AM
Contributor
Posts: 27

Re: SAS/Connect, libname engine, invisible tables

Hi Haikuo,

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

Regards,

Sándor

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: SAS/Connect, libname engine, invisible tables

Bump before involving SAS tech support.

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

Thanks,

Haikuo

Frequent Contributor
Posts: 129

Re: SAS/Connect, libname engine, invisible tables

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

Respected Advisor
Posts: 3,124

Re: SAS/Connect, libname engine, invisible tables

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

Frequent Contributor
Posts: 129

Re: SAS/Connect, libname engine, invisible tables

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

Respected Advisor
Posts: 3,124

Re: SAS/Connect, libname engine, invisible tables

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

Solution
‎11-08-2012 05:02 AM
Contributor
Posts: 27

Re: SAS/Connect, libname engine, invisible tables

Hi Haikuo,

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

Regards,

Sándor

Respected Advisor
Posts: 3,124

Re: SAS/Connect, libname engine, invisible tables

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

Respected Advisor
Posts: 3,124

Re: SAS/Connect, libname engine, invisible tables

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

Contributor
Posts: 27

Re: SAS/Connect, libname engine, invisible tables

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

Respected Advisor
Posts: 3,124

Re: SAS/Connect, libname engine, invisible tables

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

Frequent Contributor
Posts: 86

Re: SAS/Connect, libname engine, invisible tables

Hi Hai.Kuo/

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

Occasional Contributor
Posts: 5

Re: SAS/Connect, libname engine, invisible tables

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

 

The value for SCHEMA should be uppercase.

Occasional Contributor
Posts: 5

Re: SAS/Connect, libname engine, invisible tables

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.

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 4476 views
  • 7 likes
  • 5 in conversation