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;
... View more
