Hi Team,
I have a data since 2007 and trying to calculate the cumulative /running totals by Clnt and Month.
Not sure the last record is not populating correctly..
Below is the data i got from the below code...
| CLNT_I | TXCT_MTH | NP_Amount | IP_Amount | WB_Amount | WO_Amount | Net_Position | Cum_Netposition |
| 98 | 31Jan2009 | 0 | 0 | -50000 | 0 | -50000 | -50000 |
| 87 | 30Sep2008 | 33547.97 | 0 | 0 | 33547.97 | 0 | 0 |
| 930 | 31May2017 | 0 | 50000 | 0 | 0 | 50000 | 50000 |
| 930 | 30Jun2017 | 0 | 0 | 0 | 0 | 0 | 50000 |
| 269 | 31Aug2011 | 370000 | 0 | 0 | 0 | 370000 | 370000 |
| 269 | 31Oct2011 | 0 | 170000 | 0 | 0 | 170000 | 540000 |
| 269 | 30Sep2012 | 0 | 408276.75 | 0 | 0 | 408276.75 | 948276.75 |
| 269 | 31May2013 | 0 | 306000 | 0 | 0 | 306000 | 1254276.75 |
| 269 | 30Jun2013 | 0 | 0 | 0 | 857545.91 | -857545.91 | 396730.84 |
| 269 | 30Jun2014 | 0 | 0 | -101616.63 | 295114.21 | -396730.84 | -5.82077E-11 |
I have calculated the Net position using the below logic.
calculated NP + calculated IP + calculated WB- calculated WO) as Net_Position
Not sure what is missing from the below code to calculating the running totals on the Net_Position.
proc sort data= Farlo_IAP_Clients_M nodupkey ; by TIAM_CLNT_I TXCT_MTH ;run;
data Farlo_IAP_Clients_C;
retain Cum_Netposition Cum_IAP_Amount Cum_IAP_Increase Cum_TotWO_Amount Cum_WriteOFF_Amount Cum_IFRWriteOFF_Amount
Cum_WriteBack_Amount Cum_DWriteOff_Amount ;
set Farlo_IAP_Clients_M;
by TIAM_CLNT_I TXCT_MTH;
if first.TIAM_CLNT_I and first.TXCT_MTH then
cum_Netposition=0; else cum_Netposition = cum_Netposition+(Net_position);
if first.TIAM_CLNT_I and first.TXCT_MTH then
cum_IAP_Amount=0;
cum_IAP_Amount+IAP_Amount;
if first.TIAM_CLNT_I and first.TXCT_MTH then
cum_IAP_Increase=0;
cum_IAP_Increase+IAP_Increase;
if first.TIAM_CLNT_I and first.TXCT_MTH then
Cum_TotWO_Amount=0;
Cum_TotWO_Amount+Total_WritOffAmount ;
if first.TIAM_CLNT_I and first.TXCT_MTH then
Cum_WriteOFF_Amount=0;
Cum_WriteOFF_Amount+WriteOFF_Amount ;
if first.TIAM_CLNT_I and first.TXCT_MTH then
Cum_IFRWriteOFF_Amount=0;
Cum_IFRWriteOFF_Amount+IFRWriteOFF_Amount ;
if first.TIAM_CLNT_I and first.TXCT_MTH then
Cum_WriteBack_Amount=0;
Cum_WriteBack_Amount+WriteBack_Amount ;
if first.TIAM_CLNT_I and first.TXCT_MTH then
Cum_DWriteOff_Amount=0;
Cum_DWriteOff_Amount+DWriteOff_Amount;
run;
proc sort data= Farlo_IAP_Clients_C; by TIAM_CLNT_I TXCT_MTH; run;
Aprreciate your help.thanks
Regards
RS
