topic Re: SAS--TERA in SAS Programming

SAS--TERA

Ronein — Tue, 04 Feb 2025 09:02:03 GMT

Hello

Let's say that I work on 2 tera tables in order to create sas data set.

Here are 3 ways: way1 is 100% working well , Way2 is also 100% working well.

I want to ask about way3 only in order to learn it.

Is it possible to -

A-create a violate table1 in Tera

B-create a violate table2 in Tera

C-create a violate table3 in Tera by merge violate table1+ violate table2

D-Create SAS data set from violate table3

/***WAY1***/
/***WAY1***/
/***WAY1***/
proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table VBM374  as 
select * from connection to teradata
(
SELECT   top 100000  Branch_Cust_Nbr as customer_ID,Branch_Cust_IP,first_Branch_Cust_IP as numerator,Team_IP
from VBM374_USED_BRANCH_CUSTOMER
);
disconnect from teradata;
quit ;
proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table VBM376  as 
select * from connection to teradata
(
SELECT   top 100000 Team_IP,Branch_Nbr 
from VBM376_INTERNAL_ORGANIZATION
);
disconnect from teradata;
quit ;
proc sql;
create table WANT_WAY1 as
select a.*,b.Branch_Nbr
from VBM374 as a
inner join VBM376  as b
on a.Team_IP=b.Team_IP
;
quit;
/***WAY2***/
/***WAY2***/
/***WAY2***/
proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table WANT_WAY2  as 
select * from connection to teradata
(
SELECT   top 100000  a.Branch_Cust_Nbr as customer_ID,a.Branch_Cust_IP,a.first_Branch_Cust_IP as numerator,a.Team_IP,b.Branch_Nbr
from VBM374_USED_BRANCH_CUSTOMER as a
inner join VBM376_INTERNAL_ORGANIZATION as b
on a.Team_IP=b.Team_IP
);
disconnect from teradata;
quit ;
 /***WAY3***/
 /***WAY3***/
 /***WAY3***/
 /**Create violtate table1 in tera data ***/
/*SELECT   top 100000  Branch_Cust_Nbr as customer_ID,Branch_Cust_IP,first_Branch_Cust_IP as numerator,Team_IP*/
/*from VBM374_USED_BRANCH_CUSTOMER*/
 
 /**Create violtate table2 in tera data ***/
/*SELECT   top 100000 Team_IP,Branch_Nbr */
/*from VBM376_INTERNAL_ORGANIZATION*/

 /**Create violtate table3 in tera data ***/
/*by merge violtate table1 +violtate table2*/

/**Create sas data set from violtate table3**/

Re: SAS--TERA

Patrick — Tue, 04 Feb 2025 11:35:46 GMT

I'd go for your Way 2 because it can take advantage of indexes for the join (if they exist), of Teradata parallel processing and importantly it will reduce the data volume to what you really need on the SAS side. The data transfer between SAS and a DB is normally the bottleneck so you want to reduce data volumes as much as possible before transferring them.

For your way 3: Yes, of course, you can also first create volatile tables. They are pretty much the same to work with than permanent tables. You just need to ensure that you run all the processing in the same Teradata session (connection=global).

I would define the connection to Teradata via a libname statement that you then use in your connect statement (like in the code in your other question here).

Using a libname statement has also the advantage that you can use the same libref in both Proc SQL and a data step that then all can connect to the same Teradata session (if using connection=global).

Re: SAS--TERA

Tom — Wed, 05 Feb 2025 18:53:59 GMT

Why do you keep connecting and disconnecting from the database? That will make it hard to use volatile tables since they will disappear when you connection ends.

It will be much easier if you just make a libref that points to the database and leave it open as long as you need it. Then in your SQL steps you can CONNECT USING to have SQL re-use the already existing connection.

libname tera teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth;
proc sql;
connect using tera;
execute by tera 
( ... teradata statement here ...
);
create table want as select * from connection to tera
( .. teradata query here ...
);
quit;

When you done using Teradata you can just clear the libref.

libname tera clear;

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 08:07:00 GMT

I don't understand this code.

As I said-

I want to create a violtate table1 IN TERA

I want to create a violtate table2 IN TERA

I want to merge In TERA the two violtate tables (Result be violtate table3)

Then I want to create SAS data set from the violtate table3

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 08:08:46 GMT

Thanks,

I dont understand your code.

You wrote one time "tera staement"

in other time you wrote "tera query"

I dont understand the difference between tera query and tera sdtatement

Also ,I see only one create table statement and I guess it create the data set in sas

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 08:14:00 GMT

Can you please show full code of Way 3?

Thank you so much

Re: SAS--TERA

LinusH — Wed, 05 Feb 2025 09:48:16 GMT

Statement in this context means an operation that doesn't give you any data back as a result. CREATE is an example.
A query returns a result in form of data, i.e., SELECT.

Re: SAS--TERA

Patrick — Wed, 05 Feb 2025 10:38:12 GMT

@Ronein wrote:

Can you please show full code of Way 3?

Thank you so much

