topic Re: Reading data from a MS SQL database; which connection type to use? in SAS Enterprise Guide

Reading data from a MS SQL database; which connection type to use?

PoulRavn — Mon, 13 Apr 2015 10:56:36 GMT

Hi, I use EG61 and also for some purposes SAS JMP 11. I have found, that from SAS JMP, I can access a SQL database sitting on a server. It looks as if JMP uses a ODBC type access to the database. I would perfer to do it from EG61, but the connection mechanism seems to be different. I am not able to set up the ODBC data source, but should I use SAS/Access instead. Is it correct, that there are such differences?

Regards

Poul Ravn Sørensen

Re: Reading data from a MS SQL database; which connection type to use?

Kurt_Bremser — Mon, 13 Apr 2015 11:41:26 GMT

What is your SAS setup? Do you work with local or remote SAS, and in case of remote, which operating system does the server use?

Re: Reading data from a MS SQL database; which connection type to use?

PoulRavn — Mon, 13 Apr 2015 11:51:27 GMT

Sorry, I should have mentioned: The SAS installation is on a remote server. It is 9.3.

Regards

Poul

Re: Reading data from a MS SQL database; which connection type to use?

Kurt_Bremser — Mon, 13 Apr 2015 11:54:59 GMT

And is the remote server also Windows, or another platform, and where do you run JMP?

Re: Reading data from a MS SQL database; which connection type to use?

PoulRavn — Mon, 13 Apr 2015 12:02:15 GMT

Yes, both are on Windows. My local JMP is on win7. I am not 100% sure about the remote machine though. What could the choices be? What should I look for?

Re: Reading data from a MS SQL database; which connection type to use?

Kurt_Bremser — Mon, 13 Apr 2015 12:48:02 GMT

Like the modules for accessing RDBMS's, SAS/ACCESS to ODBC needs to be licensed.

So you either need the license for ACCESS to a) ODBC or b) your RDBMS.

WIth a), you need the ODBC source configured on the SAS server, with b), you need the DB client installed and working.

A workaround is to open the ODBC source in Enterprise Guide and import the data via EG, but this is considerably less performant (concurrent in/out transfer on your desktop machine)

Re: Reading data from a MS SQL database; which connection type to use?

PoulRavn — Mon, 13 Apr 2015 16:59:23 GMT

Hi, implicitly I think my question is answered like this: Yes, there is a difference between the way SAS JMP and EG61 connects to a SQL database. Whereas JMP connects via an ODBC mechanism which must come with the complete JMP installation, EG needs extra modules that must be licensed.

Regards Poul

Re: Reading data from a MS SQL database; which connection type to use?

jakarman — Mon, 13 Apr 2015 17:37:14 GMT

run a proc setinit;run; with Eguide and you will the SAS server and modules that are licensed. Licensing with Eguide is server based with the SAS installation on that server.

Eg has no additional modules, it is just a very nice terminal approach. The SAS DI/BI Server installation can possible need additional licenses Still there is is the option to use (limited read-only) ODBC resources defined on your desktop that will upload data for further processing. http://www2.sas.com/proceedings/forum2007/058-2007.pdf

Open in the processflow open menu's (alt mouse button). Below choice open there is a list. Data - program --- Exchange , OleDB, ODBC Other

As it doing an upload it warns you can better user SAS/ACess when having above 10.000 rows locally

Re: Reading data from a MS SQL database; which connection type to use?

ChrisHemedinger — Mon, 13 Apr 2015 17:42:22 GMT

You can use File->Open->OLE DB from SAS Enterprise Guide and connect to SQL Server -- but that's not the most efficient method. I've outlined some of the approach in this thread (about Oracle data):

Chris

Re: Reading data from a MS SQL database; which connection type to use?

PoulRavn — Tue, 14 Apr 2015 13:20:37 GMT

HI again, I agree that I can connect to the database as such. In JMP I can write some quering statements that are performed on the table. However in EG I just import the whole of the file. Seems a little overdoint it when what I want do do is exactly to query the database using some sql statements.

Regards

Poul

Re: Reading data from a MS SQL database; which connection type to use?

ChrisHemedinger — Tue, 14 Apr 2015 13:32:24 GMT

I agree -- it's often overkill to import the entire table, which is how File->Open->OLE DB works. SAS/ACCESS to OLE DB or ODBC is a better option if you have the modules available. There is a short primer on this concept here:

26178 - Efficient Data Access using SAS Enterprise Guide

Chris