BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
lvalencia
Fluorite | Level 6
WITH
	ACTIVITY AS 
		(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(IFNULL( 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
		),
		
		
	ACCESS_TRANSFERS AS 
		(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
		),

	Cork_Transfers AS 
		(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
		),
	
	Fort3_Transfers AS 
		(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 
		),

	Closing AS
		(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 
		(IFNULL(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)

SELECT 
COALESCE(opening.investor,newloans.investor, closing.investor, activity.investor, access_transfers.investor, cork_transfers.investor, fort3_transfers.investor) AS Investor, 
round(IFNULL(opening_balance,0),2) AS Opening_Balance, 
round(IFNULL(origination,0),2) AS origination, round(IFNULL(ppi_topup,0),2) AS PPI_Added, -round(principal_repayments,2) AS repayments, -round(DEFAULTed,2) AS defaulted, 
round(closing_balance,2) AS Closing_Balance,
round((IFNULL(transfer_fort3,0)+IFNULL(transfer_access,0)+IFNULL(transfer_cork,0)),2) AS transferred,
round((IFNULL (opening_balance,0)+IFNULL(origination,0)+IFNULL(ppi_topup,0)-IFNULL(principal_repayments,0)-IFNULL(defaulted,0)-IFNULL(closing_balance,0)+IFNULL(transfer_fort3,0)+IFNULL(transfer_access,0)+IFNULL(transfer_cork,0)),2) AS Check_Closing,
round(-default_Diff,2) AS Default_Variance,
round(opening_balance+IFNULL(origination,0)+IFNULL(ppi_topup,0)-IFNULL(principal_repayments,0)-IFNULL(defaulted,0)-IFNULL(closing_balance,0)+IFNULL(transfer_fort3,0)+IFNULL(transfer_access,0)+IFNULL(transfer_cork,0)-IFNULL(default_diff,0),2) AS Remaining_Variance,
round(IFNULL(interest_repayments,0),2) AS interest_repayments,
round(IFNULL(interest_recoveries,0),2) AS interest_recoveries,
round(IFNULL(principal_recoveries,0),2) AS principal_recoveries,
round(IFNULL(interest_repayments,0)+IFNULL(interest_recoveries,0)+IFNULL(principal_recoveries,0)+IFNULL(principal_repayments,0),2) AS Total_Collections,
round(IFNULL(expected_recoveries,0),2) AS Expected_Recoveries,
CASE WHEN Closing_Balance>0 THEN Round(IFNULL(interest_accrued,0),2) ELSE 0 end AS Interest_Accrued
FROM closing
LEFT JOIN opening ON opening.investor = closing.investor 
LEFT JOIN newloans ON newloans.investor = closing.investor
LEFT JOIN activity ON activity.investor = closing.investor
LEFT JOIN Access_Transfers ON access_transfers.investor = closing.investor
LEFT JOIN Cork_Transfers ON Cork_transfers.investor = closing.investor
LEFT JOIN Fort3_transfers ON Fort3_Transfers.investor = closing.investor
Order BY Investor

I must convert the sql code to sas code. How to I convert sql code to sas code?

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

2 REPLIES 2
yabwon
Onyx | Level 15

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

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



lvalencia
Fluorite | Level 6
Your suggestions worked. Thanks!

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 243 views
  • 3 likes
  • 2 in conversation