Hi, I am trying to create a new table or data set differently based on logic that looks for specific records in a data set. For example, I want to create a new data set with a WTD, MTD, YTD, and LTD column from the original data set if all of those exist. If they don't (in my case there is no WTD in the original data set) then I want to insert a dummy WTD record and union it to the MTD, YTD, and LTD records from the original data set for the new data set. The way I have written the code, I get the following error: ERROR: A Composite expression (usually a subquery) is used incorrectly in an expression. Here is the code I have written. Is there a way to acomplish what I am tring to do? proc sql;
create table FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT_APP as
SELECT DISTINCT
CASE
WHEN EXISTS
(
select *
from FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT a3
where TIMEFRAME = 'WTD'
)
THEN
(
select
b.TIMEFRAME,
b._TYPE_ as _TYPE_,
b._PAGE_ as _PAGE_,
b._TABLE_ as _TABLE_,
b.INVESTOR_LOAN_ID_N,
b.NOTE_BALANCE_Sum,
b.NOTE_BALANCE_Mean,
b.WGT_AVG_20YR_Mean,
b.LTV_Mean,
b.FICO_Mean,
b.SRP_RATE_Mean,
b.SRP_AMT_Mean,
b.SRP_AMT_Sum,
b.PCT_TOTAL_UPB_PURCHASE_Mean,
b.PCT_TOTAL_UPB_REFI_Mean,
b.PCT_TOTAL_CNT_ESCROW_Mean,
b.PCT_TOTAL_CNT_NONESCROW_Mean,
CASE
WHEN b.TIMEFRAME = 'WTD' then 1
WHEN b.TIMEFRAME = 'MTD' then 2
WHEN b.TIMEFRAME = 'YTD' then 3
WHEN b.TIMEFRAME = 'LTD' then 4
END as TIMEFRAME_NO
from FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT b
/*order by TIMEFRAME_NO*/
)
else
(
SELECT
'WTD' as TIMEFRAME,
'1' as _TYPE_,
1 as _PAGE_,
1 as _TABLE_,
0 as INVESTOR_LOAN_ID_N,
0 as NOTE_BALANCE_Sum,
0 as NOTE_BALANCE_Mean,
0 as WGT_AVG_20YR_Mean,
0 as LTV_Mean,
0 as FICO_Mean,
0 as SRP_RATE_Mean,
0 as SRP_AMT_Mean,
0 as SRP_AMT_Sum,
0 as PCT_TOTAL_UPB_PURCHASE_Mean,
0 as PCT_TOTAL_UPB_REFI_Mean,
0 as PCT_TOTAL_CNT_ESCROW_Mean,
0 as PCT_TOTAL_CNT_NONESCROW_Mean,
1 as TIMEFRAME_NO
from FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT a
union
select
b.TIMEFRAME,
b._TYPE_ as _TYPE_,
b._PAGE_ as _PAGE_,
b._TABLE_ as _TABLE_,
b.INVESTOR_LOAN_ID_N,
b.NOTE_BALANCE_Sum,
b.NOTE_BALANCE_Mean,
b.WGT_AVG_20YR_Mean,
b.LTV_Mean,
b.FICO_Mean,
b.SRP_RATE_Mean,
b.SRP_AMT_Mean,
b.SRP_AMT_Sum,
b.PCT_TOTAL_UPB_PURCHASE_Mean,
b.PCT_TOTAL_UPB_REFI_Mean,
b.PCT_TOTAL_CNT_ESCROW_Mean,
b.PCT_TOTAL_CNT_NONESCROW_Mean,
CASE
WHEN b.TIMEFRAME = 'WTD' then 1
WHEN b.TIMEFRAME = 'MTD' then 2
WHEN b.TIMEFRAME = 'YTD' then 3
WHEN b.TIMEFRAME = 'LTD' then 4
END as TIMEFRAME_NO
from FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT b
/*order by TIMEFRAME_NO*/
) END as x
FROM FLOWFRED.FMFLOW_LOANSFUNDED_20YR_RPT a;
quit;
... View more
