Hi Haikuo, Thank you very much for this code which worked very well. I am trying to understand what this code is doing and could you please see if I have understood correctly, time permitting. This is what “ifn” function does based on literature I have reviewed. /*In the following code, we assign a value of 1 or 0 to flag variable*/ data test; set test; if sex= ‘Male’ then      flag =1;                          else  flag=0; run; /*Some programmers use the following code to do the same task above*/ data test;   set test; flag=ifn(sex=’Male’,1,0); run; This is your code data want;   do until (last.account_number);      set have;        by bank_number account_number current_date;           flag=ifn(Arrears_Band not in ('NPNA','writoff') and lag(Arrears_Band)='NPNA',1,flag);               if first.account_number then flag=0;   end;   do until (last.account_number);      set have;        by bank_number account_number current_date;        if flag then output;   end;   run; • Do until starts to read all the records pertinent to first account found in our data set called “this”. So, these are the records. Bank_number    Account_number  Current_date   Arrears_Band 10  111    30SEP2010 NPNA 10  111    31OCT2010 Current 10  111    30NOV2010 NPNA 10  111    30JUN2011 NPNA 10  111    01JAN2012 writoff when do until executes below statement, I think this is what happening. flag=ifn(Arrears_Band not in ('NPNA','writoff') and lag(Arrears_Band)='NPNA',1,flag); Above statement says: If Arrears_Band not in ('NPNA','writoff') and lag(Arrears_Band)='NPNA’ then a new variable called “flag” is created and its value is returned as 1. If this condition is not satisfied, then then the value for the variable “flag” is returned as "flag". (I am not too sure). Then comes to the next statement:               if first.account_number then flag=0; Above statement returns zero for the first account_number. Now this should be the answer after processing the account_number that do until first hits. Bank_number   Account_number Current_date Arrears_Band lag(Arrears_Band ) Flag 10 111   30SEP2010 NPNA 0 10 111 31OCT2010 Current NPNA 1 10 111   30NOV2010 NPNA Current flag 10 111   30JUN2011 NPNA NPNA flag 10 111   01JAN2012 writoff NPNA flag Then in the next do until pass, all the records whose flag = 1 are outputted. But then only the last 3 records of the above table should be outputted which is not our intended answer. But your code generated the intended answer. How come? Would appreciate if you could shed some light. Thank you for your time. Mirisage ... View more

Hi Haikuo, Thank you very much. This code correctly identifies one account which violates the business rule. This is the account. Obs     Bank_number     Account_number      Current_date      Arrears_Band     flag (these are variable names) 1 40 555 08FEB2010 30 - 60 1 2 40 555 31MAR2010 NPNA 1 3 40 555 30APR2010 60 - 90 1 4 40 555 31MAY2010 NPNA 1 However, there is another account which violates our business rule. Below shown is that account which is not identified by the code. 10  111    30SEP2010 NPNA 10  111    31OCT2010 Current /*after NPNA you will not get either "NPNA" or “writoff” which violates our rule */ 10  111    30NOV2010 NPNA 10  111    30JUN2011 NPNA 10  111    01JAN2012 writoff I wonder if you have sometime to have a look. Thank you for your time and expertise. Mirisage ... View more

Hi Ahmed, Linlin, Art and Howles, Many thanks to all of you. Regards Mirisage ... View more

Hi Art, Thank you. Sorry my question was not clear. Let me to simplyfy my question. In this data set, I actually have 7 distinct account_numbers as I have shown in green color. data A; input bank_number account_number $ 4-6; cards; 10 111 10 111 70 222 70 222 40 333 70 111 40 555 40 666 10 777 ; run; Bank_number        Account_number 10                                 111 10                                   777 40                                 333 40                                   555 40                                   666 70                                   111 70                                 222 Question: When I have run below code, it says there are only 6 distinct accounts (see the output inside the square below). proc sql; select count(distinct account_number) from a; quit; output 6 But acutally there are 7 distinct accounts. When I have run your code belwo also it says there are only 6 disticnt accounts whereas it should have siad there are 7. proc sql;   select account_number, count(account_number)     from a       group by account_number   ; quit; This is the output of above code 111 3 222 2 333 1 555 1 666 1 777 1 Question: So, how could I get the correct number which should be 7. Thanks Miris ... View more

