Run Long Tera language query code in SAS

[Ronein]

Hello

I have long query run in TeraData.

I was asked to run this query in SAS.

I don't want to use the libname method since it required to write SAS language and I want to run same code as it was run in Tera.(I mean same query code).

Then I want to use the connect to tera method  by the following way:

proc sql;
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table ttt as
select * from connection to teradata
(
SELECT *
from
Where
);
disconnect from teradata;

quit ;

I know to do it for each query  but the problem that some queries use the violate table that was created before.

 Can anyone show the full code how to run this code in SAS?

CREATE VOLATILE TABLE  VT5727_DATES_M  AS (
SELECT Max(PROCESS_DATE )   AS END_DATE  
,(CAST('01/10/2023' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE 
,END_DATE/ 100 + 190000 AS MONTH_REPORTED        
FROM   V797_PROCESS_DATE  
)WITH DATA ON COMMIT PRESERVE ROWS; 
 


CREATE VOLATILE TABLE VT5727_POP_LOANS_M AS (
SELECT DISTINCT
DATE AS Run_Date
,VBM374.first_Branch_Cust_IP as  Numerator_DWH 
,VBM374.Branch_Cust_IP
,VBM374.Current_Branch_Cust_IP
,VBM374.Branch_Cust_Nbr
,VBM374.Branch_Nbr
,AS52_SHIUCH_CHAT AS division_nbr    

,DHIA_TASH.AS52_MIS_CHODASHIM 
,DHIA_TASH.AS52_TEDIRUT_KEREN  
,DHIA_TASH.AS52_SUG_DHIA 

,CASE WHEN AS52_SUG_DHIA IN (0,1) THEN  Cast ( DHIA_TASH.AS52_SCHUM_DHIA AS DECIMAL(18,0)) *(V00X613.Exchange_Rate / V00X613.Exchange_Rate_Calc_Currency_Am) ELSE  ZeroIfNull(LOAN_BAL_PREV.Next_Payment_Amt_Nis )   END AS  SCHUM_DHIA 
,DHIA_TASH.AS52_TAR_DHIA 
,CASE WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V1' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM 
	WHEN DHIA_TASH.AS52_TEDIRUT_KEREN IN ('T1037V0','T1037V12') THEN SCHUM_DHIA 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V3' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM/3 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V6' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM/6 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V2' AND DHIA_TASH.AS52_MIS_CHODASHIM BETWEEN 3 AND 4 THEN SCHUM_DHIA * 2 
	  ELSE SCHUM_DHIA * DHIA_TASH.AS52_MIS_CHODASHIM/2  
	  END AS SCHUM_TASHLOMIM_NIDHO 
  
,DHIA_TASH.Curr_Agr_Id AS Agreement_Account_Id 
,VBM442.agreement_open_date 
,VBM442_CURR.Agreement_Due_Close_Date 
,VBM448_ACT_LOAN_AGR_RATE.Interest_Pct
,CASE WHEN DHIA_TASH.AS52_SUG_DHIA = '2' THEN  1 ELSE 0 end AS ind_mitveh_israel    
,VBM442.Loan_Model_Code 
,VBM442.Loan_Serial_Nbr 
,VBM442.loan_type_code 
,CASE WHEN Substr(V02X17_PRODUCT_CREDITS.Type_Of_Sector_Code,6,3)= '45' THEN 1 ELSE 0 END AS ind_house_loan 
,DHIA_TASH.AS52_TAR_KEREN_AFT
,DHIA_TASH.AS52_TAR_RIBIT_AFT

,Greatest(DHIA_TASH.AS52_TAR_KEREN_AFT,VBM452_ACT_LOAN_AGR_TERM.Fund_Next_Pmt_Date) AS END_DHIA_DATE
,CASE WHEN (DHIA_TASH.AS52_TAR_KEREN_AFT/ 100 + 190000) = (DHIA_TASH.AS52_TAR_RIBIT_AFT/ 100 + 190000)  THEN 1 ELSE 0 END AS FULL_DHIA
,DHIA_TASH.AS52_ITRA_MSHRCHT_ACHARON 
,CASE WHEN END_DHIA_DATE > (SEL END_DATE FROM VT5727_DATES_M) THEN 1 ELSE 0 end AS ind_still_dhia
,AS52_ITRA_MSHRCHT   
,CASE WHEN VBM451_ACT_LOAN_AGR_SYMBOL.Delayed_Deal_Code= 'T460V1' AND VBM410.Total_Delay_Bal_Amt <> 0 THEN 1 ELSE 0 end AS ind_Delayed_Deal

,Sum(ZeroIfNull(VBM410.Daily_Bal_Amt)+ZeroIfNull(VBM410.Fund_Linkage_Diff_Bal_Amt)+ZeroIfNull(VBM410.Temporary_Interest_Bal_Amt)+ZeroIfNull(VBM410.Temp_Int_Link_Diff_Bal_Amt)) AS ITRA_AHRONA
,Sum(ZeroIfNull(LOAN_BAL_DHIA.Next_Payment_Amt_Nis )) AS Payment_Amt_DHIA
,Sum(ZeroIfNull(LOAN_BAL_PREV.Next_Payment_Amt_Nis )) AS Payment_Amt_PREV 
,CASE WHEN Payment_Amt_DHIA = 0 AND Payment_Amt_PREV = 0 THEN 0 ELSE  Abs(CASE WHEN FULL_DHIA = 1 OR (Payment_Amt_PREV = 0 AND Payment_Amt_DHIA <> 0 ) THEN 100 ELSE (Payment_Amt_DHIA/Payment_Amt_PREV -1)*100 END) end AS reduce_rate
FROM 
VBM5732_AGR_DELAYED_PAYMENTS AS DHIA_TASH 
INNER JOIN VBM374_USED_BRANCH_CUSTOMER AS  VBM374
ON DHIA_TASH.Prev_Branch_Cust_IP = VBM374.Branch_Cust_IP
LEFT JOIN V01X997_IP_REGULATORY_ACTIVITY_SEGMENT  AS V01X997
ON (VBM374.Branch_Cust_IP = V01X997.Inv_Party_Id OR VBM374.Current_Branch_Cust_IP= V01X997.Inv_Party_Id) AND V01X997.Validity_Date = (SELECT Max(Validity_Date) FROM V01X997_IP_REGULATORY_ACTIVITY_SEGMENT) 
AND V01X997.Customer_Segment IN  (1000,2000,3110,3120,3200,3300)
INNER JOIN VBM442_AGREEMENTS AS VBM442
  ON (VBM442.Branch_Cust_IP=VBM374.Branch_Cust_IP AND DHIA_TASH.Prev_Agr_Id=VBM442.Agreement_Account_Id )
 LEFT JOIN V00X613_EXCHANGE_RATE AS V00X613
        ON V00X613.Exchange_Rate_Date = (SELECT Max(PROCESS_DATE ) FROM V797_PROCESS_DATE)
        AND AS52_MATBEA_ISKA =V00X613.Currency_Id
LEFT JOIN VBM451_ACT_LOAN_AGR_SYMBOL 
ON (VBM451_ACT_LOAN_AGR_SYMBOL.Agreement_Account_Id=VBM442.Agreement_Account_Id)
 LEFT JOIN VBM452_ACT_LOAN_AGR_TERM 
 ON (VBM452_ACT_LOAN_AGR_TERM.Agreement_Account_Id=VBM442.Agreement_Account_Id)
INNER JOIN VBM321_PRODUCTS
ON (VBM321_PRODUCTS.Product_Id=VBM442.Product_Id)
LEFT JOIN V02X17_PRODUCT_CREDITS
ON (V02X17_PRODUCT_CREDITS.Product_Id=VBM321_PRODUCTS.Product_Id AND VBM321_PRODUCTS.Subject_Code='AS')
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS VBM410
ON VBM410.Agreement_Account_Id = DHIA_TASH.Curr_Agr_Id AND VBM410.Agreement_Summary_Date =(SEL END_DATE FROM VT5727_DATES_M)
INNER JOIN VBM442_AGREEMENTS AS VBM442_CURR 
  ON (VBM442_CURR.Branch_Cust_IP=VBM374.Current_Branch_Cust_IP AND DHIA_TASH.Curr_Agr_Id=VBM442_CURR.Agreement_Account_Id )
LEFT JOIN VBM448_ACT_LOAN_AGR_RATE 
ON VBM448_ACT_LOAN_AGR_RATE.Agreement_Account_Id=VBM442_CURR.Agreement_Account_Id 
LEFT JOIN 
		 (SELECT
		LOAN_BAL_PREV.Agreement_Account_Id,
		Max(LOAN_BAL_PREV.Agreement_Summary_Date ) AS max_date
		FROM 
		VBM5732_AGR_DELAYED_PAYMENTS AS DHIA_TASH
		INNER JOIN VBM442_AGREEMENTS AS VBM442
		  ON (DHIA_TASH.Prev_Agr_Id=VBM442.Agreement_Account_Id )
		LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_PREV
		ON (VBM442.Agreement_Account_Id=LOAN_BAL_PREV.Agreement_Account_Id  )
		WHERE LOAN_BAL_PREV.Agreement_Summary_Date  < DHIA_TASH.AS52_TAR_DHIA
		AND DHIA_TASH.Validity_Date = (SELECT Max(Validity_Date) FROM VBM5732_AGR_DELAYED_PAYMENTS)
		AND AS52_TAR_DHIA BETWEEN 1231001 AND  (SEL END_DATE FROM VT5727_DATES_M)
		AND ((AS52_SHIUCH_CHAT NOT IN (833,835) AND AS52_MIS_CHODASHIM  BETWEEN 2 AND 24) OR (AS52_SHIUCH_CHAT IN (833,835) AND AS52_MIS_CHODASHIM>0))
		AND AS52_TAR_KEREN_AFT - AS52_TAR_KEREN_KDM  >0 
		GROUP BY 1) AS LOAN_max_date 
ON (VBM442.Agreement_Account_Id=LOAN_max_date.Agreement_Account_Id)
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_PREV
ON (LOAN_max_date.Agreement_Account_Id=LOAN_BAL_PREV.Agreement_Account_Id AND LOAN_BAL_PREV.Agreement_Summary_Date  = LOAN_max_date.max_date ) 
LEFT JOIN VA7836_HARAVOT_BARZEL_POP AS VA7836 
ON VA7836.Branch_Cust_IP = VBM374.Branch_Cust_IP
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_DHIA 
ON (VBM442.Agreement_Account_Id=LOAN_BAL_DHIA.Agreement_Account_Id AND LOAN_BAL_DHIA.Agreement_Summary_Date = DHIA_TASH.AS52_TAR_DHIA)
WHERE 
AS52_TAR_DHIA BETWEEN 1231001 AND  (SEL END_DATE FROM VT5727_DATES_M)
AND DHIA_TASH.Validity_Date = (SELECT Max(Validity_Date) FROM VBM5732_AGR_DELAYED_PAYMENTS)
AND ((AS52_SHIUCH_CHAT NOT IN (833,835)  AND AS52_MIS_CHODASHIM  BETWEEN 2 AND 24) OR (AS52_SHIUCH_CHAT IN (833,835) AND AS52_MIS_CHODASHIM>0))
AND AS52_TAR_KEREN_AFT - AS52_TAR_KEREN_KDM  >0 
AND VBM442.Agreement_Category_Code  =  3 
AND VBM374.Status_Code<> 'T295V51'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
) WITH DATA PRIMARY INDEX (Current_Branch_Cust_IP,Branch_Cust_IP,Agreement_Account_Id,AS52_TAR_DHIA) ON COMMIT PRESERVE ROWS ;



CREATE VOLATILE TABLE VT5727_LOANS_PIGUR AS (
SELECT
  LOANS_M.Branch_Cust_IP,
  LOANS_M.Agreement_Account_Id,
  VBM442_AGREEMENTS.Agreement_Account_Id AS Agreement_Account_Id_pigur,
  VBM442_AGREEMENTS.Loan_Model_Code,
  VBM442_AGREEMENTS.Loan_Serial_Nbr,
  VBM442_AGREEMENTS.Loan_Type_Code,
  V03X30_LOAN_AGREEMENT.Loan_Value_Date AS pigur_date_ForInPigurToday
FROM
VT5727_POP_LOANS_M AS LOANS_M
INNER JOIN VBM442_AGREEMENTS
ON  (VBM442_AGREEMENTS.Branch_Cust_IP=LOANS_M.Branch_Cust_IP AND VBM442_AGREEMENTS.Loan_Model_Code = LOANS_M.Loan_Model_Code
     AND VBM442_AGREEMENTS.Loan_Serial_Nbr=LOANS_M.Loan_Serial_Nbr )
INNER JOIN V03X30_LOAN_AGREEMENT 
ON (V03X30_LOAN_AGREEMENT.Agreement_Account_Id=VBM442_AGREEMENTS.Agreement_Account_Id AND V03X30_LOAN_AGREEMENT.Loan_Value_Date>= LOANS_M.agreement_open_date)
WHERE
VBM442_AGREEMENTS.Agreement_Category_Code  =  3
AND VBM442_AGREEMENTS.Loan_Type_Code  IN  ( 11, 21, 22  )  
AND LOANS_M.ind_Delayed_Deal = 1  
) WITH DATA PRIMARY INDEX (Branch_Cust_IP,Agreement_Account_Id,Agreement_Account_Id_pigur) ON COMMIT PRESERVE ROWS ;


  

[Patrick]

@Ronein Please reference other discussions if they are related. Like the one here.

The Teradata SQL you share only creates volatile tables. There must be something happening with these because volatile tables are gone once the teradata session terminates.

The SQL you shared is joining a lot of tables. If you would want to execute this all in SAS you would need to load all the source Teradata tables into SAS - which depending on volumes could be very counterproductive. 

The approach normally chosen with source data in a database is to reduce the volumes on the DB side as far as possible and only then load the data into SAS for further processing. 

With Teradata being an MPP environment I'd rather spend the time to performance improve the existing Teradata SQL.

imho this is about some Internet research using the opportunity to upskill a bit with Teradata. Just by doing a very quick search:
How to Optimize Joins in Teradata: A Case Study

Optimizing Teradata Statements containing Multiple JOINS

...first step of course is to identify the critical path and long running steps because tweaking these is where you can gain the most performance. Often when people say performance they actually mean end-to-end elapsed time.

[Ronein]

I don't want to load tera tables into sas! I want to run the query in sas but on tera and the final desired table to be saved as sas set

[Tom]

---

@Ronein wrote:
I don't want to load tera tables into sas! I want to run the query in sas but on tera and the final desired table to be saved as sas set

---

In that case you can either change the code so instead of making a volatile table it just returns the results and run it using FROM CONNECTION TO instead of EXECUTE.

create table sasdataset as select * from connect to td
(select ...... rest of your Teradata query )
;

Or define a libref with temporary table access and copy the volatile table in a second step after the one that creates it.

libname td teradata .... ;
proc sql;
connect using td;
execute by td
(create volatile table XXX .... rest of your Teradata query )
;
quti;

data sasdataset;
  set td.XXX;
run;

[SASJedi]

For a quick read, it appears that all these queries do is create volatile tables in Teradata. When you close the connection to Teradata, the volatile tables are deleted. So what would be the point of running this code in SAS? If you intend to make the volatile tables available for subsequent processing in SAS, you'll need to maintain an open connection to Teradata using the LIBNAME statement GLOBAL option.   

Here is some suggested SAS Help Center reading to help you better understand and plan this process:

• Temporary Table Support for Teradata
• Temporary Table Support for SAS/ACCESS- General Temporary Table Use

If I recall correctly, volatile tables are not entered into the Teradata table metadata catalog so they won't be visible in the SAS library, but they can still be referenced by name in SAS code and manipulated just like any other Teradata table. 

[Ronein]

The point of running in sas is that there should be created a final table based on tera tables and then I need to merge this table with sas data sets in order to get the final table ( data set)

[Tom]

---

@Ronein wrote:
The point of running in sas is that there should be created a final table based on tera tables and then I need to merge this table with sas data sets in order to get the final table ( data set)

---

So that will require more analysis of type of combining you are doing.  The goal should be to reduce the amount of data you need transfer between SAS and Teradata as the transfer is normally the slowest operation.

The scenario I am most familiar with is using SAS to make reports from a large database stored in Teradata (why use Teradata if you data is not large).   So we would first get a list of IDs from the SAS dataset and upload that to Teradata.  Then we could use that list of IDS to subset the large database and derive the variables we need and download that dataset from Teradata to SAS so we could use SAS procedures to analyze and report on the data.

[Patrick]

Just by formatting the huge SQL a bit nicer shows what a monster this is

Spoiler

CREATE VOLATILE TABLE VT5727_POP_LOANS_M AS (
    SELECT DISTINCT DATE AS Run_Date
    , VBM374.first_Branch_Cust_IP AS Numerator_DWH
    , VBM374.Branch_Cust_IP
    , VBM374.Current_Branch_Cust_IP
    , VBM374.Branch_Cust_Nbr
    , VBM374.Branch_Nbr
    , AS52_SHIUCH_CHAT AS division_nbr
    , DHIA_TASH.AS52_MIS_CHODASHIM
    , DHIA_TASH.AS52_TEDIRUT_KEREN
    , DHIA_TASH.AS52_SUG_DHIA
    , CASE 
      WHEN AS52_SUG_DHIA IN (
          0
          , 1
          )
        THEN Cast(DHIA_TASH.AS52_SCHUM_DHIA AS DECIMAL(18, 0)) * (V00X613.Exchange_Rate / V00X613.Exchange_Rate_Calc_Currency_Am)
      ELSE ZeroIfNull(LOAN_BAL_PREV.Next_Payment_Amt_Nis)
      END AS SCHUM_DHIA
    , DHIA_TASH.AS52_TAR_DHIA
    , CASE 
      WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V1'
        THEN SCHUM_DHIA * DHIA_TASH.AS52_MIS_CHODASHIM
      WHEN DHIA_TASH.AS52_TEDIRUT_KEREN IN (
          'T1037V0'
          , 'T1037V12'
          )
        THEN SCHUM_DHIA
      WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V3'
        THEN SCHUM_DHIA * DHIA_TASH.AS52_MIS_CHODASHIM / 3
      WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V6'
        THEN SCHUM_DHIA * DHIA_TASH.AS52_MIS_CHODASHIM / 6
      WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V2'
        AND DHIA_TASH.AS52_MIS_CHODASHIM BETWEEN 3
          AND 4
        THEN SCHUM_DHIA * 2
      ELSE SCHUM_DHIA * DHIA_TASH.AS52_MIS_CHODASHIM / 2
      END AS SCHUM_TASHLOMIM_NIDHO
    , DHIA_TASH.Curr_Agr_Id AS Agreement_Account_Id
    , VBM442.agreement_open_date
    , VBM442_CURR.Agreement_Due_Close_Date
    , VBM448_ACT_LOAN_AGR_RATE.Interest_Pct
    , CASE 
      WHEN DHIA_TASH.AS52_SUG_DHIA = '2'
        THEN 1
      ELSE 0
      END AS ind_mitveh_israel
    , VBM442.Loan_Model_Code
    , VBM442.Loan_Serial_Nbr
    , VBM442.loan_type_code
    , CASE 
      WHEN Substr(V02X17_PRODUCT_CREDITS.Type_Of_Sector_Code, 6, 3) = '45'
        THEN 1
      ELSE 0
      END AS ind_house_loan
    , DHIA_TASH.AS52_TAR_KEREN_AFT
    , DHIA_TASH.AS52_TAR_RIBIT_AFT
    , Greatest(DHIA_TASH.AS52_TAR_KEREN_AFT, VBM452_ACT_LOAN_AGR_TERM.Fund_Next_Pmt_Date) AS END_DHIA_DATE
    , CASE 
      WHEN (DHIA_TASH.AS52_TAR_KEREN_AFT / 100 + 190000) = (DHIA_TASH.AS52_TAR_RIBIT_AFT / 100 + 190000)
        THEN 1
      ELSE 0
      END AS FULL_DHIA
    , DHIA_TASH.AS52_ITRA_MSHRCHT_ACHARON
    , CASE 
      WHEN END_DHIA_DATE > (
          SEL END_DATE FROM VT5727_DATES_M
          )
        THEN 1
      ELSE 0
      END AS ind_still_dhia
    , AS52_ITRA_MSHRCHT
    , CASE 
      WHEN VBM451_ACT_LOAN_AGR_SYMBOL.Delayed_Deal_Code = 'T460V1'
        AND VBM410.Total_Delay_Bal_Amt <> 0
        THEN 1
      ELSE 0
      END AS ind_Delayed_Deal
    , Sum(ZeroIfNull(VBM410.Daily_Bal_Amt) + ZeroIfNull(VBM410.Fund_Linkage_Diff_Bal_Amt) + ZeroIfNull(VBM410.Temporary_Interest_Bal_Amt) + ZeroIfNull(VBM410.Temp_Int_Link_Diff_Bal_Amt)) AS ITRA_AHRONA
    , Sum(ZeroIfNull(LOAN_BAL_DHIA.Next_Payment_Amt_Nis)) AS Payment_Amt_DHIA
    , Sum(ZeroIfNull(LOAN_BAL_PREV.Next_Payment_Amt_Nis)) AS Payment_Amt_PREV
    , CASE 
      WHEN Payment_Amt_DHIA = 0
        AND Payment_Amt_PREV = 0
        THEN 0
      ELSE Abs(CASE 
            WHEN FULL_DHIA = 1
              OR (
                Payment_Amt_PREV = 0
                AND Payment_Amt_DHIA <> 0
                )
              THEN 100
            ELSE (Payment_Amt_DHIA / Payment_Amt_PREV - 1) * 100
            END)
      END AS reduce_rate 
    FROM VBM5732_AGR_DELAYED_PAYMENTS AS DHIA_TASH INNER JOIN VBM374_USED_BRANCH_CUSTOMER AS VBM374
    ON DHIA_TASH.Prev_Branch_Cust_IP = VBM374.Branch_Cust_IP 
    
    LEFT JOIN V01X997_IP_REGULATORY_ACTIVITY_SEGMENT AS V01X997
    ON (
        VBM374.Branch_Cust_IP = V01X997.Inv_Party_Id
        OR VBM374.Current_Branch_Cust_IP = V01X997.Inv_Party_Id
        )
      AND V01X997.Validity_Date = (
        SELECT Max(Validity_Date)
        FROM V01X997_IP_REGULATORY_ACTIVITY_SEGMENT
        )
      AND V01X997.Customer_Segment IN (
        1000
        , 2000
        , 3110
        , 3120
        , 3200
        , 3300
        ) 
        
    INNER JOIN VBM442_AGREEMENTS AS VBM442
    ON (
        VBM442.Branch_Cust_IP = VBM374.Branch_Cust_IP
        AND DHIA_TASH.Prev_Agr_Id = VBM442.Agreement_Account_Id
        ) 
        
    LEFT JOIN V00X613_EXCHANGE_RATE AS V00X613
    ON V00X613.Exchange_Rate_Date = (
        SELECT Max(PROCESS_DATE)
        FROM V797_PROCESS_DATE
        )
      AND AS52_MATBEA_ISKA = V00X613.Currency_Id 
      
    LEFT JOIN VBM451_ACT_LOAN_AGR_SYMBOL
    ON (VBM451_ACT_LOAN_AGR_SYMBOL.Agreement_Account_Id = VBM442.Agreement_Account_Id) 
    
    LEFT JOIN VBM452_ACT_LOAN_AGR_TERM
    ON (VBM452_ACT_LOAN_AGR_TERM.Agreement_Account_Id = VBM442.Agreement_Account_Id) 
    
    INNER JOIN VBM321_PRODUCTS
    ON (VBM321_PRODUCTS.Product_Id = VBM442.Product_Id) 
    
    LEFT JOIN V02X17_PRODUCT_CREDITS
    ON (
        V02X17_PRODUCT_CREDITS.Product_Id = VBM321_PRODUCTS.Product_Id
        AND VBM321_PRODUCTS.Subject_Code = 'AS'
        ) 
        
    LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS VBM410
    ON VBM410.Agreement_Account_Id = DHIA_TASH.Curr_Agr_Id
      AND VBM410.Agreement_Summary_Date = (
        SEL END_DATE FROM VT5727_DATES_M
        ) 
        
    INNER JOIN VBM442_AGREEMENTS AS VBM442_CURR
    ON (
        VBM442_CURR.Branch_Cust_IP = VBM374.Current_Branch_Cust_IP
        AND DHIA_TASH.Curr_Agr_Id = VBM442_CURR.Agreement_Account_Id
        ) 
        
    LEFT JOIN VBM448_ACT_LOAN_AGR_RATE
    ON VBM448_ACT_LOAN_AGR_RATE.Agreement_Account_Id = VBM442_CURR.Agreement_Account_Id 
    
    LEFT JOIN (
      SELECT LOAN_BAL_PREV.Agreement_Account_Id
        , Max(LOAN_BAL_PREV.Agreement_Summary_Date) AS max_date
      FROM VBM5732_AGR_DELAYED_PAYMENTS AS DHIA_TASH
      
      INNER JOIN VBM442_AGREEMENTS AS VBM442
      ON (DHIA_TASH.Prev_Agr_Id = VBM442.Agreement_Account_Id)
      
      LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_PREV
        ON (VBM442.Agreement_Account_Id = LOAN_BAL_PREV.Agreement_Account_Id)
        
      WHERE LOAN_BAL_PREV.Agreement_Summary_Date < DHIA_TASH.AS52_TAR_DHIA
        AND DHIA_TASH.Validity_Date = (SELECT Max(Validity_Date) FROM VBM5732_AGR_DELAYED_PAYMENTS)
        AND AS52_TAR_DHIA BETWEEN 1231001
        AND (
            SEL END_DATE FROM VT5727_DATES_M
            )
        AND (
              (
                AS52_SHIUCH_CHAT NOT IN (
                  833
                  , 835
                  )
                AND AS52_MIS_CHODASHIM BETWEEN 2
                  AND 24
                )
              OR (
                AS52_SHIUCH_CHAT IN (
                  833
                  , 835
                  )
                AND AS52_MIS_CHODASHIM > 0
                )
              )
        AND AS52_TAR_KEREN_AFT - AS52_TAR_KEREN_KDM > 0
        
      GROUP BY 1
    ) AS LOAN_max_date
    ON (VBM442.Agreement_Account_Id = LOAN_max_date.Agreement_Account_Id) 
    
    LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_PREV
    ON (
        LOAN_max_date.Agreement_Account_Id = LOAN_BAL_PREV.Agreement_Account_Id
        AND LOAN_BAL_PREV.Agreement_Summary_Date = LOAN_max_date.max_date
        ) 
        
    LEFT JOIN VA7836_HARAVOT_BARZEL_POP AS VA7836
    ON VA7836.Branch_Cust_IP = VBM374.Branch_Cust_IP 
    
    LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_DHIA
    ON (
        VBM442.Agreement_Account_Id = LOAN_BAL_DHIA.Agreement_Account_Id
        AND LOAN_BAL_DHIA.Agreement_Summary_Date = DHIA_TASH.AS52_TAR_DHIA
        ) 
      WHERE AS52_TAR_DHIA BETWEEN 1231001
      AND (
          SEL END_DATE FROM VT5727_DATES_M
          )
      AND DHIA_TASH.Validity_Date = (
        SELECT Max(Validity_Date)
        FROM VBM5732_AGR_DELAYED_PAYMENTS
        )
      AND (
        (
          AS52_SHIUCH_CHAT NOT IN (
            833
            , 835
            )
          AND AS52_MIS_CHODASHIM BETWEEN 2
            AND 24
          )
        OR (
          AS52_SHIUCH_CHAT IN (
            833
            , 835
            )
          AND AS52_MIS_CHODASHIM > 0
          )
        )
      AND AS52_TAR_KEREN_AFT - AS52_TAR_KEREN_KDM > 0
      AND VBM442.Agreement_Category_Code = 3
      AND VBM374.Status_Code <> 'T295V51'
      
    GROUP BY 1
      , 2
      , 3
      , 4
      , 5
      , 6
      , 7
      , 8
      , 9
      , 10
      , 11
      , 12
      , 13
      , 14
      , 15
      , 16
      , 17
      , 18
      , 19
      , 20
      , 21
      , 22
      , 23
      , 24
      , 25
      , 26
      , 27
      , 28
      , 29
      , 30
  )
  WITH DATA PRIMARY INDEX (
      Current_Branch_Cust_IP
      , Branch_Cust_IP
      , Agreement_Account_Id
      , AS52_TAR_DHIA
      ) ON

COMMIT PRESERVE ROWS;

[Tom]

Talk to your Teradata DBA for optimization of Teradata code.  Hint run EXPLAIN on the code to see how Teradata plans to attack the problem.  Note that with Teradata the key thing is making sure you have set the primary index properly so that the data spread evenly across the compute nodes so you can take full advantage of the multiprocessing.

To work with temporary ("volatile") tables use a SINGLE connection to Teradata.

That is first create a LIBREF and then use the LIBREF instead of making new connections for every query.

libname td teradata ..... ;
proc sql;
connect using td ;
execute by td ( some SQL );
create table work.xx as select * from connection to td
(some SQL)
;
quit;
...
proc sql;
connect using td;
...