You've got already almost all the pieces and just need to put them together. I also strongly suggest that you spend a bit of time reading in the SAS and Teradata docu the bits you don't fully understand. That's really the only way to really learn and understand.

I've take known bits and copy/pasted them into Copilot.

And here the answer I've got which looks to me like valid code. Test it and if there are issues debug. That's how you learn.

Connect to Teradata using the LIBNAME statement:

libname tdwork teradata 
    AUTHDOMAIN=TeraDataAuth 
    mode=teradata  
    server=dwprod
    connection=global 
    dbmstemp=yes;

Create the volatile tables and merge them:

proc sql;
    connect using tdwork;
    
    /* Create volatile table1 */
    execute (
        CREATE VOLATILE TABLE volatile_table1 AS
        (SELECT TOP 100000
            Branch_Cust_Nbr AS customer_ID,
            Branch_Cust_IP,
            first_Branch_Cust_IP AS numerator,
            Team_IP
        FROM VBM374_USED_BRANCH_CUSTOMER)
        ON COMMIT PRESERVE ROWS
    ) by tdwork;
    
    /* Create volatile table2 */
    execute (
        CREATE VOLATILE TABLE volatile_table2 AS
        (SELECT TOP 100000
            Team_IP,
            Branch_Nbr
        FROM VBM376_INTERNAL_ORGANIZATION)
        ON COMMIT PRESERVE ROWS
    ) by tdwork;
    
    /* Create volatile table3 by merging table1 and table2 */
    execute (
        CREATE VOLATILE TABLE volatile_table3 AS
        (SELECT
            a.customer_ID,
            a.Branch_Cust_IP,
            a.numerator,
            b.Team_IP,
            b.Branch_Nbr
        FROM volatile_table1 a
        LEFT JOIN volatile_table2 b
        ON a.Team_IP = b.Team_IP)
        ON COMMIT PRESERVE ROWS
    ) by tdwork;
    
    disconnect from tdwork;
quit;

Create a SAS data set from the volatile table3:

data work.final_dataset;
    set tdwork.volatile_table3;
run;

proc print data=work.final_dataset;
run;

In this example:

The libname statement establishes a global connection to the Teradata database using the LIBNAME reference tdwork.
The proc sql block uses this LIBNAME reference to create three volatile tables: volatile_table1, volatile_table2, and volatile_table3.
The volatile_table3 is created by merging volatile_table1 and volatile_table2 using a LEFT JOIN.
Finally, the data step creates a SAS data set from the volatile_table3 table.

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 13:12:28 GMT

In your code should use Tera language or sas language?

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 13:26:03 GMT

you please show the code?

Then I will apply it and let you know if it is working

Re: SAS--TERA

LinusH — Wed, 05 Feb 2025 13:37:37 GMT

All syntax within an EXCUTE or ..FROM CONNECTION TO blocks need to be in RDBMS specific.
Anything outside these blocks should be SAS PROC SQL syntax.

Re: SAS--TERA

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

I suggest that you read through the documentation for a better understanding:
https://documentation.sas.com/doc/en/lrcon/9.4/n1kbstf7vw0qcjn1ibfc8c78a9lc.htm

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 15:58:25 GMT

So use DBMS SQL syntax

Re: SAS--TERA

Tom — Wed, 05 Feb 2025 18:53:36 GMT

@Ronein wrote:
In your code should use Tera language or sas language?

It was already clear which parts are using SAS syntax and which parts are using Teradata syntax.

libname tera teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth;
proc sql;
connect using tera;
execute by tera 
( ... teradata statement here ...
);
create table want as select * from connection to tera
( .. teradata query here ...
);
quit;

The code shown is all SAS code. The parts with the ... elipses ... are where you place the Teradata code.

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 18:50:35 GMT

Thank you so much.
I will apply it and let you know how it worked

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 21:10:10 GMT

Hello

I tried to run this code but get error.

I also see that in the libname statement that you wrote there is no schema=DWP_vall

libname tdwork teradata 
AUTHDOMAIN=TeraDataAuth 
mode=teradata  
server=dwprod
connection=global 
dbmstemp=yes;
proc sql;
connect using tdwork;
/* Create volatile table1 */
execute 
(
CREATE VOLATILE TABLE volatile_table1 AS
(SELECT TOP 1000 Branch_Cust_Nbr AS customer_ID,Branch_Cust_IP,first_Branch_Cust_IP AS numerator,Team_IP
 FROM VBM374_USED_BRANCH_CUSTOMER)
 ON COMMIT PRESERVE ROWS
)
by tdwork;
disconnect from tdwork;
quit;

Here is the error in Log

1 The SAS System 10:47 Wednesday, February 5, 2025

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program 1';
4 %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
NOTE: The ACCESSIBLE_GRAPH option is pre-production for this release.
21 ENCODING='utf-8'
22 STYLE=HTMLBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28
29
30
31 libname tdwork teradata
32 AUTHDOMAIN=TeraDataAuth
33 mode=teradata
34 server=dwprod
35 connection=global
36 dbmstemp=yes;
NOTE: Libref TDWORK was successfully assigned as follows:
Engine: TERADATA
Physical Name: dwprod

