DATA Step, Macro, Functions and more

SAS Remote Library Invisible Datasets

Reply
Frequent Contributor
Posts: 86

SAS Remote Library Invisible Datasets

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.

Regular Contributor
Posts: 217

Re: SAS Remote Library Invisible Datasets

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.


Super User
Posts: 6,972

Re: SAS Remote Library Invisible Datasets

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 86

Re: SAS Remote Library Invisible Datasets

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

Regular Contributor
Posts: 217

Re: SAS Remote Library Invisible Datasets

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.

Contributor
Posts: 27

Re: SAS Remote Library Invisible Datasets

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

Frequent Contributor
Posts: 86

Re: SAS Remote Library Invisible Datasets

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 ;

Contributor
Posts: 33

Re: SAS Remote Library Invisible Datasets

libname abc slibref=abc server = user ;

You need the slibref statment ;-)

Frequent Contributor
Posts: 86

Re: SAS Remote Library Invisible Datasets

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

Occasional Contributor
Posts: 6

Re: SAS Remote Library Invisible Datasets

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.

Valued Guide
Posts: 3,208

Re: SAS Remote Library Invisible Datasets

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

---->-- ja karman --<-----
Regular Learner
Posts: 1

Re: SAS Remote Library Invisible Datasets

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;

Frequent Contributor
Posts: 86

Re: SAS Remote Library Invisible Datasets

Thanks Guys.

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

Ask a Question
Discussion stats
  • 12 replies
  • 1056 views
  • 0 likes
  • 8 in conversation