BookmarkSubscribeRSS Feed
Venkat4
Quartz | Level 8

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?

8 REPLIES 8
SASKiwi
PROC Star

Load your SAS table into Teradata as a temporary table and do the join there.

Venkat4
Quartz | Level 8

Thanks, I forgot to mention the teradata got only read access, no write access to this production db.  

SASKiwi
PROC Star

Most databases have special temporary space just for this purpose:

 

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

 

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.

Ksharp
Super User
You can use IN operator too.

select *
 from tera 
  where id in (select id form sas);


OR Hash Table.
LinusH
Tourmaline | Level 20
Sorry @Ksharp, those are not very good ways to so it. The result is that all 1.5 billion rows are transferred to the local SAS session.
TD temporary table should be the way to evaluate first.
Data never sleeps
Venkat4
Quartz | Level 8

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.

LinusH
Tourmaline | Level 20
I think that if upload to a temp table you should be able to use implicit pass through.
Data never sleeps
SuryaKiran
Meteorite | Level 14

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.

Thanks,
Suryakiran

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2134 views
  • 3 likes
  • 5 in conversation