ODBC in SAS Enterprise Guide

[Walternate]

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!

[ChrisHemedinger · Accepted solution]

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

[TomKari]

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

[Walternate]

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.

[ballardw]

The recent version of Battlestar Galactica, not the original.

[TomKari]

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