topic ODBC and Redshift not working in LIBNAME statement in SAS Enterprise Guide

ODBC and Redshift not working in LIBNAME statement

illmatic — Thu, 06 Oct 2022 17:59:44 GMT

Hello,

The following statement returns an error. I am utilizing SAS EG 7.1 locally, no connection/server profile setup.

/* ODBC Method */
libname myAWS odbc datasrc=&server
complete="                  &odbparams"
schema=&schema;

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver 
specified
ERROR: Error in the LIBNAME statement.

I have the odbc.ini on my computer updated with correct settings and am able to utilize that in other connections like SQL Workbench, but I am unable to assign a readable library on SAS EG.

The following libname works, but requires odbc wrap which isn't ideal because I don't want to have to memorize schemas and table structures. I'd like to see them in SAS EG in the library section.

libname myAWS ODBC DSN = AWS readbuff=1;

Re: ODBC and Redshift not working in LIBNAME statement

jebjur — Thu, 06 Oct 2022 18:12:13 GMT

To clarify, when you say you are using Enterprise Guide locally, I'm assuming you mean you are connecting to a local SAS server (SAS 9.4 installed on the same PC as Enterprise Guide). Did you install and configure the Redshift ODBC driver in the ODBC administrator on your PC so it can 'talk' to the database?

https://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-connection.html#install-odbc-driver-windows

If so, I would first open Base SAS 9.4 on the PC and try a prompted connection with code such as:

LIBNAME MyAWS ODBC PROMPT=YES;
%put %superq(sysdbmsg);

You will be prompted to select the ODBC DSN you created and also enter the correct information for server, username, etc to connect to the dbase. If that is successful, the necessary connection parameters will be written to the log.

Re: ODBC and Redshift not working in LIBNAME statement

illmatic — Thu, 06 Oct 2022 18:37:18 GMT

That did it. Thanks a bunch!!!