topic SAS to Athena connection - create table in athena in SAS Programming

SAS to Athena connection - create table in athena

PRAVIN_JAIN — Mon, 21 Oct 2024 12:55:40 GMT

I am connecting to Athena through SAS, connection is working and I am able to fetch the data as well into SAS.

After manipulation in SAS, i need to upload result dataset into AWS where i am getting an error as below -

Working libname connection :

libname athtst clear;
%let schema_core="test_db";

libname athtst jdbc schema=&schema_core
driverclass="com.simba.athena.jdbc.Driver"
url=<>;Schema=<>;Workgroup=<>;GlueEndpointOverride=<>;EndpointOverride=<>;AwsCredentialsProviderClass=<>;"
user=<> password="&pass."
classpath="/sas/Software/jdbcdrivers/xx_athena_looker.jar" schema=&schema_core.;

Code -

proc sql;
create table athtst.test_out as
select * from test_in;
quit;

Error -

ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute error: [Simba][AthenaJDBC](100071) An error has been thrown from the
AWS Athena client. No location was specified for table. An S3 location must be specified [Execution ID not available].

I have tried multiple option providing S3 location in above create table statement however its not working. Looking for some guidance on this.

Re: SAS to Athena connection - create table in athena

Amir — Mon, 21 Oct 2024 15:09:43 GMT

Hi,

There are an odd number of quotes (9) in the code:

libname athtst clear;
%let schema_core="test_db";

libname athtst jdbc schema=&schema_core
driverclass="com.simba.athena.jdbc.Driver"
url=<>;Schema=<>;Workgroup=<>;GlueEndpointOverride=<>;EndpointOverride=<>;AwsCredentialsProviderClass=<>;"
user=<> password="&pass."
classpath="/sas/Software/jdbcdrivers/xx_athena_looker.jar" schema=&schema_core.;

Does the log show any messages regarding this?

Thanks & kind regards,

Amir.

Re: SAS to Athena connection - create table in athena

PRAVIN_JAIN — Tue, 22 Oct 2024 06:11:59 GMT

Thanks for response.

Given libname statement is just a sample, I have removed some info from actual statement hence it may have unbalanced quotes. Libname statement is running fine.

Below code works fine if I use source as athena table as source however I get error if I use SAS work table as source.

Success -

proc sql;
create table athtst.test_out as
select * from athtst.test_in;
quit;

Error -

proc sql;
create table athtst.test_out as
select * from test_in;
quit;

My requirement is to upload SAS dataset into Athena.

Re: SAS to Athena connection - create table in athena

Patrick — Tue, 22 Oct 2024 12:07:05 GMT

What happens if you run below code?

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;
proc append base=athtst.test_out data=work.test_in nowarn force;
run;quit;

If this doesn't work please share the SAS log.