Desktop productivity for business analysts and programmers

ODBC in SAS Enterprise Guide

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

ODBC in SAS Enterprise Guide

Hi all,

I'm trying to open a dataset in SAS Enterprise Guide from ODBC using the following code:

PROC SQL;

CONNECT TO ODBC(DSN='database_name');

CREATE TABLE newtablename AS

SELECT * FROM CONNECTION TO ODBC(SELECT * FROM connectingtablename);

quit;

I get an error message that the ODBC connection cannot be found. This is weird because I can manually import databases from ODBC using File>Open>ODBC and selecting the table I need. However, I then don't know how to call the dataset (which is in my Process Flow).

Either a solution to the coding problem or a way to call my dataset from the Process Flow (or both!) would be very helpful.

Thanks!


Accepted Solutions
Solution
‎08-22-2013 10:27 AM
Community Manager
Posts: 2,891

Re: ODBC in SAS Enterprise Guide

When you select File->Open->ODBC Data, EG connects to the data using your local ODBC drivers and data connections.  The table is viewable in the data grid.  When you run a task (such as query builder), EG makes a copy of the entire table in your WORK library so that SAS code can work with it.

When you submit a PROC SQL program with a CONNECT to ODBC, it's your SAS session that must have access to the appropriate driver and defined DSNs.  So if your SAS session is on another machine, you need to make sure the driver and definitions are present there.  And you also need SAS/ACCESS to ODBC installed with that SAS workspace.

This second method is more efficient than the first, because File->Open->ODBC will route your table through the EG client.  It's convenient for small tables and one-off operations, but it does not scale well to large data tables or batch-style jobs.

More here:

26178 - Efficient Data Access using SAS Enterprise Guide

Chris

View solution in original post


All Replies
Solution
‎08-22-2013 10:27 AM
Community Manager
Posts: 2,891

Re: ODBC in SAS Enterprise Guide

When you select File->Open->ODBC Data, EG connects to the data using your local ODBC drivers and data connections.  The table is viewable in the data grid.  When you run a task (such as query builder), EG makes a copy of the entire table in your WORK library so that SAS code can work with it.

When you submit a PROC SQL program with a CONNECT to ODBC, it's your SAS session that must have access to the appropriate driver and defined DSNs.  So if your SAS session is on another machine, you need to make sure the driver and definitions are present there.  And you also need SAS/ACCESS to ODBC installed with that SAS workspace.

This second method is more efficient than the first, because File->Open->ODBC will route your table through the EG client.  It's convenient for small tables and one-off operations, but it does not scale well to large data tables or batch-style jobs.

More here:

26178 - Efficient Data Access using SAS Enterprise Guide

Chris

PROC Star
Posts: 1,146

Re: ODBC in SAS Enterprise Guide

Sorry, nothing useful to add, but I LOVE the handle.

Another Fringe fan, I assume? Any suggestions for a similar show I might like (I'm still in mourning).

Tom

Frequent Contributor
Posts: 138

Re: ODBC in SAS Enterprise Guide

Thanks!

I'm still mourning the loss of my beloved show as well...and I've yet to find an ongoing show that I feel lives up to the same quality (although I hear good things about Orphan Black).

In terms of older shows, 4400 is a must and Battlestar Galactica has similarly cool plot twists even though it's much more depressing.

Super User
Posts: 11,144

Re: ODBC in SAS Enterprise Guide

The recent version of Battlestar Galactica, not the original.

PROC Star
Posts: 1,146

Re: ODBC in SAS Enterprise Guide

Agreed about Battlestar Galactica!

I'll check out 4400; I think I just saw a few minutes of Orphan Black while channel surfing, and I wasn't impressed, but that's not really a very fair way to judge it. I'll give it a fuller viewing soon.

Thanks for the tips! (And by the way, don't disparage the original Battlestar...it may not have been very good, but it had TWO famous Canadian actors in it...Lorne Green, and John Colicos!!)

There. I've waved the Maple Leaf for today.

Tom

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 8480 views
  • 0 likes
  • 4 in conversation