Replicating some SQL Queries in SAS DI

[Soulbroda]

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?

[subin]

Are you using user written transformation?

[Soulbroda]

No. I intend to use the SQL transformations in the DI Studio.

[Patrick]

You will need to enable explicit pass-through in order to use database specific SQL syntax in expressions.

http://support.sas.com/documentation/cdl/en/etlug/67323/HTML/default/viewer.htm#n1sqvkm1pyw3a1n1ik1a... 

For the first rather complex SQL (meaning multiple tables with Unions and Joins): To set that up in a single transformation will be painful. You could consider to first create views for the joins using the SQL Join transformation and then use the SQL Set transformation for the Union. ....or you could just implement the code as user written code (within a Proc SQL as explicit pass-through code). Even if user written, you still should map source to target column - even though that won't be functional anymore it would at least still support data lineage/impact analysis on a SAS Metadata level.