I have a teradata table with 1.5 billion rows of data. I need to filter this table on 1+ million account numbers, so I can only get those rows from the teradata table. The second table containing only the account numbers is in SAS locally obtained from a totally different DW.
I thought of using "intersect", but the issue is that the first table has several columns, and the second table got only one column containing account numbers, so it won't work. Then I thought of adding all account numbers into a macro and pass that in the explicit passthrough. But then the macro values will go beyond 32,767 character limit.
It seems I have to use implicit passthrough, but I want this to be done quick not taking hours.
Is there any other ways this can be accomplished within a matter of minutes vs. hours?
Load your SAS table into Teradata as a temporary table and do the join there.
Thanks, I forgot to mention the teradata got only read access, no write access to this production db.
Most databases have special temporary space just for this purpose:
Check with your Teradata DBA to see if you can have access to this. This is the most efficient method - any other cross-database technique will be extremely slow.
You can use IN operator too. select * from tera where id in (select id form sas); OR Hash Table.
Thanks to both. I already tried that in clause using implicit passthrough like you stated, but it didn't finish and I fed up of it, then stopped. We do have write access to a different database within teradata, so I think I will load the 1+mil account numbers into a teradata db that we have access to write, then use that in the in clause filter via explicit passthrough.
I didn't know about the temporary table space - but that is a good info. Thanks again.
To speed up the database access use EXISTS statement in SQL rather than the IN statement
Select *from table_a a
where exists(select*from orders o
where a.prod_id=o.prod_id);
It is best to write a SQL statement with subquery.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.