topic Re: SAS Athena Join tables in SAS Programming

SAS athena Join tables

skygold16 — Tue, 13 Nov 2018 03:58:32 GMT

Hi All,

Has anyone experienced joining tables from SAS and Athena?

I have a requirement of joining table one in SAS and another in Athena.

proc sql noprint;
connect to ODBC as athn (DSN="AWS-Athena-SAS-xxx-xxx" );

create TABLE data.join(compress=char) as
select base.*
from connection to athn
(
Select A.id

from data.table_in_sas A

INNER JOIN table_in_s3 B
ON A.id = B.id
) base;
disconnect from athn;
quit;

SAS Athena Join tables

skygold16 — Tue, 13 Nov 2018 03:57:12 GMT

Hi All,

Has anyone experienced joining tables from SAS and Athena?

I have a requirement of joining table one in SAS and another in Athena.

proc sql noprint;
connect to ODBC as athn (DSN="AWS-Athena-SAS-xxx-xxx" );

create TABLE data.join(compress=char) as
select base.*
from connection to athn
(
Select A.id

from data.table_in_sas A

INNER JOIN table_in_s3 B
ON A.id = B.id
) base;
disconnect from athn;
quit;

Re: SAS athena Join tables

ChrisNZ — Tue, 13 Nov 2018 04:47:51 GMT

This is not possible with any data base.

The join must take place where both tables are.

So either you download the athena table to sas or you upload the sas table to athena.

The does the former:

libname ATH odbc DSN="AWS-Athena-SAS-xxx-xxx" user=xxx etc ;
proc sql ;
  create table SASLIB.TAB3 as
  select *
  from ATH.TAB1, SASLIB.TAB2
  where ID1=ID2;
quit;

Re: SAS Athena Join tables

ChrisNZ — Tue, 13 Nov 2018 08:26:10 GMT

What is this? Why do you ask the same question twice? Do you understand the reply? Did you try?

Re: SAS Athena Join tables

Kurt_Bremser — Tue, 13 Nov 2018 08:27:37 GMT

There was a double post which I merged. I forgot to activate the notification, sorry for that.

Re: SAS Athena Join tables

ChrisNZ — Tue, 13 Nov 2018 08:30:10 GMT

Ah it makes more sense! :)I usually mark the second post as spam.No worries. Thanks.

Re: SAS Athena Join tables

Kurt_Bremser — Tue, 13 Nov 2018 08:32:30 GMT

@ChrisNZ wrote:
I usually mark the second post as spam.

Haven't considered that (yet). Will do a meditation on it.

Re: SAS Athena Join tables

skygold16 — Tue, 13 Nov 2018 11:56:52 GMT

Hope confusion is clear now. That wasn't intentional to ask same question again, but by mistake might have submited the same question twice.

@ChrisNZ I will try your solution tomorrow and see if it works.