Hi, I have a huge table (Main) which contains millions of records and so many variables like Account,Token and Trans_Type etc. There is an another table (Lookup ) which contains only two variables - Account and Tokens. Now I have to make a final table which will be based on below condition- 1) Corresponding Token from Lookup where substr(Account,15,1)= "2" or Corresponding Token from Main table where Trans_Type = "O". I wrote this code - proc sql; create table Final as select a.*, b.* from Main as a Lookup as b where a.token = b.token | a.Trans_Type = "O" ; quit; Actually I need to join the table because going forward I have to remove the account number column and the corresponding Token values from Lookup Table will be added in the Token Column in Main table. That's why I am first putting Where substr(Account,15,1)="2" in the Lookup table to get the corresponding Token from the Lookup table. Now I have to join both the table where all the Tokens from Lookup table or those token where Trans_Type ="O" in the Main table. So this will like Full Join not left join because of OR condition. So if we execute the full join on huge table size will be increased. So need to search the alternate method. Please let me know any other way except Hash Join. Because demerit with Hash join is it takes lot of memory. As Lookup table is index created on both the column and Main table is also indexed on Account, Token, Trans_Type because Main table is huge table. So this query eventually taking 5-6 hrs of time and also taking the huge memory. I need to reduce the time while extracting the data.
... View more