12 Month data mcro

Reply
Contributor
Posts: 30

12 Month data mcro

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
Super User
Posts: 8,069

Re: 12 Month data mcro

Posted in reply to Allistair

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 30

Re: 12 Month data mcro

Posted in reply to KurtBremser

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.

Super User
Posts: 8,069

Re: 12 Month data mcro

Posted in reply to Allistair

Allistair 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 Smiley Wink

 

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 30

Re: 12 Month data mcro

Posted in reply to KurtBremser

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;

 

 

 

Super User
Posts: 8,069

Re: 12 Month data mcro

Posted in reply to Allistair

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 30

Re: 12 Month data mcro

Posted in reply to KurtBremser

please find attached sample dataset

Super User
Posts: 8,069

Re: 12 Month data mcro

[ Edited ]
Posted in reply to Allistair

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 8,069

Re: 12 Month data mcro

Posted in reply to Allistair

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-dat...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 8 replies
  • 90 views
  • 0 likes
  • 2 in conversation