37 proc sql;
38 connect using tdwork;
39 /* Create volatile table1 */
40 execute
41 (
42 CREATE VOLATILE TABLE volatile_table1 AS
43 (SELECT TOP 1000 Branch_Cust_Nbr AS customer_ID,Branch_Cust_IP,first_Branch_Cust_IP AS numerator,Team_IP
44 FROM VBM374_USED_BRANCH_CUSTOMER)
45 ON COMMIT PRESERVE ROWS
46 )
47 by tdwork;
ERROR: Teradata execute: Syntax error: expected something between ')' and the 'ON' keyword.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
48 disconnect from tdwork;
NOTE: Statement not executed due to NOEXEC option.
2 The SAS System 10:47 Wednesday, February 5, 2025

49 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 95.21k
OS Memory 24984.00k
Timestamp 02/05/2025 11:09:09 PM
Step Count 127 Switch Count 0
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

50
51 %LET _CLIENTTASKLABEL=;
52 %LET _CLIENTPROCESSFLOWNAME=;
53 %LET _CLIENTPROJECTPATH=;
54 %LET _CLIENTPROJECTPATHHOST=;
55 %LET _CLIENTPROJECTNAME=;
56 %LET _SASPROGRAMFILE=;
57 %LET _SASPROGRAMFILEHOST=;
58
59 ;*';*";*/;quit;run;
60 ODS _ALL_ CLOSE;
61
62
63 QUIT; RUN;
64

Re: SAS--TERA

Ronein — Wed, 05 Feb 2025 21:46:15 GMT

Great now it is working 100% and 3 violate tables were created in tera

How can I now create a sas data set from violate3 (the last violate table)?

proc sql; 
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth connection=global); 
/* Create volatile table1 */
execute( 
create volatile table volatile1 as
(
SELECT TOP 100000
            Branch_Cust_Nbr AS customer_ID,
            Branch_Cust_IP,
            first_Branch_Cust_IP AS numerator,
            Team_IP
        FROM VBM374_USED_BRANCH_CUSTOMER
)  
with data primary index (Branch_Cust_IP) 
on commit preserve rows  ) by teradata; 
/* Create volatile table2 */
execute( 
create volatile table volatile2 as
(
SELECT TOP 100000
            Team_IP,
            Branch_Nbr
        FROM VBM376_INTERNAL_ORGANIZATION
)  
with data primary index (TEAM_IP) 
on commit preserve rows  ) by teradata; 
 /* Create volatile table3 by merging table1 and table2 */
  execute (
CREATE VOLATILE TABLE volatile3 AS
(SELECT
            a.customer_ID,
            a.Branch_Cust_IP,
            a.numerator,
            b.Team_IP,
            b.Branch_Nbr
        FROM volatile1 as a
        LEFT JOIN volatile2 as b
        ON a.Team_IP =b.Team_IP)

with data primary index (Branch_Cust_IP) 
on commit preserve rows  ) by teradata; 
    disconnect from teradata;
quit;

I tried this code but error--ERROR: Teradata prepare: Object 'volatile3' does not exist. SQL statement was: SELECT * from volatile3.


 proc sql;    
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);   
create table want as
select * from connection to teradata 
(
SELECT * from volatile3
);  
disconnect from teradata; 
quit;

Re: SAS--TERA

Tom — Wed, 05 Feb 2025 21:50:29 GMT

Most likely from the code you posted all three volatile tables were deleted when you closed PROC SQL (and hence closed the connection to Teradata). What happens if you do all four statements in same PROC SQL steps?

This is why I recommend making a LIBREF first and then using that, so your volatile tables will persist until you close the libref.

Re: SAS--TERA

Patrick — Wed, 05 Feb 2025 23:11:26 GMT

The connect statement in your 2nd Proc SQL misses connection=global and though creates a new session on Teradata where the volatile tables don't exists.

Either have all your SQLs under a single Proc SQL under a single connection, or make sure you use in multiple SQLs the exactly same connection string including connection=global or (my preference) define the connection once via a Libname statement (with connection=global) and then use syntax connect using <libref>

Re: SAS--TERA

Tom — Thu, 06 Feb 2025 02:59:03 GMT

@Patrick wrote:

The connect statement in your 2nd Proc SQL misses connection=global and though creates a new session on Teradata where the volatile tables don't exists.

Either have all your SQLs under a single Proc SQL under a single connection, or make sure you use in multiple SQLs the exactly same connection string including connection=global or (my preference) define the connection once via a Libname statement (with connection=global) and then use syntax connect using <libref>

CONNECTION=GLOBAL is not going to magically cause SAS to keep connections open. Instead it is so you can have two more different connections (two libnames or one libname and one SQL step) share the one connection to the database. So for it to allow you to run two separate PROC SQL steps you must have a libref open the whole time. It which point you might as well not bother with the CONNECTION=GLOBAL and just use the CONNECT USING in your PROC SQL steps.