I am currently working on a project and have all my queries written in SQL. I have tried replicating some of these queries using the SAS DI Studio but I've been unable to as I keep getting an error every time. I'd appreciate if anyone who has had experience with something similar can point me in the right direction of how to go about it. The Query is below: select exc.ACCOUNT_RK, exc.excess_amt
into #temp_largest_excess
from (
select
la.ACCOUNT_RK
,max((fac.BALANCE_AMT - la.X_MAX_AVAILABLE_AMT)) as excess_amt
from LOAN_ACCOUNT la
inner join FINANCIAL_ACCOUNT_CHNG fac on fac.ACCOUNT_RK = la.ACCOUNT_RK and la.VALID_FROM_DTTM between fac.VALID_FROM_DTTM and fac.valid_to_dttm and fac.currency_cd = 'CAD' -- align time periods
where fac.VALID_FROM_DTTM between DATEADD(m,-1,DATEADD(m, DATEDIFF(m,0,GETDATE()),0)) and EOMONTH(dateadd(month,-1,getdate())) -- balances between start and end of last month
and (fac.BALANCE_AMT - la.X_MAX_AVAILABLE_AMT) > 0 -- defn of excess
group by la.ACCOUNT_RK
UNION
select
la.ACCOUNT_RK
,max((fac.BALANCE_AMT - la.X_MAX_AVAILABLE_AMT)) as excess_amt
from LEASE_ACCOUNT la
inner join FINANCIAL_ACCOUNT_CHNG fac on fac.ACCOUNT_RK = la.ACCOUNT_RK and la.VALID_FROM_DTTM between fac.VALID_FROM_DTTM and fac.valid_to_dttm and fac.currency_cd = 'CAD' -- align time periods
where fac.VALID_FROM_DTTM between DATEADD(m,-1,DATEADD(m, DATEDIFF(m,0,GETDATE()),0)) and EOMONTH(dateadd(month,-1,getdate())) -- balances between start and end of last month
and (fac.BALANCE_AMT - la.X_MAX_AVAILABLE_AMT) > 0 -- defn of excess
group by la.ACCOUNT_RK
UNION
select
la.ACCOUNT_RK
,max((fac.BALANCE_AMT - la.X_MAX_AVAILABLE_AMT)) as excess_amt
from MORTGAGE_ACCOUNT la
inner join FINANCIAL_ACCOUNT_CHNG fac on fac.ACCOUNT_RK = la.ACCOUNT_RK and la.VALID_FROM_DTTM between fac.VALID_FROM_DTTM and fac.valid_to_dttm and fac.currency_cd = 'CAD' -- align time periods
where fac.VALID_FROM_DTTM between DATEADD(m,-1,DATEADD(m, DATEDIFF(m,0,GETDATE()),0)) and EOMONTH(dateadd(month,-1,getdate())) -- balances between start and end of last month
and (fac.BALANCE_AMT - la.X_MAX_AVAILABLE_AMT) > 0 -- defn of excess
group by la.ACCOUNT_RK
) exc and this: select
ROW_NUMBER() OVER (ORDER BY ACCOUNT_RK,VALID_FROM_DT) AS rn,
dateadd(day, -ROW_NUMBER() OVER (ORDER BY ACCOUNT_RK,VALID_FROM_DT), VALID_FROM_DT) AS grp,
ACCOUNT_RK,
VALID_FROM_DT
into #temp_excess_groups Anyone with an idea on how best to get these replicated in SAS DI Studio?
... View more
