The source data ('work.test') used for loading is created directly in the script via DATA STEPS. The destination is an external Teradata database, connected via a SAS/ACCESS libname.
1 Code Block
LIBNAME
Explanation : Defines a SAS library (`mybulk`) to connect to a Teradata database using SAS/ACCESS. The `tpt=yes` option enables the Teradata Parallel Transporter for more efficient bulk data loading. The macro variables `&dbms` and `&CONNOPT` are assumed to be defined upstream for the database connection.
Copied!
libname mybulk &dbms &CONNOPT tpt=yes;
1
LIBNAME mybulk &dbms &CONNOPT tpt=yes;
2 Code Block
PROC DELETE
Explanation : Deletes tables 'test1' and 'test2' from the Teradata database (`mybulk`) if they exist, ensuring a clean environment for the demonstration.
Explanation : Creates a temporary SAS dataset named 'work.test' with a single observation where variable 'x' has the value 1. This dataset serves as the source for loading operations.
Copied!
data work.test;
x=1; output;
run;
1
DATA work.test;
2
x=1; OUTPUT;
3
RUN;
4 Code Block
PROC APPEND
Explanation : Appends data from the SAS dataset 'work.test' to the Teradata table 'mybulk.test1'. The `tpt=yes` option enables TPT and `fastload=yes` indicates using TPT's fastload mode, which is generally more performant for large data volumes.
Explanation : Deletes table 'test1' from Teradata before recreating it with PROC SQL.
Copied!
proc delete data=mybulk.test1; run;
1
PROC DELETEDATA=mybulk.test1; RUN;
6 Code Block
PROC SQL
Explanation : Creates a new Teradata table 'mybulk.test2' from the SAS dataset 'work.test' using PROC SQL. The `tpt=yes` and `fastload=yes` options are applied at creation time for efficient data loading.
Copied!
proc sql;
create table mybulk.test2 (tpt=yes fastload=yes) as
select * from work.test;
quit;
1
PROC SQL;
2
create TABLE mybulk.test2 (tpt=yes fastload=yes) as
3
select * from work.test;
4
QUIT;
7 Code Block
DATA STEP Data
Explanation : Recreates the temporary SAS dataset 'work.test' with five observations (x from 1 to 5). This updated dataset will be used to demonstrate other TPT options.
Explanation : Creates the Teradata table 'mybulk.test1' from 'work.test' using PROC SQL with TPT options `tpt=yes` and `fastload=yes` for fast loading.
Copied!
proc sql;
create table mybulk.test1 (tpt=yes fastload=yes)
as select * from work.test;
quit;
1
PROC SQL;
2
create TABLE mybulk.test1 (tpt=yes fastload=yes)
3
as select * from work.test;
4
QUIT;
9 Code Block
PROC DELETE
Explanation : Deletes table 'test1' from Teradata before recreating it with the 'multistmt' option.
Copied!
proc delete data=mybulk.test1; run;
1
PROC DELETEDATA=mybulk.test1; RUN;
10 Code Block
PROC SQL
Explanation : Creates the Teradata table 'mybulk.test1' with the TPT option `multistmt=yes`, which can improve performance by combining multiple SQL statements into a single TPT transaction.
Copied!
proc sql;
create table mybulk.test1 (tpt=yes multistmt=yes)
as select * from work.test;
quit;
1
PROC SQL;
2
create TABLE mybulk.test1 (tpt=yes multistmt=yes)
3
as select * from work.test;
4
QUIT;
11 Code Block
PROC DELETE
Explanation : Deletes table 'test1' from Teradata before recreating it with the 'multiload' option.
Copied!
proc delete data=mybulk.test1; run;
1
PROC DELETEDATA=mybulk.test1; RUN;
12 Code Block
PROC SQL
Explanation : Creates the Teradata table 'mybulk.test1' with the TPT option `multiload=yes`, another TPT optimization technique for parallel data loading.
Copied!
proc sql;
create table mybulk.test1 (tpt=yes multiload=yes)
as select * from work.test;
quit;
1
PROC SQL;
2
create TABLE mybulk.test1 (tpt=yes multiload=yes)
3
as select * from work.test;
4
QUIT;
13 Code Block
PROC DELETE
Explanation : Performs a final cleanup by deleting tables 'test1' and 'test2' created in Teradata during the demonstration.
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
Copyright Info : NAME: tpt.sas
TITLE: Sample Programs
PRODUCT: SAS/ACCESS to Teradata
SYSTEM: z/OS, UNIX, WINDOWS
REF: SAS/ACCESS 9 for Relational Databases: Reference
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.
This site uses technical and analytical cookies to improve your experience.
Read more.