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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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