0

I am creating a ETL job in SAS DI Studio to read Teradata table into SAS, then to apply user written code on top of it and then load the SAS work table into Teradata again using Teradata Table Loader.

What is the best way to read the large teradata table (50Cr or more records) into SAS work space? I am currently using following Base SAS code to create a work table:

data work.out_table;
 set db.in_table;
  <sas-statements>;
run;
sushil
  • 1,576
  • 10
  • 14
Harshad Patil
  • 313
  • 1
  • 3
  • 13
  • FYI: "Cr" (Crore) is an Indian term meaning 100,000 (written 1,00,000), but is not commonly used outside of India and the region; you may get better responses if you use the more common, American terminology (so, 1000=thousand, 1,000,000=million, etc.). – Joe Feb 25 '15 at 17:34
  • You will also get better responses if you give a bit more information here. The big thing you're missing is whether `db.in_table` contains rows or columns that are unnecessary for `work.out_table`. – Joe Feb 25 '15 at 17:36
  • to correct, I want to extract entire data from teradata table(s) which contains 500 million or more records. Thanks. – Harshad Patil Feb 25 '15 at 17:46
  • Ah, right - crore is 10 million, not 100 thousand. I should know that, and yet I get it wrong... I will answer later if nobody else does, but I have no DI Studio experience so I'd rather wait for someone like Dom who might have a more appropriate specific solution. Your solution is potentially a good one, depending on your situation. – Joe Feb 25 '15 at 17:51
  • Hello Harshad, what kind of statements are you using? Have you considered in database processing? – jaamor Feb 28 '15 at 19:13
  • Hi, I want to do following operations on the data after getting into SAS: 1. Apply strip and tranwrd function to all character variables using character array. 2. Apply user written code for checking missing and duplicate records and send invalid records to error table. 3. Finally, load valid records into teradata target table. – Harshad Patil Mar 01 '15 at 15:15

1 Answers1

0

Try adding FASTEXPORT=YES option in the Library reference for Teradata or adding dataset option for the Input table within Table option tab of Append or TableLoader transformation.

I'm assuming you are on Teradata 13 > greater. If you are using older version then instead of FASTEXPORT=YES use DBSLICEPARM=ALL parameter. Since FASTEXPORT uses TPT API and that was introduced from Teradata 13 onwards.

sushil
  • 1,576
  • 10
  • 14