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