topic Re: Append - Unsuccessful in SAS Programming

Append - Unsuccessful

VALLY — Mon, 19 Jul 2021 12:49:18 GMT

Good day expects.

am running the below query, however it sends a failure notification. i have attached a log as well. please assist.

%LET PATH = /SAS/data/RiskDataManagement/data/NWOW/data/VBM/BRACC/03_Scripts/BRACC_Model;

%LET PATH_2 = /SAS/data/RiskDataManagement/data/NWOW/data/VBM/BRACC/03_Scripts/Ad_Hoc_Scripts;

%LET PATH_3 = /SAS/data/RiskDataManagement/data/NWOW/data/VBM/BRACC/03_Scripts/Drop_Rules;

/*CREATE BP AND SAP INDICATOR FOR BRACC ALL*/

%INCLUDE "&PATH./TRANSFORM_BRACC_BPIND.sas";

/*RUN BRACC_TREND - THIS WILL CREATE THE BRACC TREND FILE*/

%INCLUDE "&PATH./TRANSFORM_BRACC_TREND.sas";

/*CREATE EMAIL TO SEND OUT TO TEAM*/

%INCLUDE "&PATH_2./Email_ReportwithDifference.sas";

/*RUN STATUS - THIS WILL CREATE THE BUCKETS DATASET*/

%INCLUDE "&PATH./TRANSFORM_BRACC_BPSVC.sas";

/*APPEND SAP AND BRACC - BP AND SAP INDICATOR IS RECREATED FOR APPENED FILE WITHIN THIS STEP*/

%INCLUDE "&PATH./TRANSFORM_BRACC_UNION.sas";

/*Email Report*/

%INCLUDE "/SAS/data/RiskDataManagement/data/NWOW/data/VBM/BRACC/03_Scripts/Ad_Hoc_Scripts/Append_Email_Report.sas";

Re: Append - Unsuccessful

Tom — Mon, 19 Jul 2021 12:57:51 GMT

You haven't really shown use what error it is that you don't understand. You didn't even really show us the code you ran, just the code you use to include the actual code.

To help you debug use the SOURCE2 option so that the actual commands from the include files will be displayed in your SAS log. You can either set the system option at the top of your program:

options source2;

Or add it as an option on the %INCLUDE statement itself:

%INCLUDE "&PATH./TRANSFORM_BRACC_BPIND.sas" / source2;

The first thing I see in your LOG is that it appears you are trying to capture the output of PROC CONTENTS using ODS OUTPUT statements but something has gone wrong and the output your code expected were not produced.

Re: Append - Unsuccessful

VALLY — Mon, 19 Jul 2021 13:10:06 GMT

see below error message

121 The SAS System 08:32 Monday, July 12, 2021

system cpu time 0.00 seconds
memory 464.59k
OS Memory 24232.00k
Timestamp 2021/07/12 09:58:51 AM
Step Count 167 Switch Count 0
Page Faults 0
Page Reclaims 172
Page Swaps 0
Voluntary Context Switches 6
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: The file OUTBOX is:
E-Mail Access Device

Message sent
To: "
Cc:
Bcc:
Subject: SAP and BRACC Append - Unsuccessful
Attachments:
NOTE: 6 records were written to the file OUTBOX.
The minimum record length was 11.
The maximum record length was 28.
NOTE: DATA statement used (Total process time):
real time 7.66 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 318.50k
OS Memory 23720.00k
Timestamp 2021/07/12 09:58:59 AM
Step Count 168 Switch Count 11
Page Faults 0
Page Reclaims 119
Page Swaps 0
Voluntary Context Switches 80
Involuntary Context Switches 4
Block Input Operations 0
Block Output Operations 0

WARNING: Apparent symbolic reference TOTAL not resolved.
WARNING: Apparent symbolic reference SB_RECS not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&TOTAL. = &SB_RECS.
ERROR: The macro AP_REPORT will stop executing.

ERROR: Errors printed on page 121.

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
real time 1:26:11.88
user cpu time 35:36.70
system cpu time 18:24.21

Re: Append - Unsuccessful

Tom — Mon, 19 Jul 2021 13:26:01 GMT

So these are the lines that tell you want the issue is.

WARNING: Apparent symbolic reference TOTAL not resolved.
WARNING: Apparent symbolic reference SB_RECS not resolved.

Your code is expecting those two macro variables to have been created. So look backwards in the LOG (or program) and see where it was supposed to create them and find out why they did not get populated.

Re: Append - Unsuccessful

VALLY — Tue, 20 Jul 2021 08:41:52 GMT

@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 ;

Re: Append - Unsuccessful

Kurt_Bremser — Tue, 20 Jul 2021 08:46:24 GMT

Without having access to ALL your source datasets, the code as such won't tell us anything.

Scan your log, and fix any WARNINGs or ERRORs from the top down. Fixing the first problem will often take care of the rest (or at least most of it), whil fixing from the bottom up is usually an exercise in futility.

If you have problems with this, post the complete log of the first step that results in a WARNING or ERROR. Use this button for posting the log: