topic Re: Proper syntax to access a Redshift server in libname statement? in SAS Programming

Proper syntax to access a Redshift server in libname statement?

RobertWF1 — Thu, 18 Jul 2024 14:48:10 GMT

The data management people in my company (who are not SAS users) have sent me information to connect to one of our Redshift servers:

User ID: user

Password: pwd

Database Server: svr123

Database Port: 1234

Database: db123

Schema: data_schema_123

My libname statement is as follows:

libname newlib redshift 
user="user" 
password="pwd" 
READBUFF=10000 SUB_CHAR=QUESTIONMARK 
schema="data_schema_123"
database="db123"
server="svr123"
port=1234;

I'm seeing the following error message after executing:

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

Is there anything obviously wrong with my syntax or is this more of an IT issue?

Re: Proper syntax to access a Redshift server in libname statement?

sbxkoenk — Thu, 18 Jul 2024 20:30:54 GMT

@IlBoon ... can you help?

Koen

Re: Proper syntax to access a Redshift server in libname statement?

SASKiwi — Thu, 18 Jul 2024 20:59:59 GMT

Has your SAS installation ever been successfully used with Redshift before or is this the first time such a database connection has been tried? Does your SAS run on a PC or a server? If it is on a PC, for example, then you should check to see if you have a Redshift ODBC driver installed via the Windows ODBC Administrator tool.

Re: Proper syntax to access a Redshift server in libname statement?

RobertWF1 — Thu, 18 Jul 2024 21:25:14 GMT

My company is running SAS Studio in a web browser on a remote desktop.

Not sure about the Redshift ODBC driver - I'll check or ask the IT folks.

I've never had trouble connecting before with SAS, but this is the first time I've accessed data via AWS Redshift.

Thanks for your help!

Re: Proper syntax to access a Redshift server in libname statement?

RobertWF1 — Thu, 18 Jul 2024 21:31:13 GMT

Update:
I do see Amazon Redshift ODBC Driver folders (32 and 64-bit) in Windows>Start Menu>Programs.

Re: Proper syntax to access a Redshift server in libname statement?

SASKiwi — Thu, 18 Jul 2024 22:16:25 GMT

SAS Studio typically uses a remote SAS server. You can find out what that is by running this:

%put &=SYSHOSTNAME;

To access Redshift the 64-bit Redshift ODBC driver would need to be installed on the remote SAS server. Typically this is a job for your SAS administrator. If you have one I suggest you check with them. I suspect your LIBNAME is missing a DSN setting which identifies both the ODBC driver being used and the Redshift server to connect to.

EDIT: Try this DSN = "svr123"

Re: Proper syntax to access a Redshift server in libname statement?

ChrisNZ — Fri, 19 Jul 2024 04:17:44 GMT

@SASKiwi

Why would one use SAS/ACCESS Interface to Amazon Redshift if it relies on ODBC? Why not use SAS/ACCESS Interface to ODBC?

Amazon provides ODBC drivers for linux and Windows.