this is the code I used it is very messy :
data TG.Savings_pocket&MONTH.;
set lib.uniqu_acct&MONTH.;
where sub_prod_cde in ('test');
SP_IND = 1;
RUN;
proc sql;
create table TG.Savings_Pocket_&MONTH. as
selct a.*,
b.unique as SP_ind
from TG.Credit_Card_&MONTH. as a
left join TG.Savings_pocket&MONTH. as b
on a.unique_orig = b.unique;
quit;
proc sql;
create table TG.Cash_Investments_&MONTH. as
select unique, uniqu_acct , stat_cde, PROD_CDE, SUB_PROD_CDE, cur_bal_amt
from lib.uniqu_acct&MONTH.
where co_cde= 15 and prod_cde = 'ALL' and sub_prod_cde in ('an') and stat_cde = '99' and unique in (Select unique from TG.Savings_Pocket_&MONTH.)
union all
select unique, uniqu_acct, stat_cde, PROD_CDE, SUB_PROD_CDE, cur_bal_amt
from I1TDA.MI_TDA_ACCT_&MONTH.
where co_cde= 15 and prod_cde in ('test') and stat_cde = '99' and unique in (Select unique from TG.Savings_Pocket_&MONTH.);
quit;
proc sql;
create table TG.Cash_Investments_&MONTH. as
select distinct unique,"CI" as CI_Ind
from TG.Cash_Investments_&MONTH.
group by unique;
quit;
proc sql;
create table TG.Cash_Investments_2_&MONTH. as
selct a.*,
B.CI_Ind
from TG.Savings_Pocket_&MONTH. as a
left join TG.Cash_Investments_&MONTH. as b
on a.unique = b.unique;
quit;
/*Home Loans*/
proc sql;
create table TG.Home_Loans_&MONTH. as
select distinct unique,
(case
when prod_cde = 'ALL' and
ln_tpe in ('AHL','CHL','MDL','SHL','FOA')and
stat_cde in ('NN') then "HL."
else ""
end)
as HL_ind
from LIB.LIB&MONTH.;
quit;
proc sort data=TG.Home_Loans_&MONTH. nodupkey;
by unique;
run;
proc sql;
create table TG.Home_Loans_2_&MONTH. as
selct a.*,
B.HL_Ind
from TG.Cash_Investments_2_&MONTH. as a
left join TG.Home_Loans_&MONTH. as b
on a.unique = b.unique;
quit;
/*Revolving Loans*/
proc sql;
create table TG.Revolving_Loans_&MONTH. as
select distinct unique,
(case
when LN_TPE = "RCP" and stat_cde not in ('FCL','P-O','UST') then "RL."
else ""
end)
as RL_ind
from I1ILP.MI_ILP_ACCT_&MONTH.;
quit;
proc sort data=TG.Revolving_Loans_&MONTH. nodupkey;
by unique;
run;
proc sql;
create table TG.Revolving_Loans_2_&MONTH. as
selct a.*,
B.RL_Ind
from TG.Cash_Investments_2_&MONTH. as a
left join TG.Revolving_Loans_&MONTH. as b
on a.unique = b.unique;
quit;
/*Personal Loans*/
proc sql;
Create table TG.Personal_Loans_&MONTH. as
select distinct a.unique,"PL." as PL_Ind
from I1ILP.mi_ilp_acct_&MONTH. a inner join idmi01.MI_SUB_PROD_T b on a.LN_TPE = b.SUB_PROD_CDE and a.PROD_CDE = b.PROD_CDE and a.CO_CDE = b.CO_CDE
left join I1ILP.MI_ILP_ACCT_&MONTH. c on a.unique = c.unique
where a.prod_cde = 'PLS' and a.stat_cde = '' and
a.ln_tpe in ('AAA');
quit;
proc sort data=TG.Personal_Loans_&MONTH. nodupkey;
by unique;
run;
proc sql;
create table TG.Personal_Loans_2_&MONTH. as
selct a.*,
B.PL_Ind
from TG.Revolving_Loans_2_&MONTH. as a
left join TG.Personal_Loans_&MONTH. as b
on a.unique = b.unique;
quit;
/*OD IND*/
proc sql ;
create table TG.OD_IND_2&MONTH. as select
a.*,
b.unique as OD_IND
from VBS.Personal_Loans_2_&MONTH. AS A
LEFT JOIN DDA_M.uniqu_acct&MONTH. AS B
ON A.unique_ORIG = B.unique;
WHERE b.CR_LIMIT_AMT < -1000;
RUN;
proc sql;
create table TG.TLN_IND&MONTH. AS SELECT
A.*,
b.RDA_ACCOUNT_NO as TLN_IND
FROM TG.OD_IND_2&MONTH. AS A
LEFT JOIN DDA_M.uniqu_acctMONTH. AS B
ON A.unique_ORIG = B.unique;
WHERE B.RDA_ACCOUNT > 0;
RUN;
proc sort data=TG.tln_ind&MONTH. nodupkey;
by unique_orig;
run;
DATA TG.CC_IND&MONTH.;
SET VBS.All_Products_unique_CC;
IF unique ^= '' THEN CC_IND = 1;
RUN;
proc sort data=TG.CC_IND&MONTH. nodupkey;
by unique;
run;
PROC SQL;
CREATE TABLE TG.CC_IND_A&MONTH. AS
SELECT A.*,
B.CC_IND AS NEW_CC_IND
FROM vbs.Final_VSI_Indicator_2 AS A
LEFT JOIN TG.CC_IND AS B
ON A.unique_ORIG = B.unique;
QUIT;
Data TG.CC_IND_A_FINAL&MONTH.;
set TG.CC_IND_A&MONTH.;
if NEW_CC_IND = "" then CC_IND_NEW&MONTH. = 0 ;
else CC_IND_NEW&MONTH. = 1;
if SP_IND = "" then SP_IND_NEW&MONTH. = 0 ;
else SP_IND_NEW&MONTH. = 1;
if CI_IND = "" then CI_IND_NEW&MONTH. = 0 ;
else CI_IND_NEW&MONTH. = 1;
if OD_IND = "" then OD_IND_NEW&MONTH. = 0 ;
else OD_IND_NEW&MONTH. = 1;
if TLN_IND = "" then TLN_IND_NEW&MONTH. = 0 ;
else TLN_IND_NEW&MONTH. = 1;
if PL_IND = "" then PL_IND_NEW&MONTH. = 0 ;
else PL_IND_NEW&MONTH. = 1;
run;
data TG.vsi_calc&MONTH.;
set TG.CC_IND_A_FINAL&MONTH.;
vsi_calc&MONTH. = sum( CC_IND_NEW&MONTH. , SP_IND_NEW&MONTH., CI_IND_NEW&MONTH., TLN_IND_NEW&MONTH. ,OD_IND_NEW&MONTH., PL_IND_NEW&MONTH.);
RUN;
