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???
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.
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
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????
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
Here is a paper about uploading SAS data set/table into Teradata
https://support.sas.com/resources/papers/proceedings11/142-2011.pdf
Here is another paper has very relevant examples to your processing scenario
Data movement issues: Explicit SQL Pass-Through can do the trick
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).
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.
@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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.