topic Re: Teradata and SAS in SAS Enterprise Guide

Teradata and SAS

Riana — Wed, 21 Sep 2016 06:55:12 GMT

Hi,

Teradata is faster than SAS and I have been told that it would be better to push queries to Teradata and
run them there.

I have a SAS dataset and a large teradata table(8 million rows) and need to join them. I use a libname statement to

connect to Teradata:
Libname mytera teradata user='xxxx' pass='xxxxx' tdpid=abc schema=abc_efg ;

I'm a little confused,could you give me a code example that

How it would be if I want to join tables in SAS?

How it would be if I want to join them in Teradata?

Thanks.

Re: Teradata and SAS

LinusH — Wed, 21 Sep 2016 08:05:30 GMT

"Faster" is a relative word.

Given the same hardware, I would say that SAS is generally faster, I've worked with a Teradata installation that was much slower that the ordinary SAS 4 core Windows server.

But Teradata can deploy quite large MPP setups, so very large datasets, yes faster because of more iron.

To join in SAS, just issue a libname to your TS schema, Be aware of that the whole TD table will be transferred to the SAS session (unless you have some TD table specific where filtering).

To join in TD, typically you need to upload your SAs table to TD, and then you could use SQL pass-through, or if you got the settings right, perhaps you could even use implicit SQL.

If your SAS table is relatively small, take a look at the DBKEY= data set option.

Re: Teradata and SAS

SASKiwi — Wed, 21 Sep 2016 08:10:36 GMT

What is the joining logic between the Teradata table and the SAS one? How many rows are in your SAS table?

Both tables need to be in the same environment to be joined. If the SAS table is the smaller one then loading that into Teradata as a temporary table is a good approach. You can then run an SQL passthru query in Teradata to do the join.

Re: Teradata and SAS

Riana — Thu, 29 Sep 2016 12:05:03 GMT

Can you please give me an example?

Thanks.

Re: Teradata and SAS

Doc_Duke — Fri, 30 Sep 2016 15:38:48 GMT

Doriana,

As SASKiwi said, both tables need to be in the same environment to do the join. A lot of times, analyst do not have write access to the main database. In that case, we have to do the join in SAS. There are a couple of strategies that I have used.

1) do the SQL join referenceing the two tables and use the SQL code to minimize the columns and rows transfered from TD (using TD passthru subqueries if possible).

2) Use SQL passthru to being in the smallest set of TD data that you can into a local SAS dataset without the SAS table criteria (e.g. subset on 'year' and specific columns). Then use SQL to join resulting SAS datasets.

Re: Teradata and SAS

Patrick — Mon, 03 Oct 2016 01:59:18 GMT

Where processing should happen really depends on the concrete case. What you normally want to minimize with heterogenous joins (=combining data from different data sources) is the movement of data between servers.

Case 1: Big Teradata table, small SAS table, result needed in SAS only a small subset of rows in Teradata

Here you certainly want to push all the processing to Teradata so you need to somehow upload the SAS data into Teradata for the join.

If you only need key columns from the SAS side to sub-set rows in Teradata then "dbkey" is great; else you probably need to go for a temporary table in Teradata (if allowed to do so).

Case 2: Big Teradata table, small SAS table, result needed in SAS are almost all rows from Teradata table

Here processing can happen in SAS; eventually using a data step where you load your small SAS table into a hash and then do the lookup this way. Use Keep/Drop to only load the required columns from Teradata to SAS. There are also options like "readbuff" which you might want to alter from their default settings.

There is no "one fits all" approach but you need to acquire a bit a deeper understanding of how things work to come up with the right solution on a case by case basis. Below a good starting point.

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n1v1cfazem7dejn1xiq60o8zd45g.htm