Could it be a SAS version of SQL? Like that:
%let
ACTIVITY =
(SELECT
(CASE WHEN closing_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON = 1 THEN 'Aegon'
WHEN closing_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON IS NULL THEN 'Aegon-Finco'
WHEN closing_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN CLOSING_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID IS NULL THEN 'Access'
WHEN closing_INVESTOR_ID = 7048910 THEN 'Fort-1'
WHEN closing_INVESTOR_ID =7788504 THEN 'Fort-2'
WHEN closing_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN closing_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID IS NULL THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN closing_INVESTOR_ID = 8794037 AND OPENING_INVESTOR_ID = 8794037 THEN 'Fort-3'
WHEN closing_INVESTOR_ID = 8794037 AND OPENING_INVESTOR_ID IS NULL THEN 'Access' ELSE 'Review' END) AS Investor,
sum(PPI_TOPUP_REPORTING_MONTH) AS PPI_TOPUP,
sum(PRINCIPAL_REPAYMENTS) AS PRINCIPAL_REPAYMENTS,
sum(CoAlEsCe( DEFAULT_Amount,0)) AS Defaulted,
sum(CASE WHEN DEFAULT_AMOUNT IS NOT NULL THEN (CURRENT_PRINCIPAL_BALANCE_PREV-DEFAULT_AMOUNT) ELSE 0 END) AS DEFAULT_DIFF,
sum(INTEREST_REPAYMENTS)AS INTEREST_REPAYMENTS,
sum(PRINCIPAL_RECOVERIES) AS PRINCIPAL_RECOVERIES,
Sum(INTEREST_RECOVERIES) AS INTEREST_RECOVERIES,
sum(CASE
WHEN DEFAULT_AMOUNT IS NOT NULL AND PRincipal_recoveries IS NULL AND closing_INVESTOR_ID = 764626 THEn DEFAULT_amount*0.44
WHEN DEFAULT_AMOUNT IS NOT NULL AND PRincipal_recoveries IS NULL AND closing_INVESTOR_ID <> 764626 THEn DEFAULT_amount*0.35
WHEN DEFAULT_AMOUNT IS NOT NULL AND PRincipal_recoveries = 0 AND closing_INVESTOR_ID = 764626 THEn DEFAULT_amount*0.44
WHEN DEFAULT_AMOUNT IS NOT NULL AND PRincipal_recoveries = 0 AND closing_INVESTOR_ID <> 764626 THEn DEFAULT_amount*0.35
ELSE 0 END) AS Expected_Recoveries
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_v2 feap
GROUP BY CASE WHEN closing_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON = 1 THEN 'Aegon'
WHEN closing_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON IS NULL THEN 'Aegon-Finco'
WHEN closing_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN CLOSING_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 6127857 AND OPENING_INVESTOR_ID IS NULL THEN 'Access'
WHEN closing_INVESTOR_ID = 7048910 THEN 'Fort-1'
WHEN closing_INVESTOR_ID =7788504 THEN 'Fort-2'
WHEN closing_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN closing_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID IS NULL THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 8271826 AND OPENING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN closing_INVESTOR_ID = 8794037 AND OPENING_INVESTOR_ID = 8794037 THEN 'Fort-3'
WHEN closing_INVESTOR_ID = 8794037 AND OPENING_INVESTOR_ID IS NULL THEN 'Access' ELSE 'Review' END
);
%let
ACCESS_TRANSFERS =
(SELECT CONCAT('Access') AS Investor,
SUM(CASE
WHEN OPENING_INVESTOR_ID = 6127857 AND CLOSING_INVESTOR_ID = 8271826 THEN -CURRENT_PRINCIPAL_BALANCE
WHEN OPENING_INVESTOR_ID = 8271826 AND CLOSING_INVESTOR_ID = 6127857 THEN CURRENT_PRINCIPAL_BALANCE
WHEN OPENING_INVESTOR_ID IS NULL AND CLOSING_INVESTOR_ID = 8794037 THEN -CURRENT_PRINCIPAL_BALANCE
WHEN OPENING_INVESTOR_ID = 6127857 AND CLOSING_INVESTOR_ID = 8794037 THEN -CURRENT_PRINCIPAL_BALANCE
ELSE 0 END) AS Transfer_Access
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_V2 feap
);
%let
Cork_Transfers =
(SELECT CONCAT('Cork') AS Investor,
SUM(CASE
WHEN OPENING_INVESTOR_ID = 6127857 AND CLOSING_INVESTOR_ID = 8271826 THEN CURRENT_PRINCIPAL_BALANCE
ELSE 0 END) AS Transfer_Cork
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_v2 feap
);
%let
Fort3_Transfers =
(SELECT CONCAT('Fort-3') AS Investor,
SUM(CASE WHEN OPENING_INVESTOR_ID IS NULL AND CLOSING_INVESTOR_ID = 8794037 THEN CURRENT_PRINCIPAL_BALANCE
WHEN OPENING_INVESTOR_ID = 6127857 AND CLOSING_INVESTOR_ID = 8794037 THEN CURRENT_PRINCIPAL_BALANCE
ELSE 0 END) AS Transfer_Fort3
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_v2 feap
);
%let
Closing =
(SELECT (CASE
WHEN CLOSING_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON = 1 THEN 'Aegon'
WHEN CLOSING_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON IS NULL THEN 'Aegon-Finco'
WHEN CLOSING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN CLOSING_INVESTOR_ID = 7048910 THEN 'Fort-1'
WHEN CLOSING_INVESTOR_ID =7788504 THEN 'Fort-2'
WHEN CLOSING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 8794037 THEN 'Fort-3' ELSE 'Review' END) AS Investor,
SUM(CURRENT_PRINCIPAL_BALANCE) AS Closing_Balance,
sum(CASE WHEN closing_status = 1 AND CURRENT_PRINCIPAL_BALANCE > 0 THEN
(CoAlEsCe(CURRENT_PRINCIPAL_BALANCE_PREV,0)*CURRENT_INTEREST_RATE/360*DAYS_BETWEEN(cutoff_date,CASE WHEN date_disbursement < ADD_MONTHS(cutoff_date,-1) AND date_first_instalment_current > cutoff_date THEN DATE_disbursement ELSE ADD_MONTHS(cutoff_date,-1) END)) ELSE 0 END) AS Interest_Accrued
FROM RAW_RISK.FINANCE_EXTRACT_APR2023_v2 feap
GROUP BY
CASE WHEN CLOSING_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON = 1 THEN 'Aegon'
WHEN CLOSING_INVESTOR_ID = 764626 AND FLAG_PREV_AEGON IS NULL THEN 'Aegon-Finco'
WHEN CLOSING_INVESTOR_ID = 6127857 THEN 'Access'
WHEN CLOSING_INVESTOR_ID = 7048910 THEN 'Fort-1'
WHEN CLOSING_INVESTOR_ID =7788504 THEN 'Fort-2'
WHEN CLOSING_INVESTOR_ID = 8271826 THEN 'Cork'
WHEN CLOSING_INVESTOR_ID = 8794037 THEN 'Fort-3' ELSE 'Review' END);
proc SQL;
create table ABC as /* new line */
SELECT
COALESCE(opening.investor,newloans.investor, closing.investor, activity.investor, access_transfers.investor, cork_transfers.investor, fort3_transfers.investor) AS Investor,
round(CoAlEsCe(opening_balance,0),0.01) AS Opening_Balance,
round(CoAlEsCe(origination,0),0.01) AS origination, round(CoAlEsCe(ppi_topup,0),0.01) AS PPI_Added, -round(principal_repayments,0.01) AS repayments, -round(DEFAULTed,0.01) AS defaulted,
round(closing_balance,0.01) AS Closing_Balance,
round((CoAlEsCe(transfer_fort3,0)+CoAlEsCe(transfer_access,0)+CoAlEsCe(transfer_cork,0)),0.01) AS transferred,
round((IFNULL (opening_balance,0)+CoAlEsCe(origination,0)+CoAlEsCe(ppi_topup,0)-CoAlEsCe(principal_repayments,0)-CoAlEsCe(defaulted,0)-CoAlEsCe(closing_balance,0)+CoAlEsCe(transfer_fort3,0)+CoAlEsCe(transfer_access,0)+CoAlEsCe(transfer_cork,0)),0.01) AS Check_Closing,
round(-default_Diff,0.01) AS Default_Variance,
round(opening_balance+CoAlEsCe(origination,0)+CoAlEsCe(ppi_topup,0)-CoAlEsCe(principal_repayments,0)-CoAlEsCe(defaulted,0)-CoAlEsCe(closing_balance,0)+CoAlEsCe(transfer_fort3,0)+CoAlEsCe(transfer_access,0)+CoAlEsCe(transfer_cork,0)-CoAlEsCe(default_diff,0),0.01) AS Remaining_Variance,
round(CoAlEsCe(interest_repayments,0),0.01) AS interest_repayments,
round(CoAlEsCe(interest_recoveries,0),0.01) AS interest_recoveries,
round(CoAlEsCe(principal_recoveries,0),0.01) AS principal_recoveries,
round(CoAlEsCe(interest_repayments,0)+CoAlEsCe(interest_recoveries,0)+CoAlEsCe(principal_recoveries,0)+CoAlEsCe(principal_repayments,0),0.01) AS Total_Collections,
round(CoAlEsCe(expected_recoveries,0),0.01) AS Expected_Recoveries,
CASE WHEN Closing_Balance>0 THEN Round(CoAlEsCe(interest_accrued,0),0.01) ELSE 0 end AS Interest_Accrued
FROM &closing. as closing
LEFT JOIN &opening. as opening ON opening.investor = closing.investor
LEFT JOIN &newloans. as newloans ON newloans.investor = closing.investor
LEFT JOIN &activity. as activity ON activity.investor = closing.investor
LEFT JOIN &Access_Transfers. as Access_Transfers ON access_transfers.investor = closing.investor
LEFT JOIN &Cork_Transfers. as Cork_Transfers ON Cork_transfers.investor = closing.investor
LEFT JOIN &Fort3_transfers. as Fort3_transfers ON Fort3_Transfers.investor = closing.investor
Order BY Investor
I assume IFNULL() is equivalent to Coalesce() function , and in round() you want to have data rounded to the second decimal place (which in SAS verision of round() is 0.01). I didn't noticed any other sql functions, if there are any - let me know.
[EDIT:] this is almost 1-2-1 port of the original code, depending on your setup it may not be the optimal (in terms of performance). Espacialy if the
RAW_RISK.FINANCE_EXTRACT_APR2023_v2
is a table from external database. If it is from external database, first (potential) step for optimisation would be to get a copy of the table in SAS. If it is not possible, first I would materialise views from the WITH clause into tables, and then I would use them in the query.
I didn't look at the "business logic" of the code, just did syntax port, so it may be possible to rewrite it better in 4GL.
Bart
