topic SAS + ODBC not working in version 9 in SAS Programming

SAS + ODBC not working in version 9

GoldFish — Tue, 03 Feb 2009 19:19:26 GMT

In version 8 I used to run this code no problem:
libname ext 'C:\';
PROC SQL;
CONNECT TO ODBC(prompt);
CREATE TABLE ext.Test AS
SELECT * FROM CONNECTION TO ODBC
(
select DateTime from dbo.Date);
%PUT &SQLXMSG;
%PUT &SQLXRC;
DISCONNECT FROM odbc;
quit;

Pretty simple connection to ODBC. But after upgrading to version 9 it gives me the following error:
464 libname ext 'C:\';
NOTE: Libref EXT was successfully assigned as follows:
Engine: V9
Physical Name: C:\
465 PROC SQL;
466 CONNECT TO ODBC(prompt);
ERROR: The ODBC engine cannot be found.
ERROR: A Connection to the ODBC DBMS is not currently supported, or is not installed at your
site.
467 CREATE TABLE ext.Test AS
468 SELECT * FROM CONNECTION TO ODBC
469 (
470 select DateTime from dbo.Date);
ERROR: The ODBC engine cannot be found.
ERROR: A Connection to the ODBC DBMS is not currently supported, or is not installed at your
site.
471 %PUT &SQLXMSG;
ERROR: A Connection to the ODBC DBMS is not currently supported, or is not installed at your
site.
472 %PUT &SQLXRC;
-1
473 DISCONNECT FROM odbc;
ERROR: Connection to the odbc DBMS does not exist.
474 quit;

When I run PROC SETINIT;run; it shows that I do have an ODBC engine... what is the hold up here?

Re: SAS + ODBC not working in version 9

LinusH — Wed, 04 Feb 2009 11:24:25 GMT

Are you sure that SAS/ACCESS was properly installed in SAS9?
I think this issue is best solved by SAS tech support.

Regards,
Linus

Re: SAS + ODBC not working in version 9

pmiddlet72 — Thu, 17 Oct 2013 16:24:16 GMT

Late post i just realized. But that's totally a SAS/ACCESS to ODBC issue. You need a driver manager and the proper driver to the database you intend to contact.

As an additional, yet important note: Depending on if this is a server installation or not, you will also need to deal with what sort of user/pass credentials your db uses. For security purposes, many companies are going, or have gone to using ldap to authorize users. For example, if you are in an environment that requires you use a user/pass from an active directory (ie single sign on / SSO) that will need to be considered when configuring ODBC connection parameters to those data sources. This as we've found can be a big deal and all efforts should be made in checking through these requirements first as any single source of information on how to set this up is not to be found.

Re: SAS + ODBC not working in version 9

SASKiwi — Thu, 17 Oct 2013 18:54:36 GMT

It would be helpful to know what operating system you are using with SAS and what database you are trying to connect to. If you are using Windows then you can configure/test ODBC connections in the ODBC Administrator tool of Windows Control Panel. It will also list what ODBC drivers you have installed.

Re: SAS + ODBC not working in version 9

LinusH — Thu, 17 Oct 2013 18:58:10 GMT

Still, the error implies that there something missing in the installation, not any ODBC manager configuration issue,