I like this and I was initially trying something similar to send 1000 examples through Teradata at a time using a where statement on the key. Was getting some sort of Teradata. I initially was thinking that my data being improperly defined as Num/Char was the culprit but I spent too much time on it and essentially started going down the rabbit hole I am in now. I will look through you example as this is a good approach as well. Thank you for taking to time to share this. ... View more

I am joining the 110k records with a table with Millions. The matches are then brought back to SAS to then do additional processing. ... View more

The data transfer into Teradata completes without error. The timeout happens during the inner join. I will look into finding an appropriate Teradata admin to get their input. Thank you for the suggestion. ... View more

Thank you for all feedback and suggestions. Hopefully to clarify my original question some. I was taking population A records and sending to a Temp Table in Teradata so that I can join with another table within Teradata. This Teradata table is not indexed with the primary merge key and is not something that is in scope. The query runs very slowly against millions of transactional records. As a result, the processed times out due to limitations within our SAS environment. I was trying to find a way to split the Temp table into smaller populations so that I can run the query N number of times based upon the volume. However, I did not want to hard code the number of loops or Temp tables because the volume could change. Some of the suggestions that were made helped clear my mental block and I came up with the following. I ran this on a much smaller population of 101 records so that I could do some quick testing. However, it appears to be working. If there is any major inefficiencies, I would not be offended if you smack my hand and advise better ways to do them as I am always open to learning.   options obs=101; Data INV(keep=KEY vol_j); set INVENTORY.LOCATION; vol_j=1; run; proc sort; by vol_j key; options obs=max; Data INV; set INV; by vol_j key; if first.vol_j then order_j=1; else order_j+1; run; Data loop_number(drop=order_j); set INV(keep=order_j); vol_run=10; loopnum=ceil(order_j/vol_run); proc sort; by vol_run descending loopnum; proc sort nodupkey; by vol_run; Data _null_; set loop_number; call symput( 'loop_end_num', loopnum ); call symput( 'vol_per_run', vol_run ); run; /**********************************************************************************/ %macro J_LOOP; %do j=1 %to &loop_end_num; %macro JM_QUERY; Proc sql; connect to INVENTORY2.LOCATION; create table NEW_INV as select * from connection to TERA( select a.KEY ,b.KEY from INVENTORY.TERA a inner join TEMPFILE.TERA_&J b on a.KEY = b.KEY); disconnect from TERA; quit; %mend JM_QUERY; %if &j=1 %then %do; Data Inventory_&J; set INV; if order_j LE &vol_per_run * &J; run; proc delete data=TEMPFILE.TERA_&J; run; data TEMPFILE.TERA_&J; set Inventory_&J; run; %JM_QUERY; Data NEW_INV_ALL; set NEW_INV; run; %end; %else %do; Data Inventory_&J; set INV; if order_j GT ((&vol_per_run)*(&J-1)) and order_j LE (&vol_per_run * &J); run; proc delete data=TEMPFILE.TERA_&J; run; data TEMPFILE.TERA_&J; set Inventory_&J; run; %JM_QUERY; Data NEW_INV_ALL; set NEW_INV_ALL NEW_INV; run; %end; %end; %mend J_LOOP; %J_LOOP; Thank you again for all of the responses and suggestions. I was unsure if anyone would respond to the forum and I have been very pleased with what appears to be a very supporting network of professionals!       ... View more

I do agree indexing would be beneficial for this project and ALL others but I do not see that they are going to put in efforts for the changes needed. I will try working with this. Thank you. ... View more

Yes, That is what kinda being done. Taking the inventory and sending it to Teradata for the Join and upon completion, deleting the temp. I am trying to limit the variables sent to Teradata and doing innner join on the matching key(s) but it is unbearably slow. When matching just 1 record where A.key = B.key, it took about 20 minutes.    Sorry for painting an unfinished picture.   In my mind, I am trying to do the following. 1. Create a counter of # of records in my inventory - RECORD_COUNT 2. Loops = CEIL(RECORD_COUNT/10000) 3. If count of Inventory <= 10K send to Temp_TERADATA location, run QUERY and send to RESULTS_TMP1, delete Temp_TERADATA, (Repeat for Loops 2-N and Set Results 1-N into FINAL_QUERY results) 4. Once I have the matching records, I can the merge/join the results back with my original Inventory to bring back in all needed variables for the end reporting.   ... View more

We are using teradata and the network is very slow M-F. The SQL code I am using is maxed out on the where clause criteria.  I want to say I can share coding but large organization may frown upon this. I know that is not an ideal response but rather not poke the bear. I will try to mock up generic duplication of the code but I believe the main issue is with the data source limitations and not having the file's join key indexed properly. I have used similar code in prior company and the the code runs quickly.    Thank you for your response. ... View more

The indexing of the file would be ideal. Unfortunately, the file that I am joining my Inventory with is indexed in a secondary key that we cannot use. There have been requests to index the file(in Teradata) but this has not been something that is getting prioritized as I believe we may be switching to Snowflake.    I have not used hashobjects yet but I will review. Thank you for the suggestion.   ... View more