Hi Patrick, This is a tremondous help as well as a very useful new knowledge for me. Thank you very much again!. Regards Mirisage ... View more

Hi Ksharp and ballardw, Thank you very much to both of you. Hi Ksharp, This code worked really well. Great relief. (Actually I have tried your example code provided in another posting too although I failed in that attmept). Hi ballardw, Thank you very much for your attachement too. Regards Mirisage ... View more

Hi Patrick and Art, Many thanks to both of you. I think this is the good time to show how inefficiently I have achieved what I wanted to achieve.  Using the data set (shown at the very bottom, can be run readily), I wanted to create 23 Markov Matrices/Tables for consecutive months, commencing Feb 2010 to Mar 2010 pair, culminating Dec 2011 to Jan 2012 pair. /*This is the first Table I generated using my novice code provided further down*/ Succession of O/S Balance ($ and %) and Accounts (#) from Feb 2010 to Mar 2010 prior_arrears_band Areeras_Band Current 60 - 90 N Balance Balance N Balance Balance Sum % Sum % Current 2 1571 ? . . ? 1 - 30 . . ? 1 800 ? /*This is the second Table I generated*/ Succession of O/S Balance ($ and %)) and Accounts (#) from Mar 2010 to Apr 2010 prior_arrears_band Areeras_Band Current 60 - 90 N Balance Balance N Balance Balance Sum % Sum % 30 - 60 1 200 ? . . ? NPNA . . ? 1 30 ? Next, ..... Apr 2010 to May 2010 table May 2010 to Jun 2010 table Jun  2010 to Jul  2010 table …………………………………………….. ………………………………………………… ………………………………………………. Dec 2011 to Jan 2012 table (this is the last table) This is the inefficient (but hopefully correct) approach I have taken. (Data set is provided at the very bottom that can be readily run). /*Sorting*/ proc sort data= RAW_DATA_SET_3 out=RAW_DATA_SET_3_sorted;    by bank_number account_number current_date; run; /*Isolating only accounts occur any day between 1Feb2010 and 31Mar2010 (consecutive pairs of 2 months). Note that some accounts may not occur in pairs*/ data Cycle_FEB_TO_MAR_2010; set RAW_DATA_SET_3_sorted;    if '1FEB2010'd le current_date le '31MAR2010'd; run; /*sorting again*/ proc sort data=Cycle_FEB_TO_MAR_2010 out= Cycle_FEB_TO_MAR_2010_; by bank_number account_number current_date; run; /*Getting rid of single records. In other words, isolating only consecutive pairs */ data Cycle_FEB_TO_MAR_2010_iso; set Cycle_FEB_TO_MAR_2010_; by bank_number account_number current_date; if not (first.account_number and last.account_number); run; /*getting the preceeding month's arrears_band into the succeeding month's row*/ data Cycle_FEB_TO_MAR_2010_iso; set Cycle_FEB_TO_MAR_2010_iso;      prior_arrears_band = lag( arrears_band ); run; /*Removing Preceeding month rows, or in other words, retaining succeeding month's row*/ /*This retained row now has both preceeding month's arrers band and succeeding month's arrears band*/ data Cycle_FEB_TO_MAR_2010_iso_;    set Cycle_FEB_TO_MAR_2010_iso;    if '1MAR2010'd le current_date le '31MAR2010'd; run; /*User defined format to control the column and row title orders*/ proc format ; value $ fmt (multilabel notsorted)   'Current'='Current'   '1 - 30'='1 - 30'   '30 - 60'='30 - 60'   '60 - 90'='60 - 90'   '90 +' = '90 +'    'NPNA'='NPNA'   'writoff' = 'writoff' ; run; /*This proc tabulate works but row title order not work*/ proc tabulate data= Cycle_FEB_TO_MAR_2010_iso_ order=formatted;    class prior_arrears_band /PRELOADFMT order=data; /*row CLASS statement*/    class arrears_band /PRELOADFMT order=data; /*column CLASS statement*/    var balance; table prior_arrears_band='', arrears_band*(n balance*sum)/ box=prior_arrears_band; format arrears_band $fmt.; /*applying the earlier defined format*/ Title 'Succession of O/S Balance ($ and %) and Accounts (#) from Feb 2010 to Mar 2010'; run; This is the final first table I generated (except balance % columns) using my novice code. Succession of O/S Balance ($ and %) and Accounts (#) from Feb 2010 to Mar 2010 prior_arrears_band Arreras_Band Current 60 - 90 N Balance Balance N Balance Balance Sum % Sum % Current 2 1571 ? . . ? 1 - 30 . . ? 1 800 ? Above markov table says: 2 accounts have cycled from “Current” bucket in FEB2010 (preceeding month) to “Current” bucket in MAR2010 (succeeding month).     The corresponding $ amount is $1,571 (i.e. 666+905) 1 account has cycled from “1-30” bucket in FEB2010 (preceeding month) to “60-90” bucket in MAR2010 (succeeding month).     The corresponding $ amount is $800  Next I have to repeat this exercise for the Mar 2010 to Apr 2010 cycle and so on, culminating Dec 2011 to Jan 2012 cycle. I know no body can read all these things, but appreciate if any help to make my code efficent, if time permits. This is dataset used. data RAW_DATA_SET_3; 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 1111111   31MAR2010 Personal Loan 555  30 - 60 10 1111111   30APR2010 Personal Loan 200  Current 10 1111111   31MAY2010 Personal Loan 108  1 - 30 30 1111111   31DEC2010 Res. Mortgage 800  Current 30 1111111   31JAN2011 Res. Mortgage 600  90 + 30 1111111   28FEB2011 Res. Mortgage 600  NPNA 30 1111111   17MAR2011 Res. Mortgage 600  writoff 20 333333333 15MAR2010 Personal OD   700  NPNA 20 333333333 30APR2010 Personal OD   30   NPNA 20 333333333 31MAY2010 Personal OD   2    NPNA 20 333333333 30JUN2010 Personal OD   1    NPNA 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 01JUL2010 Personal Loan 25   NPNA 10 444444444 30AUG2010 Personal Loan 32   NPNA 70 666666666 07FEB2010 Personal OD   999  Current 70 666666666 15MAR2010 Personal OD   666  Current 40 777777    08FEB2010 Res. Mortgage 1877 Current 40 777777    28MAR2010 Res. Mortgage 905  Current 80 777777    01FEB2010 Personal Loan 3000 90 + ; run;   Thanks Mirisae ... View more

Hi ballardw, The reason why the catelgorical columns are not coming in the order defined in proc format is becasue I have not defined them as exactly as appeard in the data set values (Thanks to Joe Matise for spotting this). Incorrect '1-30'='1-30'  correct (see the space up) '1 - 30'='1-30'  Thanks Mirisage ... View more

Hi Ballardw and Ksharp, Many thanks to both of you. Hi Ballardw I appplied your second suggestion (highlighted in yellow color in the following code). It works. thank you. Hi Ksharp, Many thanks for taking time to provide an example. I applied your "proc format" suggestion (please see belwo). But still the order of catelgorical columns are not coming in the order defined in proc format. Is there any problem in my proc format code or proc tabulate code? Also i saw you have intentionally leaft some space '1-30'='   1-30'. /*Format $ fmt. will be used in tabulate and will control the order in which the categorical    columns are laid out*/ proc format ; value $ fmt(multilabel notsorted) /*See note below */   'Current'='Current'   '1-30'='1-30'   '30-60'='30-60'   '60-90'='60-90'   'NPNA'='NPNA'     ; run; proc tabulate data=book3 order=formatted;   class prior_arrears_band;   class arrears_band /PRELOADFMT order=data;    var balance; table prior_arrears_band='', arrears_band*(n balance*sum)/ box=prior_arrears_band; format arrears_band $fmt.; run; /*Note: from literature: "Notsorted" option maintains the sort order as specified in the format and can be used in conjunction with the multilabel format option */ ... View more