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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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