topic Re: Query working on Tera tables+SAS data sets- Efficient Way to run it in SAS Programming

Query working on Tera tables+SAS data sets- Efficient Way to run it

Ronein — Mon, 08 Jan 2024 21:08:09 GMT

Hello

I will try to ask my question here in another way,

Let's say that some of the source raw data tables are located in TerData(Tables/Views) and some located in SAS (Data SETS).

Let's say that I need to run a query that work on the Tera tables and during this process create few volatile tables.

Then the last volatile tables is merged with SAS data sets and then get the final data set .

Example:

Let's say that in Tera have tables-Tera1,Tera2,Tera3

Let's say that in SAS have data sets: SAS1,SAS2

Let's say that I want to create volatile table2- V1,V2

Let's say that I want to create SAS data set -Final_Data_Set

Let's say that the query is done by the following steps:

Step1- Create volatile table V1 from Tera table :Tera1,Tera2

Step2-Create volatile table V2 from Tera table :Tera1,Tera3 and volatile table V1

Step3-Create SAS final data set called "Final_Data_Set" From volatile table V1 and SAS1(Data set),SAS2(Data set)

Currently I preform the task by:

Running Step1+Step2 in tera data and then export the result(V2) into txt File

Import the txt File into SAS

Run Step3 in SAS

My question-

Can show please full code how to perform all in SAS?

Remember that the desired task is to create SAS DATA SET(Final_Data_Set)

As you can see I run it now in two platforms-Tera+SAS and I think it is not very convenient

Maybe there is a better way to do it???

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

AhmedAl_Attar — Mon, 08 Jan 2024 21:34:15 GMT

Hi @Ronein

Typically you want to keep the processing close to where most of your data resides!

Depending on the size (Records count, and columns count) of your intermediate query result sets in comparison to your SAS data sets.

Teradata results sets larger than SAS data sets --> Use SAS Bulkload to upload your SAS data sets to temporary (Volatile) tables in Teradata and let Teradata query it.
Teradata results set smaller than SAS data sets --> Download the results sets from Teradata and use native SAS Data/Proc Step(s) to process and manipulate the data.

Generally these are best practices we have been taught to follow.

Check this 2007 paper for reference: Super Size It!!! Maximize the Performance of Your ETL Processes

Hope this helps,

Ahmed

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

Ronein — Mon, 08 Jan 2024 21:46:14 GMT

Thanks,

Your answer say that need to upload SAS data sets to temporary (Volatile) tables in Teradata

or export the TeraData result into txt file and import it into sas and then work on SAS

As I can see your answer require work in both programs-TeraData +SAS

My question- Can't it all be done in SAS????

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

AhmedAl_Attar — Mon, 08 Jan 2024 22:08:30 GMT

@Ronein

I don't think I said this "export the TeraData result into txt file and import it into sas and then work on SAS"!!

In SAS, you can directly create a SAS data set from the result sets of you Teradata query. This is the native functionality of the SAS/ACCESS Interface to Teradata.

There is no need to export to txt and import from txt!

I guess, we need to clearly understand your definition on "Can't it all be done in SAS????" to make sure we are on the same page

- You have data/tables stored in Teradata

- You have data /tables stored as SAS data set (*.sas7bdat) files

What we have been saying (on this thread and your other thread)

- You can use SAS software to submit queries, but these queries will perform much better if you kept the tables where they and only move the subsets where it made sense.

Say you have

1. Teradata table with 10 millions records in it, and another table with 5 million records --> Joining them would result in 3 million records

2. SAS data set with 1 million records --> Joining it with the 3M result set generates 1.5 million records

It would make no sense to download these two large tables (10M, 5M) from Teradata into SAS data sets to do the join!!

You are better off

- Uploading the 1M SAS data set to Teradata, Perform the join with the 3M result set

- Download the 1.5M result set into SAS data set

Hope this clarifies things for you,

Ahmed

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

AhmedAl_Attar — Mon, 08 Jan 2024 22:24:20 GMT

Here is a paper about uploading SAS data set/table into Teradata

https://support.sas.com/resources/papers/proceedings11/142-2011.pdf

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

AhmedAl_Attar — Mon, 08 Jan 2024 22:30:34 GMT

@Ronein

Here is another paper has very relevant examples to your processing scenario

Data movement issues: Explicit SQL Pass-Through can do the trick

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

LinusH — Tue, 09 Jan 2024 08:58:49 GMT

In short, yes.

If you want to use SQL pass through (most control and Teradata like), be sure to have all processing in one session (PROC SQL step).

At the point that you want/need to download the data to your SAS session, use a SELECT * FROM CONNECTION TO (TD query) statement (in the same PROC SQL).

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

Ronein — Tue, 09 Jan 2024 14:09:57 GMT

Is your solution you provided is to run all in sas??

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

LinusH — Tue, 09 Jan 2024 15:00:37 GMT

Depends what you mean by "run" in SAS?

You can control all the execution from SAS, yes. But the part you want to be executed (run?) in Teradata you can do so by using SQL pass through.

Re: Query working on Tera tables+SAS data sets- Efficient Way to run it

Tom — Tue, 09 Jan 2024 15:47:43 GMT

@Ronein wrote:
Is your solution you provided is to run all in sas??

I would definitely run it all WITH SAS, but not all "in" SAS datasets.

But I would still use SAS to send code to Teradata to run. For simple stuff I can let SAS do the conversion into Teradata SQL. For complicated stuff I would use PROC SQL to send my own Teradata SQL to the Teradata server to run.