topic Re: Creating a sas compressed dataset from a teradata table in SAS Programming

Creating a sas compressed dataset from a teradata table

RupaJ — Tue, 10 Apr 2018 23:14:33 GMT

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

Re: Creating a sas compressed dataset from a teradata table

kiranv_ — Tue, 10 Apr 2018 23:35:45 GMT

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.

Re: Creating a sas compressed dataset from a teradata table

Reeza — Wed, 11 Apr 2018 00:41:51 GMT

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

Re: Creating a sas compressed dataset from a teradata table

ChrisNZ — Wed, 11 Apr 2018 04:32:49 GMT

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;

Re: Creating a sas compressed dataset from a teradata table

kiranv_ — Wed, 11 Apr 2018 10:58:36 GMT

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;

Re: Creating a sas compressed dataset from a teradata table

RupaJ — Wed, 11 Apr 2018 14:19:52 GMT

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.

Re: Creating a sas compressed dataset from a teradata table

RupaJ — Wed, 11 Apr 2018 14:21:13 GMT

Thanks for the response!!

Will surely try the fastexport and proc copy.

Re: Creating a sas compressed dataset from a teradata table

ChrisNZ — Wed, 11 Apr 2018 23:37:23 GMT

The SPDE engine is part of SAS Base.

Use it and marvel at all you've missed.

Re: Creating a sas compressed dataset from a teradata table

Go — Wed, 11 Apr 2018 23:51:54 GMT

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 ?

Re: Creating a sas compressed dataset from a teradata table

ChrisNZ — Thu, 12 Apr 2018 00:03:59 GMT

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.

Re: Creating a sas compressed dataset from a teradata table

RupaJ — Thu, 12 Apr 2018 20:58:33 GMT

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?

Re: Creating a sas compressed dataset from a teradata table

ChrisNZ — Thu, 12 Apr 2018 21:22:44 GMT

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

Re: Creating a sas compressed dataset from a teradata table

Tom — Fri, 13 Apr 2018 06:24:27 GMT

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.