BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Haikuo
Onyx | Level 15

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

1 ACCEPTED SOLUTION

Accepted Solutions
SandorSzalma
Fluorite | Level 6

Hi Haikuo,

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

Regards,

Sándor

View solution in original post

13 REPLIES 13
Haikuo
Onyx | Level 15

Bump before involving SAS tech support.

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

Thanks,

Haikuo

LarryWorley
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6

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
Onyx | Level 15

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
Fluorite | Level 6

Hi Haikuo,

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

Regards,

Sándor

Haikuo
Onyx | Level 15

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
Onyx | Level 15

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
Fluorite | Level 6

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
Onyx | Level 15

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
Calcite | Level 5

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

shenghuani
Fluorite | Level 6

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

 

The value for SCHEMA should be uppercase.

shenghuani
Fluorite | Level 6

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.

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 11636 views
  • 9 likes
  • 5 in conversation