Simplify proc sql multiple joins

yashpande — Sat, 20 Jul 2024 16:56:59 GMT

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