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.
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.
It may be that you do not have read privilege on the directory, just execute. If the remote server is UNIX, of course.
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
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.
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?
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
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 ;
libname abc slibref=abc server = user ;
You need the slibref statment 😉
Yeah. sorry that is there. Having that also did not help
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.
mostly I code the local libref as Barnipaz indicated. The slibref being sure for the remote lib connection.
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;
Thanks Guys.
I will try these options. Many Thanks for your valuable inputs.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.