topic Re: SAS Remote Library Invisible Datasets in SAS Programming

SAS Remote Library Invisible Datasets

bnarang — Thu, 03 Apr 2014 11:49:33 GMT

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.

Re: SAS Remote Library Invisible Datasets

jwillis — Thu, 03 Apr 2014 11:57:39 GMT

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.

Re: SAS Remote Library Invisible Datasets

Kurt_Bremser — Thu, 03 Apr 2014 11:58:29 GMT

It may be that you do not have read privilege on the directory, just execute. If the remote server is UNIX, of course.

Re: SAS Remote Library Invisible Datasets

bnarang — Thu, 03 Apr 2014 12:05:29 GMT

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

Re: SAS Remote Library Invisible Datasets

jwillis — Thu, 03 Apr 2014 12:20:23 GMT

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.

Re: SAS Remote Library Invisible Datasets

SandorSzalma — Thu, 03 Apr 2014 12:49:29 GMT

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

Re: SAS Remote Library Invisible Datasets

bnarang — Thu, 03 Apr 2014 15:40:11 GMT

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 ;

Re: SAS Remote Library Invisible Datasets

Barnipaz — Thu, 03 Apr 2014 15:45:42 GMT

libname abc slibref=abc server = user ;

You need the slibref statment 😉

Re: SAS Remote Library Invisible Datasets

bnarang — Thu, 03 Apr 2014 15:48:24 GMT

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

Re: SAS Remote Library Invisible Datasets

optimist — Thu, 03 Apr 2014 16:45:37 GMT

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.

Re: SAS Remote Library Invisible Datasets

jakarman — Thu, 03 Apr 2014 16:59:44 GMT

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

Re: SAS Remote Library Invisible Datasets

BobFitz — Thu, 03 Apr 2014 18:17:36 GMT

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;

Re: SAS Remote Library Invisible Datasets

bnarang — Fri, 04 Apr 2014 09:16:36 GMT

Thanks Guys.

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

Re: SAS Remote Library Invisible Datasets

ArunAthithan — Wed, 05 Sep 2018 13:24:40 GMT

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?