BookmarkSubscribeRSS Feed
bnarang
Calcite | Level 5

Hi Guys

Please help me on this matter.

I am connecting to Remote databases and then referencing it through local library assign but all the datasets are invisible. I can still run the sql query and extract the information but cannot view the tables at all

%let user1 = xxxxxxxx;

options remote = xxxx comamid = tcp;

filename rlink "...";

signon;

Rsubmit;

libname abc odbc dsn = "xxx" ;

endrsubmit;

libname abc server = user1;

Library abc is mapped but no dataset is visible but query can be made if we already know the table names.

Many Thanks in advance.

13 REPLIES 13
jwillis
Quartz | Level 8

You have permission to view the database but you do not have permission to view/read the tables in the database.  Have the database administrator grant you read access.


bnarang
Calcite | Level 5

I have the read access rights to the database and database tables. I know the tables and can query but cannot see the tables. thats the whole problem

jwillis
Quartz | Level 8

I understand your dilemna.  I had the same issue.  My issue had to be corrected by the database administrators.  In my case I was running PC SAS and accessing the databases through a remote SAS session running on UNIX.  The UNIX SAS session had read rights to the database and could bring the data out of the database.  I personally did not have read access to the database and I could not see the tables in the database from my PC.  I could not see the tables until my userid was given permission to read the database tables.

ArunAthithan
Calcite | Level 5

Hey jwillis,

 

I have a similar problem as well!

 

A user was able to define the library through a ODBC source to teradata successfully using PC SAS and also successfully query the tables present inside .

However she is not able to extract the list of datatsets under that Teradata schema using Proc datasets or by expanding the Library!!!

 

Teradata DBA has confirmed she had perfect READ acces to this schema.

What could be the potential issue here? Any idea?

 

SandorSzalma
Fluorite | Level 6

Hi bnarang,

you use ODBC connection defined on the SAS server.

Maybe you should have a look on it. (odbc.ini). Do you access an Oracle database?

If yes, ask your SAS-administrator to change the username to uppercase.

You can define schema with your libname statement, too:

libname abc odbc dsn = "xxx" schema = "YYY";

It is important to use uppercase schema-name with odbc connection for Oracle.

Hopefully it helps.


Regards,

Sándor

bnarang
Calcite | Level 5

Hi SandoSzalma

Ya, I am using Oracle database only. I have tried second option by putting Schema is libname statement but that did not work

Rsubmit;

libname abc odbc dsn = "xxx" schema = "YYYY";

endrsubmit;

libname abc server = user ;

Barnipaz
Obsidian | Level 7

libname abc slibref=abc server = user ;

You need the slibref statment 😉

bnarang
Calcite | Level 5

Yeah. sorry that is there. Having that also did not help

optimist
Calcite | Level 5

Maybe I'm missing something here. And certainly a more complete code example would help....

You have two library assignments, one within a RSUBMIT and one local.   You say that you can successfully execute a query if you know the table. Is that within the remote submit or locally? The reason I ask is that I wonder why you don't have the ODBC option in the local libname, i.e. why you aren't using the ODBC engine locally, since you are using it remotely.

Like I said, maybe I'm missing something.

jakarman
Barite | Level 11

mostly I code the local libref  as Barnipaz indicated. The slibref being sure for the remote lib connection.

---->-- ja karman --<-----
BobFitz
Calcite | Level 5

Hi bnarang,

I'm not using ODBC but I am using a local Windows version of SAS to connect to UNIX SAS, Oracle and SAS libraries on the UNIX box.

Within the rsubmit / endrsubmit statements, I define the rfdata library and the userid and encrypted macros for the Oracle DB. These are run on the Unix box.

As  mentioned before, for the work library on the remote box, I'm using slibref.  I define rfdata on the windows box and use the remote engine to point to it.

These statement allow me to view the datasets using SAS Explorer in Windows SAS.  I also do the same for the Oracle connection.

I'm not familiar with ODBC, its been about 15 years since I used it, but this should point you in the right direction.

Regards,

Bob   

rsubmit rmtbox;
  %let userid=rfitz;
  %let pw=%str({SASENC}xxxxxxxxxxxxxxxxxxxxxx);
  libname rfdata '/net/rfdata' ;
endrsubmit;

libname work_r remote slibref=WORK server=rmtbox;
libname rfdata remote '/net/rfdata' server =rmtbox;

rsubmit rmtbox;
  libname sandbox oracle user="&userid" pass="&pw" path='sanddb' schema=sandbox;
endrsubmit;

libname sandbox remote server=rmtbox;

bnarang
Calcite | Level 5

Thanks Guys.

I will try these options. Many Thanks for your valuable inputs.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 3789 views
  • 0 likes
  • 9 in conversation