@Tom
Since the macro variable doesn't exist,
am still struggling to identify where the problem is ,in my code can you assist.
below is the code used.
PROC SQL;
CREATE TABLE WRK.TO_COMM_AC (ENCODING=ANY) AS
SELECT A.PRODUCT_GUID
,A.ZZ0011
,A.ZZ0012
,A.ZZ0014
,A.ZZ0015
,A.SYST_IND
,A.VALID_FROM
,A.VALID_TO
,B.ILGUID
,B.SOURCEGUID
,B.DESTINGUID
FROM SRC.CAP_BRACC_YG (ENCODING=ANY) A
LEFT JOIN SRC.COMM_IL_PRDBP (ENCODING=ANY) B ON A.PRODUCT_GUID = B.SOURCEGUID
WHERE LINK_PARTNER_FCT = 'Z0000093' AND LINK_VALID_TO = 99991231235959 AND SYST_IND = 'SAP'
;
QUIT;
/*JOIN TO AUSP TABLE TO GET MARKET SEGMENT*/
PROC SQL;
CREATE TABLE WRK.TO_AUSP_AC (ENCODING=ANY) AS
SELECT A.*
,B.ATWRT
,B.PARTNER_GUID
FROM WRK.TO_COMM_AC (ENCODING=ANY) A
LEFT JOIN SRC.AUSP (ENCODING=ANY) B
ON A.DESTINGUID = B.PARTNER_GUID
;
QUIT;
/*REMOVE THOSE THAT DO NOT MATCH - SHOULD BE INVESTIGATED*/
data WRK.BA_MKT_MATCH_AC (ENCODING=ANY)
WORK.NO_MATCH_AC (ENCODING=ANY);
SET WRK.TO_AUSP_AC (ENCODING=ANY);
IF ATWRT <> '' THEN
OUTPUT WRK.BA_MKT_MATCH_AC;
ELSE OUTPUT WORK.NO_MATCH_AC;
RUN;
/*JOIN TO BUT000 TO GET CUSTOMER INFORMATION*/
PROC SQL;
CREATE TABLE WRK.TO_BUT000_AC (ENCODING=ANY) AS
SELECT A.*
,B.PARTNER
,B.TYPE
,B.BPKIND
,B.BU_GROUP
,B.ZZCONTROLLIN
,B.ZZACCOUNTEXE
,B.NAME_ORG1
,B.NAME_ORG2
,B.NAME_ORG3
,B.NAME_ORG4
,B.LEGAL_ENTY
,B.IND_SECTOR
,B.LEGAL_ORG
,B.VALID_FROM
,B.VALID_TO
,B.LEGAL_ENTY
FROM WRK.BA_MKT_MATCH_AC (ENCODING=ANY) A
LEFT JOIN SRC.BUT000 (ENCODING=ANY) B
ON A.DESTINGUID = B.PARTNER_GUID
;
QUIT;
/*REMOVE ANY MISMATCHES*/
data WRK.BA_BUT_MATCH_AC (ENCODING=ANY)
NO_MATCH_AC (ENCODING=ANY);
SET WRK.TO_BUT000_AC (ENCODING=ANY);
IF PARTNER <> '' THEN
OUTPUT WRK.BA_BUT_MATCH_AC;
ELSE OUTPUT NO_MATCH_AC;
RUN;
/*BRING IN ID - FILTER FOR WHERE THERE ARE 2 INSTANCES OF AN ID PER BPID*/
PROC SORT DATA= SRC.BUT0ID (ENCODING=ANY) OUT= WRK.BUTID_SORT_AC (ENCODING=ANY);
BY PARTNER IDNUMBER VALID_DATE_TO;
RUN;
DATA WRK.BUTID_FILTER (ENCODING=ANY);
SET WRK.BUTID_SORT_AC (ENCODING=ANY);
BY PARTNER IDNUMBER VALID_DATE_TO;
IF FIRST.PARTNER THEN
COUNT = 1;
ELSE COUNT+1;
RUN;
PROC SQL;
CREATE TABLE WRK.TO_BUTID_AC (ENCODING=ANY) AS
SELECT A.*
,B.TYPE AS ID_TYPE
,B.IDNUMBER
,B.VALID_DATE_TO
FROM WRK.BA_BUT_MATCH_AC (ENCODING=ANY) AS A
LEFT JOIN (SELECT * FROM WRK.BUTID_FILTER (ENCODING=ANY) WHERE COUNT = 1) AS B ON A.PARTNER = B.PARTNER
;
QUIT;
/*BRING IN KYC INFO*/
PROC SQL;
CREATE TABLE WRK.TO_BKYC_AC (ENCODING=ANY) AS
SELECT A.*
,B.YYKYC_ROLE
,B.YYKYC_STATUS
,B.PARENT_ID
FROM WRK.TO_BUTID_AC (ENCODING=ANY) AS A
LEFT JOIN (SELECT DISTINCT PARENT_ID,YYKYC_ROLE,YYKYC_STATUS FROM SRC.YKYCM_KYC_STATUS (ENCODING=ANY)
WHERE YYKYC_ROLE = "CUSTOMER"
GROUP BY PARENT_ID,YYKYC_ROLE,YYKYC_STATUS
HAVING YYKYC_EXEC_DATE = MAX(YYKYC_EXEC_DATE)) AS B
ON A.DESTINGUID = B.PARENT_ID
;
QUIT;
/*CREATE WEALTH AND INVESTMENTS - IF EXISTS IN WI_ALL_BA_SAP_ACCOUNTS THEN WEALTH AND INVESTMENTS*/
/*PROC SQL;*/
/* CREATE TABLE WRK.BA_WI_AC (ENCODING=ANY) AS*/
/* SELECT 'W&I' AS Portfolio,'Wealth' AS Business_Area,A.**/
/* FROM WRK.TO_BKYC_AC (ENCODING=ANY) A*/
/**/
/* INNER JOIN ( SELECT * FROM BRACC.WI_ALL_BA_SAP_ACCOUNTS (ENCODING=ANY)*/
/* WHERE Source= 'SAP' ) B*/
/* ON INPUT(PUT(A.ZZ0011,$16.),16.) = INPUT(PUT(COMPRESS(INPUT(PUT(B.ACC_NUM,13.),$13.)||B.SERIAL_NUM),$16.),16.)*/
/* ON INPUT(PUT(ZZ0011,$16.),16.) = INPUT(PUT(COMPRESS(INPUT(PUT(ACC_NUM,13.),$13.)||PUT(SERIAL_NUM,Z3.)),$16.),16.)*/
/* ;*/
/* */
/*QUIT;*/
PROC SQL;
CREATE TABLE WRK.BA_WI_AC (ENCODING=ANY) AS
SELECT 'W&I' AS Portfolio
,'Wealth' AS Business_Area
,A.*
FROM WRK.TO_BKYC_AC (ENCODING=ANY) AS A
INNER JOIN (SELECT * FROM BRACC.WI_ALL_BA_SAP_ACCOUNTS (ENCODING=ANY)
WHERE ACNT_SOURC_SYSTM_N= 110) AS B ON INPUT(PUT(A.ZZ0011,$16.),16.) = B.Acc_Num
;
QUIT;
/**/
PROC SQL;
CREATE TABLE OTHER_AC (ENCODING=ANY) AS
SELECT *
FROM WRK.TO_BKYC_AC (ENCODING=ANY)
WHERE INPUT(PUT(ZZ0011,$16.),16.) NOT IN
(SELECT DISTINCT CUST_ACNT_N FROM BRACC.WI_ALL_BA_SAP_ACCOUNTS)
;
QUIT;
PROC SQL;
CREATE TABLE BA_OTHER_AC (ENCODING=ANY) AS
SELECT B.VBM_Grouping AS Business_Area
, A.*
FROM OTHER_AC (ENCODING=ANY) AS A
LEFT JOIN LOOKUP.MarketSegment AS B ON A.ATWRT = B.MKT_SEG
;
QUIT;
DATA BA_OTHER_01_AC (ENCODING=ANY);
LENGTH Portfolio $50.;
SET WORK.BA_OTHER_AC (ENCODING=ANY);
IF ATWRT IN ('701','702','703','704') OR TYPE = 2 THEN
Portfolio = 'CIB';
ELSE IF ATWRT IN ('412','532','533','553','554') THEN
Portfolio = 'Commercial Banking';
ELSE IF ATWRT IN ('410','411','550','551','552','421','422','418','428','429','530','531','534') THEN
Portfolio = 'Business Banking';
ELSE IF ATWRT IN ('400','401','419','420') THEN
Portfolio = 'Small Enterprise';
ELSE IF ATWRT IN ('140','146','240','142','144') THEN
Portfolio = 'Private Banking';
ELSE IF ATWRT = '136' THEN
Portfolio = 'Prestige';
ELSE IF ATWRT IN ('115','110','120','210','220','126') THEN
Portfolio = 'Inclusive Banking';
ELSE IF ATWRT IN ('130','135') THEN
Portfolio = 'Middle Markets';
ELSE IF ATWRT = '230' THEN
Portfolio = 'Business Affinity';
ELSE IF ATWRT = '988' THEN
Portfolio = 'Staff';
ELSE Portfolio = 'Other';
RUN;
PROC SQL;
CREATE TABLE WRK.BA_CRM_01_AC (ENCODING=ANY) AS
SELECT *
FROM WORK.BA_OTHER_01_AC (ENCODING=ANY)
UNION
SELECT *
FROM WRK.BA_WI_AC (ENCODING=ANY)
;
QUIT;
PROC SQL;
CREATE TABLE WRK.BA_CRM_AC (ENCODING=ANY) AS
SELECT B.Segment_Desc AS BP_MKT_SEG_DESC
,A.*
,C.Description AS LEGAL_ENTITY_DESC
FROM WRK.BA_CRM_01_AC (ENCODING=ANY) AS A
LEFT JOIN LOOKUP.MarketSegment AS B ON A.ATWRT = B.MKT_SEG
LEFT JOIN LOOKUP.Legal_Entity_Desc AS C ON A.LEGAL_ENTY = C.Legal_Form
;
QUIT;
/* EXTRACT ALL THE OPEN ACCOUNTS BASED ON RULES (STAT AND INACT FIELDS)*/
PROC SQL;
CREATE TABLE WRK.OPEN_ACC_AC AS
SELECT A.*
,CASE WHEN B.STAT = 'E0005' THEN 'Closed'
WHEN B.STAT = 'E0004' THEN 'Open'
ELSE 'NA' END AS OPENORCLOSED
,B.STAT
,B.INACT
FROM WRK.BA_CRM_AC (ENCODING=ANY) A
LEFT JOIN SRC.CRM_JEST (ENCODING=ANY) B ON A.PRODUCT_GUID = B.OBJNR
WHERE STAT IN ('E0004','E0005') AND INACT <> 'X'
;
QUIT;
PROC SQL;
CREATE TABLE WRK.TO_COMMPRO_AC (ENCODING=ANY) AS
SELECT A.*, B.SOURCEGUID AS PRO_SOURCEGUID, B.DESTINGUID AS PRO_DESTINGUID
FROM WRK.OPEN_ACC_AC (ENCODING=ANY) A
LEFT JOIN SRC.COMM_IL_PROREF (ENCODING=ANY) B
ON A.PRODUCT_GUID = B.SOURCEGUID
;
QUIT;
PROC SQL;
CREATE TABLE WRK.BA_PRODUCT_AC (ENCODING=ANY) AS
SELECT *
FROM WRK.TO_COMMPRO_AC (ENCODING=ANY) A
LEFT JOIN SRC.COMM_PRODUCT (ENCODING=ANY) B
ON A.PRO_DESTINGUID = B.PRODUCT_GUID
;
QUIT;
PROC SQL;
CREATE TABLE WRK.TO_PROD_TEXT_AC (ENCODING=ANY) AS
SELECT *
FROM WRK.BA_PRODUCT_AC (ENCODING=ANY) A
LEFT JOIN SRC.COMM_PRSHTEXT (ENCODING=ANY) B ON A.PRO_DESTINGUID = B.PRODUCT_GUID
;
QUIT;
/*EXTRACT OPEN AND CLOSED DATES FROM CRM7*/
/*PROC SQL;*/
/* CREATE TABLE WRK.TO_CRM7 AS*/
/* SELECT DISTINCT A.* */
/* , INPUT(PUT(SUBSTR(B.ACNT_OPEN_DATE,2,8),$8.),YYYYMMDD8.) AS OPEN_DATE*/
/* , INPUT(PUT(SUBSTR(B.ACNT_CLOSE_DATE,2,8),$8.),YYYYMMDD8.) AS CLOSED_DATE*/
/**/
/* FROM WRK.TO_PROD_TEXT_AC A*/
/**/
/* LEFT JOIN SRC.CRM7_ACCOUNT B ON INPUT(PUT(A.ZZ0011,$16.),16.) = B.ACNT_N*/
/* ;*/
/*QUIT;*/
/*GET BALANCE DATA FROM TERADATA*/
PROC SQL;
CONNECT TO TERADATA(USER="SA000846@LDAP" PASSWORD="b]gw0qfdhFf" SERVER=SBSA4 MODE = TERADATA CONNECTION=GLOBAL);
EXECUTE
(
CREATE MULTISET VOLATILE TABLE BALANCES, NO LOG AS
(
SELECT *
FROM PRODEIW_BI.BI_ACCOUNT_DAILY_BALANCE
WHERE SB_Balance_Dt = '2020-10-26'
AND ACCOUNT_MODIFIER_NUMBER = 110
)
WITH DATA PRIMARY INDEX (ACCOUNT_NUM)
ON COMMIT PRESERVE ROWS;
) BY TERADATA;
CREATE TABLE WRK.BALANCES
AS
SELECT *
FROM CONNECTION TO TERADATA
(
SELECT *
FROM
BALANCES
);
DISCONNECT FROM TERADATA;
QUIT;
PROC SQL;
CREATE TABLE WRK.TO_BALANCES AS
SELECT A.*
, C.Balance_Amt AS BOOK_BAL
FROM WRK.TO_PROD_TEXT_AC AS A
LEFT JOIN WRK.BALANCES (ENCODING=ANY) AS C ON INPUT(A.ZZ0011,16.) = C.Account_Num
;
QUIT;
PROC SQL;
CREATE TABLE WRK.FINAL_SAP_AC (ENCODING=ANY) AS
SELECT DISTINCT Business_Area
,Portfolio
,SYST_IND
,INPUT(PUT(ZZ0011,$16.),16.) AS NEW_ACC
,'000' AS NEW_SERIAL
,INPUT(ZZ0011,16.) AS ACCOUNT_NUMERIC
,'' AS M_TYPE
,'' AS MISC1
,OPENORCLOSED
,ZZ0012
,ATWRT AS BP_MKT_SEG
,BP_MKT_SEG_DESC
,'' AS MKT_SEG
,PARTNER AS BPID
,ZZACCOUNTEXE AS AE_NO
,TYPE
,'' AS STATUS
,ID_TYPE
,IDNUMBER
,'' AS BA_NAME
,'' AS DORM
,'' AS IBT_NO
,'' AS KYC
,'' AS OD_ACC_TO_BE_DEBITED
,BPKIND
,SHORT_TEXT AS PRODUCT
,SUBSTR(PRODUCT_ID,37,4) AS PRODUCT_CODE
,'' AS STYLE
,'' AS RB_CODE
,'' AS DLA
,INPUT(SUBSTR(COMPRESS(PUT(ZZ0014,14.)),1,8),8.) AS DATE_OPENED
,INPUT(SUBSTR(COMPRESS(PUT(ZZ0015,14.)),1,8),8.) AS DATE_CLOSED
,LEGAL_ENTY AS LEGAL_ENTITY
,LEGAL_ENTITY_DESC
,BOOK_BAL
,LANGU
,CAT(SUBSTR(COMPRESS(PUT(ZZ0014,14.)),1,4),"/",SUBSTR(COMPRESS(PUT(ZZ0014,14.)),5,2),"/",SUBSTR(COMPRESS(PUT(ZZ0014,14.)),7,2)," ",SUBSTR(COMPRESS(PUT(ZZ0014,14.)),9,2),":",SUBSTR(COMPRESS(PUT(ZZ0014,14.)),11,2),":",SUBSTR(COMPRESS(PUT(ZZ0014,14.)),13,2)) AS DATETIME_OPENED
/* ,COMPRESS(Business_Area||ZZ0011||ATWRT||PARTNER||TYPE||BPKIND||SHORT_TEXT||PRODUCT_ID||M_ACC_STYLE)*/
FROM WRK.TO_BALANCES (ENCODING=ANY)
;
QUIT;
/**/
DATA WRK.FINAL_SAP_AC_2 (ENCODING=ANY);
SET WRK.FINAL_SAP_AC (ENCODING=ANY);
IF DATETIME_OPENED = "1010/10/00 00:0:" THEN DATETIME_OPENED = "9999/12/31 00:00:00";
IF PRODUCT IN ('STUDENT ACHIEVER CURRENT ACCOUNT','ACHIEVER - TJEKREKENING VIR STUDENTE') THEN
PROD_DESCRIPTION = 'STUDENT ACHIEVER CURRENT ACCOUNT';
ELSE IF PRODUCT IN ('BUSINESS CURRENT ACCOUNT','BESIGHEIDS TJEKREKENING') THEN
PROD_DESCRIPTION ='BUSINESS CURRENT ACCOUNT';
ELSE IF PRODUCT = 'MYMO CURRENT ACCOUNT' THEN
PROD_DESCRIPTION ='MYMO CURRENT ACCOUNT';
ELSE IF PRODUCT IN ('ELITE CURRENT ACCOUNT','ELITE TJEKREKENING','ELITE PLUS CURRENT ACCOUNT')
THEN
PROD_DESCRIPTION = 'ELITE CURRENT ACCOUNT';
ELSE IF PRODUCT IN ('RETAIL\WHOLESALE CALL DEPOSIT','KLEINHANDEL\GROOTHANDEL-DAG DEPOSITO') THEN
PROD_DESCRIPTION = 'RETAIL\WHOLESALE CALL DEPOSIT';
ELSE IF PRODUCT IN ('PURESAVE ACCOUNT','PURESAVE-REKENING')
THEN
PROD_DESCRIPTION = 'PURESAVE ACCOUNT';
ELSE IF PRODUCT = 'ENTERPRISE AUTOBANK PLUSPLAN' THEN
PROD_DESCRIPTION = 'ENTERPRISE AUTOBANK PLUSPLAN';
ELSE IF PRODUCT ='BUSINESS BANKING START-UP TRANSACTIONAL' THEN
PROD_DESCRIPTION ='BUSINESS BANKING START-UP TRANSACTIONAL';
ELSE IF PRODUCT ='(SUM)1' THEN
PROD_DESCRIPTION ='(SUM)1';
ELSE IF PRODUCT = 'ACCESS ACCOUNT' THEN
PROD_DESCRIPTION = 'ACCESS ACCOUNT';
ELSE IF PRODUCT ='ACCESSSAVE' THEN
PROD_DESCRIPTION ='ACCESSSAVE';
ELSE IF PRODUCT ='ATTORNEYS TRUST ACCOUNT' THEN
PROD_DESCRIPTION ='ATTORNEYS TRUST ACCOUNT';
ELSE IF PRODUCT ='BIZLAUNCH' THEN
PROD_DESCRIPTION ='BIZLAUNCH';
ELSE IF PRODUCT ='BUSINESS ACCESS (BTA LITE)' THEN
PROD_DESCRIPTION ='BUSINESS ACCESS ';
ELSE IF PRODUCT ='BUSINESS TRANSACTIONAL ACCOUNT (BTA)' THEN
PROD_DESCRIPTION ='BUSINESS TRANSACTIONAL ACCOUNT';
ELSE IF PRODUCT ='CHEQUE CLEARING ACCOUNT' THEN
PROD_DESCRIPTION ='CHEQUE CLEARING ACCOUNT';
ELSE IF PRODUCT ='CONSOLIDATOR CURRENT ACCOUNT' THEN
PROD_DESCRIPTION ='CONSOLIDATOR CURRENT ACCOUNT';
ELSE IF PRODUCT ='CONTRACTSAVE' THEN
PROD_DESCRIPTION ='CONTRACTSAVE';
/* ELSE IF */
/* PRODUCT = 'ELITE PLUS CURRENT ACCOUNT' THEN*/
/* PROD_DESCRIPTION ='ELITE PLUS CURRENT ACCOUNT';*/
/* ELSE IF PRODUCT ='ENTERPRISE AUTOBANK PLUSPLAN' THEN*/
/* PROD_DESCRIPTION ='ENTERPRISE AUTOBANK PLUSPLAN';*/
ELSE IF PRODUCT ="SHARI'AH BUSINESS CALL" THEN
PROD_DESCRIPTION ='SHARIAH BUSINESS CALL';
ELSE IF PRODUCT ="SHARI'AH FIXED DEPOSIT" THEN
PROD_DESCRIPTION ='SHARIAH FIXED DEPOSIT';
ELSE IF PRODUCT ='SIGNATURE BANKING' THEN
PROD_DESCRIPTION ='SIGNATURE BANKING';
ELSE IF PRODUCT ='SOCIETY SCHEME' THEN
PROD_DESCRIPTION ='SOCIETY SCHEME';
ELSE IF PRODUCT ='TAX FREE CALL PLUS' THEN
PROD_DESCRIPTION ='TAX FREE CALL PLUS';
ELSE IF PRODUCT ='TAX FREE FIXED DEPOSIT' THEN
PROD_DESCRIPTION ='TAX FREE FIXED DEPOSIT';
ELSE IF PRODUCT ='TERM LOAN' THEN
PROD_DESCRIPTION ='TERM LOAN';
ELSE IF PRODUCT ='UCB CLEARING ACCOUNT' THEN
PROD_DESCRIPTION ='UCB CLEARING ACCOUNT';
ELSE IF PRODUCT ='VALUE ACCOUNT' THEN
PROD_DESCRIPTION ='VALUE ACCOUNT';
ELSE IF PRODUCT ='WEALTH AND INVESTMENT CURRENT ACCOUNT' THEN
PROD_DESCRIPTION ='WEALTH AND INVESTMENT CURRENT ACCOUNT';
ELSE IF PRODUCT ='WHOLESALE FIXED DEPOSIT' THEN
PROD_DESCRIPTION ='WHOLESALE FIXED DEPOSIT';
ELSE IF PRODUCT ='FIXED DEPOSIT' THEN
PROD_DESCRIPTION ='FIXED DEPOSIT';
ELSE IF PRODUCT ='FLEXI ADVANTAGE' THEN
PROD_DESCRIPTION ='FLEXI ADVANTAGE';
ELSE IF PRODUCT ='MARKETLINK' THEN
PROD_DESCRIPTION ='MARKETLINK';
ELSE IF PRODUCT ='MARKETLINK BUSINESS' THEN
PROD_DESCRIPTION ='MARKETLINK BUSINESS';
ELSE IF PRODUCT ='MARKETLINK' THEN
PROD_DESCRIPTION ='MARKETLINK';
ELSE IF PRODUCT ='MONEYMARKET CALL ACCOUNT' THEN
PROD_DESCRIPTION ='MONEYMARKET CALL ACCOUNT';
ELSE IF PRODUCT ='MONEYMARKET SELECT ACCOUNT' THEN
PROD_DESCRIPTION ='MONEYMARKET SELECT ACCOUNT';
ELSE IF PRODUCT ='MYMOBIZ' THEN
PROD_DESCRIPTION ='MYMOBIZ';
ELSE IF PRODUCT ='MZANSI ACCOUNT' THEN
PROD_DESCRIPTION ='MZANSI ACCOUNT';
ELSE IF PRODUCT ='NOTICE DEPOSIT' THEN
PROD_DESCRIPTION ='NOTICE DEPOSIT';
ELSE IF PRODUCT ='PERSONAL LOAN' THEN
PROD_DESCRIPTION ='PERSONAL LOAN';
ELSE IF PRODUCT IN ('PLUSPLAN','4495') THEN
PROD_DESCRIPTION ='PLUSPLAN';
ELSE IF PRODUCT ='PRESTIGE CURRENT ACCOUNT' THEN
PROD_DESCRIPTION ='PRESTIGE CURRENT ACCOUNT';
ELSE IF PRODUCT ='PRESTIGE PLUS SECONDARY CURRENT ACCOUNT' THEN
PROD_DESCRIPTION ='PRESTIGE PLUS SECONDARY CURRENT ACCOUNT';
ELSE IF PRODUCT ='PRIVATE BANKING CURRENT ACCOUNT' THEN
PROD_DESCRIPTION ='PRIVATE BANKING CURRENT ACCOUNT';
ELSE IF PRODUCT ='PROFESSIONAL ACCOUNT' THEN
PROD_DESCRIPTION ='PROFESSIONAL ACCOUNT';
/* ELSE IF PRODUCT ='PURESAVE ACCOUNT' THEN*/
/* PROD_DESCRIPTION ='PURESAVE ACCOUNT';*/
ELSE IF PRODUCT ='PURESAVE BUSINESS' THEN
PROD_DESCRIPTION ='PURESAVE BUSINESS';
ELSE IF PRODUCT ='SBSA TITANIUM CHEQUE CARD' THEN
PROD_DESCRIPTION ='SBSA TITANIUM CHEQUE CARD';
ELSE PROD_DESCRIPTION ='OTHER';
RUN;
PROC SORT DATA=WRK.FINAL_SAP_AC_2 (ENCODING=ANY) DUPOUT=DUPLICATES_SAP (ENCODING=ANY) OUT=WRK.FINAL_SAP_AC_3 (ENCODING=ANY) NODUPKEY;
BY _ALL_;
RUN;
/*JOIN TO ACCOUNT TYPE REFERENCE TABLE - PROVIDING ACCOUNT TYPES*/
PROC SQL;
CREATE TABLE WRK.FINAL_SAP_AC_4 (ENCODING=ANY) AS
SELECT DISTINCT Business_Area
,Portfolio
,SYST_IND
,NEW_ACC
,NEW_SERIAL
,ACCOUNT_NUMERIC
,M_TYPE
,ZZ0012
,AE_NO
,MKT_SEG
,'' AS MKT_SEG_DESC
,BP_MKT_SEG
,BP_MKT_SEG_DESC
,MISC1
,BPID AS BPID_CHAR
,INPUT(BPID,10.) AS BPID
,A.TYPE AS BP_CAT_T
,
CASE
WHEN A.TYPE = '1' THEN '1 - Individual'
WHEN A.TYPE = '2' THEN '2 - Enterprize'
WHEN A.TYPE = '3' THEN '3 - Group' ELSE '' END AS BP_CAT
,STATUS
,ID_TYPE
,IDNUMBER
,BPKIND
,PROD_DESCRIPTION AS PRODUCT
,PRODUCT_CODE
,STYLE
,OpenOrClosed
,DATE_OPENED
,DATE_CLOSED
,B.DESCRIPTION AS ACCOUNT_TYPE
,BA_NAME
,KYC
,DORM
,IBT_NO
,RB_CODE
,DLA
,OD_ACC_TO_BE_DEBITED
,DESCRIPTION
,LEGAL_ENTITY
,LEGAL_ENTITY_DESC
,DATETIME_OPENED
,LANGU
,BOOK_BAL
FROM WRK.FINAL_SAP_AC_3 (ENCODING=ANY) A
LEFT JOIN DTA.ACC_TYPE_SAP (ENCODING=ANY) B ON A.ZZ0012 = B.TYPE
;
QUIT;
PROC SQL;
CREATE TABLE WRK.FINAL_SAP_DUP_01 AS
SELECT DISTINCT
ACCOUNT_NUMERIC
,COUNT(PRODUCT) AS COUNT
FROM WRK.FINAL_SAP_AC_4
GROUP BY ACCOUNT_NUMERIC
HAVING COUNT(PRODUCT) > 1
;
QUIT;
PROC SQL;
CREATE TABLE WRK.FINAL_SAP_DUP_02 AS
SELECT A.*
,CASE WHEN B.ACCOUNT_NUMERIC NE . AND LANGU IN ("a","A")
THEN "X" END AS DUP_CH
FROM WRK.FINAL_SAP_AC_4 AS A
LEFT JOIN WRK.FINAL_SAP_DUP_01 AS B ON A.ACCOUNT_NUMERIC = B.ACCOUNT_NUMERIC
;
QUIT;
DATA WRK.FINAL_SAP_AC_5;
SET WRK.FINAL_SAP_DUP_02;
IF DUP_CH = "X" THEN DELETE;
RUN;
/*Create Final File*/
PROC SQL;
CREATE TABLE WRK.SAP_BP_COUNT AS
SELECT BPID
,COUNT(ACCOUNT_NUMERIC) AS AccountsForThisBP
,
CASE
WHEN COUNT(ACCOUNT_NUMERIC) = 1 THEN "Only 1 acc for this BP"
ELSE "Multiple Accs for this BP"
END
AS MultipleAccountsPerBP
FROM WRK.FINAL_SAP_AC_5
GROUP BY BPID
;
QUIT;
/*Create Final File*/
PROC SQL;
CREATE TABLE FIN.FINAL_SAP_ALL (ENCODING=ANY) AS
SELECT DISTINCT ACCOUNT_NUMERIC AS AccNrSer
,B.AccountsForThisBP
,SYST_IND
,'' AS BOL FORMAT = $13.
,'' AS BOL_BP FORMAT = $16.
,A.BPID AS BP_ID
,BP_CAT AS BPCAT
,'' AS CashMan FORMAT = $17.
,'' AS CashMan_BP FORMAT = $20.
,A.DATE_CLOSED FORMAT = 8.
,A.DATE_OPENED FORMAT = 8.
,'' AS DormCodeDescr FORMAT = $200.
,'' AS E3 FORMAT = $12.
,'' AS E3_BP FORMAT = $15.
,BOOK_BAL
,INPUT(BP_MKT_SEG,8.) AS MKT_SEG
,BP_MKT_SEG FORMAT = $90.
,BP_MKT_SEG_DESC FORMAT = $char45.
,Business_Area FORMAT = $char18.
,Portfolio FORMAT = $50.
,LEGAL_ENTITY FORMAT = $6.
,LEGAL_ENTITY_DESC FORMAT = $40.
,B.MultipleAccountsPerBP FORMAT = $25.
,'' AS NegotiatedPricingAcc FORMAT = $30.
,'' AS NegotiatedPricingBP FORMAT = $24.
,'' AS NP_Cheque_Serve_Min_Serv_Fee FORMAT = $59.
,'' AS NP_Cash_Deposit_Fee FORMAT = $37.
,'' AS NP_Monthly_Management_Fee_Ind FORMAT = $43.
,'' AS NP_Cheque_Deposit_Encash_Ind FORMAT = $57.
,'' AS NP_Dishonour_Fee_Ind FORMAT = $34.
,'' AS NP_Automatic_Clear_Facility_Ind FORMAT = $48.
,'' AS NP_Nominated_Fee_Acc_Ind FORMAT = $43.
,'' AS NP_Overdraft_Service_Fee_Ind FORMAT = $42.
,'' AS NP_Initiation_Fee_Ind FORMAT = $35.
,'' AS NP_Unutilised_Facility_Fee_Ind FORMAT = $44.
,'' AS NP_Honour_Fee_Ind FORMAT = $31.
,'' AS NP_Statement_Fee_Ind FORMAT = $42.
,'' AS NP_Stop_Payment_Fee_Ind FORMAT = $37.
,OpenorClosed FORMAT = $6.
,'' AS UsingNomination FORMAT = $74.
,CATX(' - ',PRODUCT_CODE,PRODUCT) AS ProdCodeDescr FORMAT = $200.
,'' AS STATUS FORMAT = $200.
,'' AS StyleCodeDescr FORMAT = $200.
,'' AS ACCOUNT_TYPE FORMAT = $50.
,DESCRIPTION AS TypeDesc FORMAT = $50.
,. AS RSKGP_N FORMAT = 8.
,DATETIME_OPENED FORMAT = $200.
FROM WRK.FINAL_SAP_AC_5 (ENCODING=ANY) AS A
LEFT JOIN WRK.SAP_BP_COUNT (ENCODING=ANY) AS B ON A.BPID = B.BPID
;
QUIT;
/*CREATE DATE VARIABLE FOR TODAY'S DATE*/
Data _null_;
format Report_dt $26. d yymmdd10. t time.;
d = today();
t = time();
FileDate = compress(translate(catx('_',put(d,yymmddn8.),put(t,time.)),'_',':'));
date_use = put(d,yymmddn8.);
call symput ('date_use',trim(date_use));
Run;
/*ARCHIVE LATEST FILE IN ARCHIVE FOLDER WITH TODAY'S DATE*/
PROC SORT DATA=FIN.FINAL_SAP_ALL OUT= ARCH.FINAL_SAP_&date_use.;
BY AccNrSer;
RUN;
/*USING ARCHIVED FILE FOR TODAY - CREATE SUMMARISED VIEW*/
PROC SQL;
CREATE TABLE ARCH.SAP_&date_use. AS
SELECT DISTINCT
OpenOrClosed
,BPCAT
,Business_Area
,ProdCodeDescr
,TypeDesc
,COUNT(AccNrSer) AS NumberOfAccounts
,&date_use. as TIMESTAMP
FROM ARCH.FINAL_SAP_&date_use.
GROUP BY OpenOrClosed,BPCAT,Business_Area,ProdCodeDescr,TypeDesc
;
QUIT;
/*IN THE EVENT OF A RERUN - DELETE ALL RECORDS WITH TODAY'S DATE IN THE PREP FILE*/
DATA TREND.SAP_TREND_PREP;
SET TREND.SAP_TREND_PREP;
IF TIMESTAMP = &date_use. THEN DELETE;
RUN;
/*APPEND SUMMARISED VIEW ONTO TREND TABLE*/
PROC APPEND BASE=TREND.SAP_TREND_PREP DATA=ARCH.SAP_&date_use. FORCE;
RUN;
/*FIX TIMESTAMP BY CONVERTING TO TEXT AND MOVE TO FINAL FOLDER FOR INGESTION*/
DATA FIN.SAP_Trend;
SET TREND.SAP_TREND_PREP;
FORMAT TIMESTAMP_1 $8.;
TIMESTAMP_1 = TIMESTAMP;
DROP TIMESTAMP;
RENAME TIMESTAMP_1 = TIMESTAMP;
RUN;
/*Email Report*/
%INCLUDE "/SAS/data/RiskDataManagement/data/NWOW/data/VBM/BRACC/03_Scripts/Ad_Hoc_Scripts/SAP_Email_Report.sas";
/*DELETE WORKING TABLES*/
PROC DELETE DATA= WRK.TO_COMM_AC ;
PROC DELETE DATA= WRK.TO_AUSP_AC ;
PROC DELETE DATA= WRK.BA_MKT_MATCH_AC ;
PROC DELETE DATA= WORK.NO_MATCH_AC ;
PROC DELETE DATA= WRK.TO_BUT000_AC ;
PROC DELETE DATA= WRK.BA_BUT_MATCH_AC;
PROC DELETE DATA= WRK.BUTID_SORT ;
PROC DELETE DATA= WRK.BUTID_FILTER ;
PROC DELETE DATA= WRK.TO_BUTID_AC ;
PROC DELETE DATA= WRK.TO_BKYC_AC ;
PROC DELETE DATA= WRK.WEALTH_ACCS ;
PROC DELETE DATA= WRK.BA_WI_AC ;
PROC DELETE DATA= OTHER_AC ;
PROC DELETE DATA= BA_OTHER_AC ;
PROC DELETE DATA= BA_OTHER_01_AC ;
PROC DELETE DATA= WRK.BA_CRM_01_AC ;
PROC DELETE DATA= WRK.BA_CRM_AC ;
PROC DELETE DATA= WRK.OPEN_ACC_AC ;
PROC DELETE DATA= WRK.TO_COMMPRO_AC ;
PROC DELETE DATA= WRK.BA_PRODUCT_AC ;
PROC DELETE DATA= WRK.TO_PROD_TEXT_AC ;
PROC DELETE DATA= WRK.FINAL_SAP_AC ;
PROC DELETE DATA= WRK.FINAL_SAP_AC_2 ;
PROC DELETE DATA= WRK.FINAL_SAP_AC_3 ;
PROC DELETE DATA= WRK.FINAL_SAP_AC_4 ;
PROC DELETE DATA= WRK.SAP_BP_COUNT ;
PROC DELETE DATA= WRK.BUTID_SORT_AC ;
PROC DELETE DATA= WRK.BALANCES ;
PROC DELETE DATA= WRK.TO_BALANCES ;
Click image to register for webinar
