BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mstjean
Fluorite | Level 6

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:

 

mstjean_0-1620676108637.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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".

 

View solution in original post

13 REPLIES 13
ballardw
Super User

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.

mstjean
Fluorite | Level 6

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

 

mstjean_0-1620680321861.png

 

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

 

Thanks again

 

SASKiwi
PROC Star

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.

ballardw
Super User

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.

 

mstjean
Fluorite | Level 6

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!

ballardw
Super User

@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".

 

mstjean
Fluorite | Level 6

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!

Reeza
Super User
Try PROC DATASETS and if you haven't used a schema, try including a schema.
mstjean
Fluorite | Level 6

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:

mstjean_1-1620680525186.png

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

Reeza
Super User
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;
SASKiwi
PROC Star

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

mstjean
Fluorite | Level 6

mstjean_0-1620681402442.png

 

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

 

SASKiwi
PROC Star

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.

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
  • 1595 views
  • 2 likes
  • 4 in conversation