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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.