topic Re: connect to tera via sas in SAS Programming

connect to tera via sas

Ronein — Tue, 04 Feb 2025 07:27:52 GMT

Hello

Via this code I connect to tera table and create a sas table called VBM376 (must use tera/sql language here)

proc sql;  
connect to teradata (server=dwprod  schema=DWP_vall authdomain=TeraDataAuth mode=teradata);
create table VBM376  as 
select * from connection to teradata
(
SELECT   top 5 *
from VBM376_INTERNAL_ORGANIZATION
);
disconnect from teradata;
quit ;

I saw in internet the option to run following code.

I dont see that any sas data set was created here.

My question - what exactly this code is doing?

Is it creating a tera table in tera?

Then can I use this tera table in sas ?


proc sql;
connect to teradata (server=dwprod  schema=DWP_vall authdomain=TeraDataAuth mode=teradata);
 execute(
 create volatile table VBM376_b as (
SELECT   top 5 *
from VBM376_INTERNAL_ORGANIZATION
 )
 with data primary index (Team_IP)
 on commit preserve rows
 ) by teradata;
quit;

Re: connect to tera via sas

LinusH — Tue, 04 Feb 2025 08:05:08 GMT

You are right.
When you are using EXECUTE ( ) BY; "nothing" happens in SAS. In this example a temporary table is created in TD.
If you modify the syntax a bit, you can use it in SAS. See this thread for an example:
https://communities.sas.com/t5/SAS-Enterprise-Guide/Need-a-Volatile-Table-from-Tereadata-to-SAS-EG-Syntax/td-p/926485

Re: connect to tera via sas

Ronein — Tue, 04 Feb 2025 08:47:29 GMT

THANK YOU

tell please what is the benefit of creating a vilotate table in tera via sas?

Is it possible then to merge this tera table with other sas daat set?

Also, I run this code and I dont understand what happens

libname tdwork teradata 
AUTHDOMAIN=TeraDataAuth 
mode=teradata  
server=dwprod
connection=global 
dbmstemp=yes
;
PROC SQL NOERRORSTOP;
CONNECT USING TDWORK as TERADATA;
EXECUTE BY TERADATA 
( create volatile table VBM376_b as
  (
SELECT   top 5 *
from VBM376_INTERNAL_ORGANIZATION
  )
with data primary index(Team_IP) On Commit Preserve Rows 
);

Re: connect to tera via sas

Patrick — Tue, 04 Feb 2025 11:52:30 GMT

@Ronein Just copy/paste your code into Copilot and you'll get already a lot of explanation. If after this something still remains unclear ask a more targeted question - eventually on a Teradata forum if it's about the Teradata SQL portion.

In SAS you can have session scope tables in WORK. They all get dropped automatically when your SAS session ends. In Teradata volatile tables are also session specific and get dropped automatically once the Teradata session ends. And both in SAS and Teradata these session specific tables are only accessible within the session so you also don't have to worry about security.

What's important is connection=global because it enables that consecutive steps like multiple Proc SQLs can use the same connection/Teradata session and though can all access to the volatile table once created.

The most common use case for temporary/volatile DB tables is for loading data. You load your SAS data into a temporary DB table and then do all the inserts and updates in-database. And once your session ends there is no need to clean-up (same as with SAS tables in WORK). ...you just need always to read-up how temporary tables really work for the DB you're using. In Oracle for example temporary tables are a bit more permanent. The table definition is permanent, the data attached to the table definition can have session or transaction scope.

Re: connect to tera via sas

Ronein — Wed, 05 Feb 2025 13:29:06 GMT

So If I open Tera then I can use this violtate table from tera?

What is the using of such code?

I am trying to understand how can I use it for my needs?

Please note that I have both SAS dat sets and tera tables that I need to work with (merge,set, and so on).

My target is to work VIA SAS platform and using sas data sets and tera tables

Re: connect to tera via sas

LinusH — Wed, 05 Feb 2025 13:35:38 GMT

Check with a Teradata DBA to guide you on how to use TD tables.
Not sure what your use case is - what is the purpose of creating volatile tables from SAS, and then access from TD?