DATA ACCT;
INPUT ACCOUNT_ID ACCOUNT_TYPE$ BALANCE$ BALANCEDATE DATE9.;
FORMAT BALANCEDATE DATE9.;
DATALINES;
123 CHEQUING $234 30-NOV-20
234 CHEQUING $100 30-NOV-20
321 SAVING $150 30-NOV-20
456 CHEQUING $10,000 30-NOV-20
678 CARD $435 30-NOV-20
789 INVESTMENT $999 31-OCT-20
123 CHEQUING $155 31-OCT-20
234 CHEQUING $67 31-OCT-20
321 SAVING $0 31-OCT-20
456 CHEQUING $1,000 31-OCT-20
234 CHEQUING $500 30-SEP-20
321 SAVING $250 30-SEP-20
;
RUN;
DATA ACCTXREF;
INPUT ACCOUNT_ID CUSTOMER_ID ACCOUNT_MONTH DATE9.;
FORMAT ACCOUNT_MONTH DATE9.;
DATALINES;
123 3010 30-NOV-20
234 3010 30-NOV-20
234 8788 30-NOV-20
321 4556 30-NOV-20
456 6789 30-NOV-20
678 4510 30-NOV-20
789 3319 30-NOV-20
123 3010 31-OCT-20
234 3010 31-OCT-20
234 8788 31-OCT-20
321 4556 31-OCT-20
456 6789 31-OCT-20
234 3010 30-SEP-20
234 8788 30-SEP-20
321 4556 30-SEP-20
;
RUN;
PROC SORT DATA=ACCT;
BY ACCOUNT_ID;
RUN;
PROC SORT DATA=ACCTXREF;
BY ACCOUNT_ID;
RUN;
DATA MERG;
MERGE ACCT ACCTXREF(RENAME=(ACCOUNT_MONTH=BALANCEDATE));
BY ACCOUNT_ID;
RUN;
DATA TASK1;
SET MERG;
WHERE ACCOUNT_TYPE IN("CHEQUING","SAVING");
WHERE ALSO BALANCEDATE='30NOV-20'D;
RUN;
PROC SORT DATA=TASK1;
BY CUSTOMER_ID;
RUN;
DATA TASK_1;
SET TASK1;
BY CUSTOMER_ID;
IF FIRST.CUSTOMER_ID=1 THEN TOTAL_BALANCE=BALANCE;
ELSE TOTAL_BALANCE=BALANCE+TOTAL_BALANCE;
RUN;
... View more