topic Re: ODBC Libname does not exist in SAS Programming

ODBC Libname does not exist

mstjean — Mon, 10 May 2021 19:49:23 GMT

Can anyone help with with accessing data from the ODBC database connection? The Libref was successfully assigned, however, I can not access that data of the contents. Please see the log below:

Re: ODBC Libname does not exist

ballardw — Mon, 10 May 2021 19:58:21 GMT

To reference a data set in a library the syntax is LIBNAME.Datasetname (that little dot is important) . You only reference a data set without the libname, that tells SAS the data set would be in the work library by default.

If you want to try to list all the members of a library the syntax is

proc contents data=libname._all_;

run;

Not all ODBC connections will provide access to proc contents it seems. If that appears to be your situation then copy the data sets to a new library and then try that library.

Re: ODBC Libname does not exist

Reeza — Mon, 10 May 2021 20:10:13 GMT

Try PROC DATASETS and if you haven't used a schema, try including a schema.

Re: ODBC Libname does not exist

SASKiwi — Mon, 10 May 2021 20:14:29 GMT

Also try expanding the library in the EG/Studio Server list or SAS Windowing System Library window and check if any tables show.

Re: ODBC Libname does not exist

mstjean — Mon, 10 May 2021 20:59:14 GMT

Thanks for your reply, I have tried your suggestion to list all members of the library and this is the error I have received:

Is this indicative of an issue of the library assignment itself?

Thanks again

Re: ODBC Libname does not exist

mstjean — Mon, 10 May 2021 21:02:39 GMT

Thank you for your suggestion,

I have already included a schema in the library assignment statement, when I used the proc datasets, I have received the following error:

I am wondering if the issue is that there is no tables in this library. Is that possible?

Re: ODBC Libname does not exist

Reeza — Mon, 10 May 2021 21:04:58 GMT

It's possible, but ODBC also doesn't always let you access the metadata so sometimes PROC DATASETS cannot work. Try just accessing a table you know exist and see if it works.

proc contents data=denodo.myTableName ;
run;

Re: ODBC Libname does not exist

SASKiwi — Mon, 10 May 2021 21:11:49 GMT

Correct. At the very least you will need server= and database= components in your database connection string. The QUALIFIER = option may also be necessary.

Can you post your actual LIBNAME statement without the masking? You can make up the names that might be sensitive.

Re: ODBC Libname does not exist

ballardw — Mon, 10 May 2021 21:15:18 GMT

ODBC connections in general are variable. Some are one-way, i.e. you can "read the data" but requests, such as Proc Contents would send, are not responded to, or at least not in a manner that Contents can use. If you really need Proc Contents to tell you about the data then you need to copy the data from the ODBC source to an actual SAS data set or sets. That may involve requesting specific data sets one at a time or maybe Proc Copy can get all the sets at once. The limits depend on the actual ODBC connection.

If the source is a database you might be able to request the information from the database metadata table(s) if it has any. Maybe.

Re: ODBC Libname does not exist

mstjean — Mon, 10 May 2021 21:17:14 GMT

No tables are showing when I try to expand it, is this the issue?

Re: ODBC Libname does not exist

mstjean — Mon, 10 May 2021 21:20:38 GMT

I should clarify that I do not need proc contents; I need to analyze the data of one of the tables, in which I know the name. I wanted to begin by examining the contents to see if the library had tables.

How should I proceed if I was to simply set one of tables in a data step. for example,

data 'mydata';

set 'existingODBCtable'

Thanks!

Re: ODBC Libname does not exist

ballardw — Mon, 10 May 2021 21:28:05 GMT

@mstjean wrote:

I should clarify that I do not need proc contents; I need to analyze the data of one of the tables, in which I know the name. I wanted to begin by examining the contents to see if the library had tables.

How should I proceed if I was to simply set one of tables in a data step. for example,

data 'mydata';

set 'existingODBCtable'

Thanks!

Unless you want to perpetuate coding headaches drop the quotes on the SAS data set names.

Data mydata.

set denodo.remotedatasetname.

run;

If the ODBC source has non-SAS Standard names then you would enclose the name in quotes and use an N to indicate such. SAS data set names start with _ or letter and contain only _, letter or digits in the name. So any spaces, characters like - * % / are not standard and the name would look like

Set denodo."name with spaces"n ;

The N and quotes that way tells SAS it is a "name literal".

Re: ODBC Libname does not exist

mstjean — Mon, 10 May 2021 21:38:15 GMT

Thank you so much for this!!! It worked! I can't thank you enough. You've saved me so much time with this. Take care!

Re: ODBC Libname does not exist

SASKiwi — Mon, 10 May 2021 23:21:10 GMT

Could well be.

Might be worth using the SAS option: OPTIONS validvarname = V7; - to ensure you end up with SAS-valid table names showing. Also be aware that only table names up to 32 characters long will show. There is no workaround for this.