topic Re: how to convert sql code to sas code in New SAS User

how to convert sql code to sas code

lvalencia — Wed, 31 May 2023 09:08:29 GMT

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?

Re: how to convert sql code to sas code

yabwon — Wed, 31 May 2023 09:46:29 GMT

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

Re: how to convert sql code to sas code

lvalencia — Wed, 31 May 2023 11:43:31 GMT

Your suggestions worked. Thanks!