Creating a sas compressed dataset from a teradata table

[RupaJ]

I am trying to create a work table from teradata libname statement. The teradata table as Count(*) 5610421124 (5.6 Billion)
the sas dataset that gets produced only has 1.3 billion records. There is no error in the log. 
What could be the reason for records getting dropped? 
Below is the sas code
data diagnosis (compress=yes);
     set pcordata.diagnosis;
run; 

Error Message:
   no errors 

[kiranv_]

5.6 billion records is massive amount of data to move from one system to another. Is there any special reason you are doing it.

[Reeza]

A data step is not the fastest method.

Since you have a libname set up, PROC COPY and/or PROC DATASETS is faster - it moves data in blocks since it doesn't expect things to change. In a data step you can add changes, but since you're not doing any of that here, it's faster to use PROC COPY. 

PROC COPY/DATASETS use the same process so it doesn't matter. 

Moving that amount of records takes time no matter what though.

---

@RupaJ wrote:

I am trying to create a work table from teradata libname statement. The teradata table as Count(*) 5610421124 (5.6 Billion)
the sas dataset that gets produced only has 1.3 billion records. There is no error in the log. 
What could be the reason for records getting dropped? 
Below is the sas code
data diagnosis (compress=yes);
     set pcordata.diagnosis;
run; 

Error Message:
   no errors 

---

[kiranv_]

use  fastexport teradta utlility to bulk transfer the data. you can do it by using fastexport = yes in your teradata libname statement as shown below.

Libname tera Teradata user=myusr1 pw=mypwd1 FASTEXPORT=YES;

[RupaJ]

Thanks for the response!!

Will surely try the fastexport and proc copy. 

[ChrisNZ]

Considering the likely size of such a long table, and supposing this data transfer is really necessary, I would strongly suggest copying to a binary-compressed SPDE library. Compression ratios are much higher than with the BASE or V9 engines.

For example:

libname DEST spde "path-string" compress=binary partsize=50g;

[RupaJ]

Hello ChrisNZ,

Thanks for the response. We just have the base SAS engine. SPD is very interesting though. Probably can suggest moving to SPD during an upgrade. 

[ChrisNZ]

The SPDE engine is part of SAS Base.

Use it and marvel at all you've missed.

[Go]

What are all the features of SPDS, I mean what are the best use cases to opt for SPD ? does it make proc sort faster ?

[ChrisNZ]

SPDE, not SPDS. The latter is a full storage and processing platform.

Yes sorts can be faster if they are not memory-bound, since significantly fewer I/Os are needed for a compressed SPDE table.

[RupaJ]

Yes. Figured that :-). So with fastexport it took 10 hours and with SPDE it took 6 hours. Here is my libname stmt. 

libname allus spde '/SAS_Work' compress=binary partsize=50g;

Can we make it run any faster?

[ChrisNZ]

> So with fastexport it took 10 hours and with SPDE it took 6 hours. Here is my libname stmt. 

You should use both. 

fastexport deals with the teradata side and spde with the sas side.

You need to identify where the bottleneck is before you continue this investigation. Also look at the network.

[Tom]

If you are disk I/O bound then you can tell SPDE engine to spread the partitions across multiple physical disks by giving it more paths to use.