This code consists of two SQL procedures written in SAS,...

September 3, 2025 at 11:50 PM

proc sql; create table recov_dup_import as select a.SNAP_DT, a.COMMENTS_1,a.Comments_2,a.comments_3,a.DT_OPEN, a.ISSUE_DT,a.AR_ID,a.CLNT_NO,a.gu_name,a.RESP_UNT,a.ACCT_MGR,a.OLBB_Booking_Transit, a.OLBB_account,a.OLBB_seg,a.BORR_BSC,a.BORR_CMI,a.SHRT_NM, a.LLPS_LN_DESC,a.product_id,a.PRODUCT_NAME,a.LN_PLAN_DTL_CD,a.LN_NOTE_CD,a.CUST_ID, a.CR_PLN_TYP,a.LN_SUB_TP_CD,a.LN_PURPOSE_CD,a.CURR_OF_OS, a.RECOV_DATE, a.TOTAL_RECOV, b.LST_WRTOF_RCVRY_DT as PREV_RECOV_DATE, /*replace with lookup value*/ b.LIFETIME_WO_RCVRY_AMT as PREV_TOTAL_RECOV, /*replace with lookup value*/ a.TOTAL_RECOV - b.LIFETIME_WO_RCVRY_AMT as mtd_recov, /*replace with lookup value*/ a.TOTAL_RECOV - b.LIFETIME_WO_RCVRY_AMT as UNCONVERTED_MTD_RECOV /*replace with lookup value*/ from temp.recov_dup_exp a left join DDWV01.LOAN_INFO_TACT b on a.clnt_no = input(put(b.ip_id,9.),9.) and substr(a.ar_id,10,12) = substr(b.AR_ID,10,12) and b.SNAP_DT = &lmthend. ; quit; /******************************************************************************/ /*all the non-duplicates from recovery final table*/ /*if there are duplicates, please run the below steps*/ proc sql; create table recov_non_dup_1 as select a.* from Edw_mtd_loan_recov_12 a left join recov_dup_import b on a.olbb_account = b.olbb_account and a.olbb_seg = b.olbb_seg where a.olbb_account <> b.olbb_account and a.olbb_seg <> b.olbb_seg; run;

This code consists of two SQL procedures written in SAS, and here's an explanation of what each block does:

First SQL Block: Creating recov_dup_import Table

  1. Purpose of Query:

    • This SQL block creates a new table, recov_dup_import, by joining data from two tables: temp.recov_dup_exp (a) and DDWV01.LOAN_INFO_TACT (b).
    • It aims to calculate recovery-related metrics (PREV_RECOV_DATE, PREV_TOTAL_RECOV, mtd_recov, UNCONVERTED_MTD_RECOV) by incorporating additional fields from b into a.

  2. Details of the Query:

    • Columns from table a are selected, including recovery details (e.g., TOTAL_RECOV, RECOV_DATE), account details (olbb_account, olbb_seg), and other data.
    • Fields from table b (LST_WRTOF_RCVRY_DT and LIFETIME_WO_RCVRY_AMT) are used as lookup values:
      • PREV_RECOV_DATE: Set to LST_WRTOF_RCVRY_DT, representing the previous recovery date.
      • PREV_TOTAL_RECOV: Set to LIFETIME_WO_RCVRY_AMT, representing the total recovery amount so far.
      • mtd_recov: Calculated as the difference between current recovery amount (TOTAL_RECOV) and lifetime recovery (LIFETIME_WO_RCVRY_AMT), i.e., month-to-date recovery.
      • UNCONVERTED_MTD_RECOV: Same as mtd_recov.

  3. Join Condition:

    • This is a LEFT JOIN between:
      • a.clnt_no and b.ip_id (which is converted to numeric using input and put).
      • A substring comparison of ar_id fields in a and b (substr(ar_id,10,12)).
      • Matching SNAP_DT from b with the macro variable &lmthend. (representing some specific snapshot date).

Second SQL Block: Creating recov_non_dup_1 Table

  1. Purpose of Query:

    • This block filters out non-duplicate records from Edw_mtd_loan_recov_12 table by comparing it with the recov_dup_import table generated previously.

  2. Details of the Query:

    • The query selects all columns (a.*) from the table Edw_mtd_loan_recov_12 (a), excluding rows that have the same olbb_account and olbb_seg values as those in recov_dup_import (b).
    • The LEFT JOIN identifies matching rows based on olbb_account and olbb_seg.
    • The WHERE condition ensures that only non-matching (<>) rows from a are included in the output.

  3. Result:

    • The resulting table recov_non_dup_1 contains recovery data without duplicates (based on the olbb_account and olbb_seg combination).

Overall Functionality

  • The first block creates a table combining recovery-related metrics and historical recovery data from two sources, performing calculations for metrics like month-to-date recovery (mtd_recov).
  • The second block filters non-duplicate recovery records from a separate table (Edw_mtd_loan_recov_12) by ensuring no overlap with the previously created recov_dup_import table based on specified columns (olbb_account, olbb_seg).

The code integrates, processes, and de-duplicates recovery data for further analysis or reporting.

Generate your own explanations
Download our vscode extension
Read other generated explanations

Built by @thebuilderjr
Sponsored by beam analytics
Read our terms and privacy policy
Forked from openai-quickstart-node