Creating a sas compressed dataset from a teradata table

Reply
Contributor
Posts: 34

Creating a sas compressed dataset from a teradata table

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

 

PROC Star
Posts: 514

Re: Creating a sas compressed dataset from a teradata table

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.

 

Super User
Posts: 23,784

Re: Creating a sas compressed dataset from a teradata table

[ Edited ]

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

 


 

PROC Star
Posts: 514

Re: Creating a sas compressed dataset from a teradata table

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;
Contributor
Posts: 34

Re: Creating a sas compressed dataset from a teradata table

Thanks for the response!!

 

Will surely try the fastexport and proc copy. 

PROC Star
Posts: 2,375

Re: Creating a sas compressed dataset from a teradata table

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;

 

 

 

 

 

 

 

Contributor
Posts: 34

Re: Creating a sas compressed dataset from a teradata table

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. 

 

 

PROC Star
Posts: 2,375

Re: Creating a sas compressed dataset from a teradata table

[ Edited ]

The SPDE engine is part of SAS Base.

Use it and marvel at all you've missed.

Frequent Contributor
Frequent Contributor
Posts: 75

Re: Creating a sas compressed dataset from a teradata table

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 ?

PROC Star
Posts: 2,375

Re: Creating a sas compressed dataset from a teradata table

[ Edited ]

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.

Contributor
Posts: 34

Re: Creating a sas compressed dataset from a teradata table

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?

PROC Star
Posts: 2,375

Re: Creating a sas compressed dataset from a teradata table

> 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.

Super User
Super User
Posts: 8,128

Re: Creating a sas compressed dataset from a teradata table

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.

Ask a Question
Discussion stats
  • 12 replies
  • 244 views
  • 1 like
  • 6 in conversation