Simplify proc sql multiple joins

[yashpande]

 Can somebody help me to break this one big proc sql into multiple proc sql as we need to convert it into SAS DI and I am somehow stuck and not getting the correct result because of the subquery

Any help is really appreciated. Thanks in advance

PROC SQL  /* FULL QUERY in SAS LIB  agreement snapshot*/ ;

CREATE TABLE im_rslts.CA_pr_betalen_basistabel_dly AS 

SELECT distinct

       (datepart(ARS.D0_CRD_RPT_DT)) AS periode_eenh_id   FORMAT=DATE9.

,  put(intnx('month', datepart(ARS.D0_CRD_RPT_DT), -1), yymmn6.) AS relatiecomplex

,  ARS.D0_CRD_RPT_DT AS boekdag

,  ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT AS saldo label="DEP_CLS_BOOK_BAL_AMT without D/C conversion (-1)"

,  substr(ARD.NK_AR_NBR, 9, 10) AS rekening_nr

,  ARD.HA_PD_CD AS product_type_cd

,  (CASE

WHEN REL.relation_type="O" THEN "CLP"

WHEN REL.relation_type="P" THEN "RGB"

END) AS reftyp

,  REL.relation_nr AS relnr

,  CATX(' ', REL.person_name, REL.prefix_name, REL.initials) AS relnaam

,  REL.segment_cd AS segment_cd  

,  REL.main_segment_group_cd AS main_segment_group_cd

,  REL.grid_relation_nr AS grid_eenh_id

,  REL.grid_segment_cdAS grid_cust_segm_code

,  REL.gridsegm_eenh_id AS gridsegm_eenh_id

,  REL.segm_eenh_id AS segm_eenh_id

,  input(substr(REL.managing_entity,1,5),5.) AS kantrnr

,  input(substr(REL.managing_entity,6,3),3.) AS relbehcd

,  REL.econ_ultimate_grid_relation_nr AS grid_econ_ult_eenh_id

,  ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT AS startsaldo LABEL="saldo van vorige dag"

,  ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT AS eindsaldo LABEL="saldo van huidige dag"

,  (ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT-COALESCE(ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT,0)) AS saldo_mutatie

,  

(CASE

WHEN ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT NOT IN (. , 0) 

THEN (ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT - ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT) / ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT * 100

WHEN ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT IN (. , 0)     

THEN 100 * SIGN(ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT)

END)  AS percentage_saldo_wijziging

,  S1.relatie_cd AS relatie_cd

,  . AS afschrijving LABEL="TXN NOT PROMOTED"

,  . AS bijschrijving LABEL="TXN NOT PROMOTED"

,  . AS aanvullen LABEL="TXN NOT PROMOTED"

,  . AS afromen LABEL="TXN NOT PROMOTED"

,  ARD.HA_AHRD_OD_LMT_TP_CD AS limit_type_code

,  ARS.FM_AHRD_OD_LMT_AMT AS authorized_overdraft_limit

,  S2.limit_type_description AS limit_type_description

,  (CASE

WHEN input(RA.bban_nr,10.) IN (SELECT NRC.rekening_nr FROM dsa_p_nl.SAV_V_NRC_REKENING_20240401 NRC) THEN 'J' ELSE 'N'

END) AS nrc_rekening_ind LABEL="sav_v_nrc"

,  (ARS.FM_DEP_CLS_BOOK_BAL_AMT)*(ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND ="D") AS saldo_debet

,  (ARS.FM_DEP_CLS_BOOK_BAL_AMT)*(ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND ="C") AS saldo_credit

,  REL.affiliate_entity_type AS affiliate_entity_type

,  REL.ma_segment_descr AS ma_segment_oms

,  (CASE 

WHEN REL.ma_segment_descr =''                                THEN 'Onbekend'

WHEN REL.ma_segment_descr ='Financial Institutions'          THEN 'FI'

WHEN REL.ma_segment_descr ='Midsized Corporates'             THEN 'MC'

WHEN REL.ma_segment_descr ='Particulier Personal Banking'    THEN 'Perba'

WHEN REL.ma_segment_descr ='Mass: Personal Banking'          THEN 'Perba'

WHEN REL.ma_segment_descr ='Particulieren'                   THEN 'Mass'

WHEN REL.ma_segment_descr ='Mass'                            THEN 'Mass'

WHEN REL.ma_segment_descr ='Private Banking'                 THEN 'Priba'

WHEN REL.ma_segment_descr ='Product Clients'                 THEN 'PC'

WHEN REL.ma_segment_descr ='Retail - Other'                  THEN 'RO' 

WHEN REL.ma_segment_descr ='Retail Companies'                THEN 'SME' 

WHEN REL.ma_segment_descr ='SME'                             THEN 'SME' 

WHEN REL.ma_segment_descr ='Self-employed/Micro'             THEN 'SEM' 

WHEN REL.ma_segment_descr ='Wholesale - Other'               THEN 'WO' 

WHEN REL.ma_segment_descr ='Wholesale Corporates'            THEN 'CC' 

ELSE ma_segment_descr 

END) AS ma_segment_naam

,  (ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND = "C") AS aantal_credit

,  (ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND = "D") AS aantal_debet

,  (ARS.FM_AHRD_OD_LMT_AMT > 0)*(ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81')) AS aantal_limit

,  (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))

