topic Re: 12 Month data mcro in SAS Programming

12 Month data mcro

EMC9 — Tue, 21 Nov 2017 06:45:21 GMT

Good day

I have a process I am busy with but need help executing it in a macro, I have a data set I created from various sources and added indicators to reflect 1 & 0 , I have the data for one month but need this for June2104 till june2015 , then I need it to run with the current months monthly.

this is how the data looks and how I need to look monthly with the addition of each month & indicators:

unique	prod1	prod2	prod3	prod4	prod5	prod6	Count	prod1_June14	prod2__June14	prod3___June14	prod4__july14	prod5_June14	prod6_June14	Count_June14	prod1_July14	prod2__July14	prod3___July14	prod4__july14	prod5_July14	prod6_July14	Count_July14
1	0	1	1	1	1	0	4	0	1	1	1	1	0	4	0	1	1	1	1	0	4
2	1	0	0	1	1	1	4	1	0	0	1	1	1	4	1	0	0	1	1	1	4
3	1	1	1	1	1	1	6	1	1	1	1	0	1	5	1	1	1	1	0	1	5
4	1	0	0	0	1	1	3	1	0	0	0	1	1	3	1	0	0	0	1	1	3
5	1	1	1	0	0	0	3	1	0	1	0	0	0	2	1	0	1	0	0	0	2
6	0	1	1	1	1	0	4	0	1	1	0	1	0	3	0	1	1	0	1	0	3
7	0	0	0	0	1	1	2	0	0	0	0	1	1	2	0	0	0	0	1	1	2
8	1	1	1	0	1	1	5	1	1	1	0	1	1	5	1	1	1	0	1	1	5
9	1	1	0	0	0	1	3	1	1	0	0	0	1	3	1	1	0	0	0	1	3
10	1	1	1	0	1	1	5	1	1	1	0	1	1	5	1	1	1	0	1	1	5

Re: 12 Month data mcro

Kurt_Bremser — Tue, 21 Nov 2017 06:53:16 GMT

Very unwieldy. Why don't you use a long format, with rows for products & dates?

Re: 12 Month data mcro

EMC9 — Tue, 21 Nov 2017 07:05:42 GMT

Hi Kurt

thank you , this is what the requester wanted , I would prefer to have it like this so the macro around this would help alot.

Re: 12 Month data mcro

Kurt_Bremser — Tue, 21 Nov 2017 07:14:59 GMT

@EMC9 wrote:

Hi Kurt

thank you , this is what the requester wanted , I would prefer to have it like this so the macro around this would help alot.

I hope you charge accordingly 😉

So you have the current month's data, and need to add it to a dataset like the one shown, creating new columns? Could you please supply an example for the data that needs to be attached? Posting it in a data step would simplify everything a lot.

Re: 12 Month data mcro

EMC9 — Tue, 21 Nov 2017 07:40:15 GMT

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;

Re: 12 Month data mcro

Kurt_Bremser — Tue, 21 Nov 2017 07:48:20 GMT

I just wanted:

- an example for the existing dataset

- an example for the data to be added, as you get it

Then I can create code that attaches the new data to the existing dataset. But I need to know the structure of both datasets, and some values are nice for testing.

Re: 12 Month data mcro

Kurt_Bremser — Tue, 21 Nov 2017 07:49:15 GMT

PS how to convert a datset to a datastep for posting: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: 12 Month data mcro

EMC9 — Wed, 22 Nov 2017 09:08:34 GMT

please find attached sample dataset

Re: 12 Month data mcro

Kurt_Bremser — Wed, 22 Nov 2017 09:15:23 GMT

This is not a dataset, it's a Excel spreadsheet.

- MS Office files are blocked at many corporate sites against downloading from the internet, for security reasons

- said security reasons prevent sensible users from doing that, anyway.

- Excel files have no structure, therefore they cannot convey important metadata information (variable types, lengths, formats) that is essential when dealing with SAS data

bottom line: Excel files are useless.

Follow the link I gave you and create datasteps and post them here. Then I can simply copy/paste and submit your code and get the identical datasets you are working with. Without having to explain to my security auditors why I'm stupidly downloading external files with possible malware to the company IT.