Anil,
I have been deseperately looking for how to code Markov analysis. My boss helped me to get the code, so he is greatefully acknowledged (not given name for confidentiality reasons). I have put everything into a hypothetical data set, so you can run it and get the output.
I learned the part II which is how to reporting the resutls of Markov from this great discussion forum, so many thanks for the forum.
Here we go....
/*Part I: Markov*/
/*Run from this place onwards including the proc datasets code....*/
proc datasets library = work nolist;
delete all;
quit;
data portfolio;
inFormat Current_date date9.;
input Bank_number Account_number $ 4-12 Current_date Product $ 24-36 Balance Arrears_Band $43-49;
format Current_date date9.;
cards;
10 444444444 28FEB2010 Personal Loan 900 1 - 30
10 444444444 31MAR2010 Personal Loan 800 60 - 90
10 444444444 31MAY2010 Personal Loan 20 NPNA
10 444444444 31JUL2010 Personal Loan 25 NPNA
10 444444444 31AUG2010 Personal Loan 32 NPNA
70 666666666 28FEB2010 Personal OD 999 Current
70 666666666 31MAR2010 Personal OD 666 Current
80 777777 28FEB2010 Personal Loan 3000 90 +
;
run;
%macro createdata(prior_date, currrr_date);
/*For Feb*/
data previous (rename=(current_date=previous_date));
set portfolio (where=(current_date ="&prior_date"d));
run;
/*For Mar*/
data curr (rename=(current_date=current_date));
set portfolio (where=(current_date ="&currrr_date"d));
run;
/*soritng*/
/*for March 2010*/
proc sort data= curr;
by bank_number account_number;
run;
/*for Feb 2010*/
proc sort data= previous;
by bank_number account_number;
run;
Data Markov (keep= bank_number account_number previous_date current_date cur_arrears previous_arreras
cur_bal previous_bal PRODUCT);
length cur_arrears $ 13 previous_arreras $13;
merge previous (in=a rename=(arrears_band=previous_arreras balance =previous_bal))
curr (in=b rename=(arrears_band=cur_arrears balance =cur_bal))
;
by bank_number account_number;
if a and not b then cur_arrears = "Not Existing";
if b and not a then previous_arreras = "Not Existing";
run;
proc append base=all data= markov;run;
%mend;
%createdata(28FEB2010, 31MAR2010) ;
%createdata(31MAR2010, 30APR2010) ;
%createdata(30APR2010, 31MAY2010) ;
%createdata(31MAY2010, 30JUN2010) ;
%createdata(30JUN2010, 31JUL2010) ;
%createdata(31JUL2010, 31AUG2010) ;
%createdata(31AUG2010, 30SEP2010) ;
%createdata(30SEP2010, 31OCT2010) ;
%createdata(31OCT2010, 30NOV2010) ;
%createdata(30NOV2010, 31DEC2010) ;
%createdata(31DEC2010, 31JAN2011) ;
%createdata(31JAN2011, 28FEB2011) ;
%createdata(28FEB2011, 31MAR2011) ;
%createdata(31MAR2011, 30APR2011) ;
%createdata(30APR2011, 31MAY2011) ;
%createdata(31MAY2011, 30JUN2011) ;
%createdata(30JUN2011, 31JUL2011) ;
%createdata(31JUL2011, 31AUG2011) ;
%createdata(31AUG2011, 30SEP2011) ;
%createdata(30SEP2011, 31OCT2011) ;
%createdata(31OCT2011, 30NOV2011) ;
%createdata(30NOV2011, 31DEC2011) ;
%createdata(31DEC2011, 31JAN2012) ;
%createdata(31JAN2012, 29FEB2012) ;
%createdata(28FEB2012, 31MAR2012) ;
%createdata(31MAR2012, 30APR2012) ;
%createdata(30APR2012, 31MAY2012) ;
%createdata(31MAY2012, 30JUN2012) ;
%createdata(30JUN2012, 31JUL2012) ;
%createdata(31JUL2012, 31AUG2012) ;
%createdata(31AUG2012, 30SEP2012) ;
%createdata(30SEP2012, 31OCT2012) ;
%createdata(31OCT2012, 30NOV2012) ;
%createdata(30NOV2012, 31DEC2012) ;
/*proc sort data=all out=all_sorted;*/
/* by bank_number account_number ;*/
/*run;*/
/*We can take 2 approaches : i) Pivot Table, ii) Proc tabulate*/
/*Part II: CREATING A TABLE TO BE PRESENTED */
/* create format for sort order of Arrears_Band */
proc format ;
invalue ArrearsCode
'CURRENT' =1
'1-30' =2
'30-60' =3
'60-90' =4
'90+' =5
'NPNA' =6
'WRITOFF' =7
OTHER =99
;
value ArrearsText
1='Current'
2='1 - 30'
3='30 - 60'
4='60 - 90'
5='90 +'
6='NPNA'
7='writoff'
99='not_existing!'
;
run;
data ALL;
set ALL;
previous_arreras_b=input(upcase(compress(previous_arreras)),ArrearsCode.);
cur_arrears_b=input(upcase(compress(cur_arrears)),ArrearsCode.);
run;
options missing=' ';
proc tabulate data= ALL order=formatted;
format cur_arrears_b previous_arreras_b ArrearsText.;
/*label current_date='Cycle';*/
/*class current_date / order=unformatted ;*/
class previous_arreras_b / order=unformatted;
class cur_arrears_b / order=unformatted;
var previous_bal cur_bal;
table /*current_date,*/ previous_arreras_b ='', cur_arrears_b*(n cur_bal*sum) / box=previous_arreras_b printmiss; /*for bi-monthly pairs we need previous_bal, so cur_bal was commented out*/
table /*current_date,*/ previous_arreras_b ='', cur_arrears_b*(n previous_bal*sum) / box=previous_arreras_b printmiss;
/*Title 'Succession of O/S Balance ($ and %) and Accounts (#)';*/
run;