THEN MIN(ARS.FM_AHRD_OD_LMT_AMT, ((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT))

ELSE 0

END) AS saldo_debet_in_limit

,  (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))

THEN  MAX(0, (((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT)-ARS.FM_AHRD_OD_LMT_AMT)) 

ELSE (ARS.FM_DEP_CLS_BOOK_BAL_AMT)*(ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND ="D")

END) AS saldo_debet_out_limit

, (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))

THEN MIN(ARS.FM_AHRD_OD_LMT_AMT, ((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT))>0 

ELSE 0

END) AS aantal_debet_in_limit

, (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81')) 

THEN MAX(0, (((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT)-ARS.FM_AHRD_OD_LMT_AMT))>0

ELSE 1

END) AS aantal_debet_out_limit

, (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))

THEN ARS.FM_AHRD_OD_LMT_AMT-MIN(ARS.FM_AHRD_OD_LMT_AMT, ((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT))

ELSE ARS.FM_AHRD_OD_LMT_AMT

END) AS dispositie_ruimte

FROM cl_exg.cl_exg_fp_ar_snpst_dly                    ARS

   INNER JOIN cl_exg.cl_exg_d7_ar                    ARD      ON ARS.d7_ar_id    = ARD.s7_ar_id

   LEFT JOIN (SELECT distinct ARS1.*, ARD1.s1_ar_id

FROM cl_exg.cl_exg_fp_ar_snpst_dly ARS1 

INNER JOIN cl_exg.cl_exg_d7_ar     ARD1      ON (ARS1.d7_ar_id    = ARD1.s7_ar_id) 

WHERE ((ARS1.d0_crd_rpt_dt = '31MAR2024:00:00:00'dt) AND (ARS1.d0_crd_rpt_dt between ARD1.TA_VLD_FROM_DT and ARD1.TA_VLD_TO_DT))

) ARS1 ON (ARD.s1_AR_ID = ARS1.s1_AR_ID)

LEFT JOIN dsa22.RELATION_AGREEMENT_202403        RA       ON ((ARD.NK_AR_NBR = RA.iban_nr) 

                                                                    AND (RA.RELATION_NR = RA.main_relation_nr) AND (RA.mdm_product_category_cd=1))

LEFT JOIN dsa22.RELATION_202403                  REL      ON (RA.relation_nr = REL.relation_nr)

LEFT JOIN dsa_p_nl.stuur_pay_relatie_cd_202404  S1      ON  (input(RA.bban_nr,10.) = S1.rekening_nr)

LEFT JOIN dsa_p_nl.STUUR_LIMIT_TYPE_CODE_202404  S2       ON ARD.HA_AHRD_OD_LMT_TP_CD = s2.limit_type_code

WHERE  ARS.d0_crd_rpt_dt = '01APR2024:00:00:00'dt

AND   ARD.HA_PD_GRP_CD = 'CRN_AC'

AND   ARD.HA_PD_CD NOT IN ('4910', '4911', '4300') /*EXCLUDE FCA*/

/*AND   ARS.D7_AR_ID = 1030000021058960*/

/*GROUP BY TXS.D0_CRD_RPT_DT, TXS.D7_AR_ID, TXS.D7_PD_ID, TXS.D7_TXN_CCY_ID*/

;

QUIT;

[Tom]

Why do you need to change it?  Does it not work as it is? 

What did you try? In what way did it not work?

How do you want to change it? Do you mean you want to do the combination in multiple steps?

I would start by taking the subquery and building that as a data step or view.

(
SELECT distinct ARS1.*, ARD1.s1_ar_id
FROM cl_exg.cl_exg_fp_ar_snpst_dly ARS1 
  INNER JOIN cl_exg.cl_exg_d7_ar ARD1
    ON  (ARS1.d7_ar_id    = ARD1.s7_ar_id) 
WHERE ((ARS1.d0_crd_rpt_dt = '31MAR2024:00:00:00'dt)
   AND (ARS1.d0_crd_rpt_dt between ARD1.TA_VLD_FROM_DT and ARD1.TA_VLD_TO_DT)
      )
) ARS1 

And then replacing that subquery in the larger query with the name of the view or dataset that you created.

[yashpande]

I need to build a SAS DI job using transformation for that . When I am combining all the tables in one SQL somehow the results are not matching and hence I need to make separate proc SQL for the same

[Tom]

---

@yashpande wrote:
I need to build a SAS DI job using transformation for that . When I am combining all the tables in one SQL somehow the results are not matching and hence I need to make separate proc SQL for the same

---

Not sure what DI allows.  But if the query works outside of DI but does not work in DI then the issue is that the data is different.

Why do you need to rebuild the logic in DI?  To do that you should probably start with the business description of what you want to do and build that using DI features. Rather than trying to reverse engineer someone else's solution to the problem created using only SQL.

[Oligolas]

Hi,

If this is wanted, I'd suggest to perform the left joins in SQL and the result modifications in a dataset.

Avoid all the cases and catx in the SQL (allthough it's fine to do it like this)

That's not too much work and it will clarify your code 

And align your code with this little tool https://sqlformat.org/ 

[Tom]

---

@Oligolas wrote:

...

And align your code with this little tool https://sqlformat.org/ 

---

Unfortunately that formatting tool hides the continuation characters (commas for example) in split lines at the END of previous line instead of placing them at the BEGINNING of the next line where a human is more likely to SEE them.  it does not seem to include any option to fix that.