@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.
... View more
