BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASRt
Calcite | Level 5

Hello all,

 

I've tried searching the site but can't quite figure out what I am missing. I have successfully created a table in SAS that I would like to join with a table in SQL I am accessing through Teradata. I have tried to create a temporary table, but this doesn't seem to be working.

I have also tried to reference the location I have saved the SAS data file to, but this doesn't seem to be working within the Teradata execute().

 

The SAS code I am using to create a SAS data set:

proc sql;
connect to teradata as tera(server=? user=? password=? mode=teradata connection=global); create table gold.apple as select distinct code from loc1.codes where product = 'big apples' group by codes;

I would like the below code to pull my previously saved SAS data set and join it with an existing SQL table in Teradata.

execute(
create multiset volatile table ig as (
select distinct s.1,
     g.code,
     s.2,
     s.3
from gold.apple as g left join silver.apple as s
group by code)
with data primary index (code) on commit preserve rows)
by tera;

When I try and run everything I get the following error: ERROR: Teradata execute: Database 'gold'  does not exist. I have tried the steps suggested here: https://communities.sas.com/t5/SAS-Enterprise-Guide/SAS-Access-to-Teradata-How-to-create-Temporary-t..., but it ends up saying I don't have the rights to create a table. I'm sure there's a way to create a linkage, I have a SAS data set and I can pull from Teradata without any issues. I'm hoping someone can point me in the right direction.

Thanks! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Teradata can only "see" its own tables.

You will have to create a libname that points to the Teradata database, and use your SAS code to create the dataset there:

libname gold /* all the necessary options to connect to the Teradata database "gold" */;

proc sql;
create table gold.apple as
select distinct code
from loc1.codes
where product = 'big apples'
group by codes;
quit;

Now you can do

proc sql;
connect to teradata as tera(server=? user=? password=? mode=teradata connection=global);

execute(
  create multiset volatile table ig as (
    select distinct s.1,
      g.code,
      s.2,
      s.3
    from gold.apple as g left join silver.apple as s
    group by code
  )
  with data primary index (code) on commit preserve rows
)
by tera;

quit;

as the dataset is now available as a Teradata table in the database.

View solution in original post

1 REPLY 1
Kurt_Bremser
Super User

Teradata can only "see" its own tables.

You will have to create a libname that points to the Teradata database, and use your SAS code to create the dataset there:

libname gold /* all the necessary options to connect to the Teradata database "gold" */;

proc sql;
create table gold.apple as
select distinct code
from loc1.codes
where product = 'big apples'
group by codes;
quit;

Now you can do

proc sql;
connect to teradata as tera(server=? user=? password=? mode=teradata connection=global);

execute(
  create multiset volatile table ig as (
    select distinct s.1,
      g.code,
      s.2,
      s.3
    from gold.apple as g left join silver.apple as s
    group by code
  )
  with data primary index (code) on commit preserve rows
)
by tera;

quit;

as the dataset is now available as a Teradata table in the database.

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 3181 views
  • 2 likes
  • 2 in conversation