hi guys,
I need your help here please
i have this dataset (mylib.active_suscribers_grouped) below and i need to add two more fields to it (opening and closing balance ) grouped by calendar day.The closing balance =opening balance+Start+Restarted-Suspension-Termination and the opening balance is the closing balance from the previous day.The opening balance for 12/23/2012 is given to me.
Calendar_Day | Status | Sum |
12/23/2012 | Terminated | 1 |
12/24/2012 | Restarted | 2 |
12/24/2012 | Start | 3 |
12/24/2012 | Suspension | 4 |
12/24/2012 | Terminated | 5 |
12/25/2012 | Restarted | 6 |
12/25/2012 | Terminated | 7 |
so i need to get something like this:
Calendar_Day | Status | Sum | opening_balance | closing_balance |
12/23/2012 | Terminated | 1 | 5 | 4 |
12/24/2012 | Restarted | 2 | 4 | 0 |
12/24/2012 | Start | 3 | 4 | 0 |
12/24/2012 | Suspension | 4 | 4 | 0 |
12/24/2012 | Terminated | 5 | 4 | 0 |
12/25/2012 | Restarted | 6 | 0 | -1 |
12/25/2012 | Terminated | 7 | 0 | -1 |
and i am using this code below but i am not gettin what i am supposed to.Where i am wrong,please?
data mylib.active_suscribers_summed;
do _n_ = 1 by 1 until(last.calendar_day);
set mylib.active_suscribers_grouped;
by calendar_day;
if _n_=1 then do;
opening_balance=5;
if status='Restarted' or status='Start' then Closing_balance=sum(opening_balance,Sum);
else Closing_balance=sum(opening_balance,-Sum);
end;
if _n_ ne 1 then do;
opening_balance=lag(closing_balance);
if status='Restarted' or status='Start' then Closing_balance=sum(Opening_Balance,Sum);
else Closing_balance=sum(Opening_balance,-Sum);end;end;
do _n_ = 1 to _n_ ;
set mylib.active_suscribers_grouped;
output ;
end ;
run;
the only thing i am getting right is the opening/closing balance for 12/23/2012
Thanks
I think this might DOW what you need 😉
data raw ;
input cal_d status : $15. sumv ;
attrib cal_d label='Calendar Day' format= date9. informat = mmddyy10. ;
list;cards;
12/23/2012 Terminated 1
12/24/2012 Restarted 2
12/24/2012 Start 3
12/24/2012 Suspension 4
12/24/2012 Terminated 5
12/25/2012 Restarted 6
12/25/2012 Terminated 7
;
data d_bals ;
retain open_bal 5 close_bal ;
do n_trans = 1 by 1 until( last.cal_d ) ;
set raw ;
by cal_d ;
if status = 'Terminated' then t_term = sumv ;
else
if status = 'Restarted' then t_rest = sumv ;
else
if status = 'Suspension' then t_susp = sumv ;
else
if status = 'Start' then t_strt = sumv ;
else
do; putlog 'please explain:' _all_ ;
stop ;
end ;
end ;
close_bal = Sum( open_bal, t_strt, t_rest, 0)
- sum( t_susp, t_term, 0 ) ;
output ;
open_bal = close_bal ;
run ;
It provides just one row for each cal date, like
open_bal close_bal n_trans cal_d status sumv t_term t_rest t_susp t_strt
5 4 1 23DEC2012 Terminated 1 1 . . .
4 0 4 24DEC2012 Terminated 5 5 2 4 3
0 -1 2 25DEC2012 Terminated 7 7 6 . .
of course, you can add a keep statement to reduce the results to just the columns youy want, but I left them in to let you see the "workings"
I'm confused! Your code doesn't match your desired results. Is the following what you are trying to achieve?:
data active_suscribers_grouped;
informat Calendar_Day mmddyy10.;
format Calendar_Day mmddyy10.;
informat Status $10.;
input Calendar_Day Status Sum;
cards;
12/23/2012 Terminated 1
12/24/2012 Restarted 2
12/24/2012 Start 3
12/24/2012 Suspension 4
12/24/2012 Terminated 5
12/25/2012 Restarted 6
12/25/2012 Terminated 7
;
data active_suscribers_summed;
set active_suscribers_grouped;
retain opening_balance closing_balance;
if _n_=1 then do;
opening_balance=5;
if status='Restarted' or status='Start' then Closing_balance=sum(opening_balance,Sum);
else Closing_balance=sum(opening_balance,-Sum);
end;
else do;
opening_balance=closing_balance;
if status='Restarted' or status='Start' then Closing_balance=sum(Opening_Balance,Sum);
else Closing_balance=sum(Opening_balance,-Sum);
end;
run;
hey Arthur,
thanks for your response but no this is not what i need to achieve. I actually need to group the data by calendar day and find the closing and opening balance for each group not for each single transaction as your code does .So for 12/24/2012 i have opening balance 5 and the closing balance should be 5+2+3-4-5=4.
: Then I probably still don't understand what you are looking for as my results don't match the results shown in your example.
data active_suscribers_summed (drop=status);
set active_suscribers_grouped;
by Calendar_Day;
retain opening_balance closing_balance;
if first.Calendar_Day then opening_balance=5;
if status='Restarted' or status='Start' then Closing_balance=sum(opening_balance,Sum);
else Closing_balance=sum(opening_balance,-Sum);
if last.Calendar_Day then output;
run;
yes Arthur,i need something as your code is giving me except that the opening balance is the closing balance of the previous day.I tried the lag function but not working for me.I am doing it wrong
Thanks a lot for your code
in other words opening_balance=5 is only for 12/23/2012 then the opening balance for next day would be whatever the closing one for the current day is and so on
: My results still don't match your example, but I think that the following does what you describe:
data active_suscribers_summed (drop=status);
set active_suscribers_grouped;
by Calendar_Day;
retain opening_balance closing_balance;
if _n_ eq 1 then opening_balance=5;
if status='Restarted' or status='Start' then Closing_balance=sum(opening_balance,Sum);
else Closing_balance=sum(opening_balance,-Sum);
if last.Calendar_Day then do;
output;
opening_balance=closing_balance;
end;
run;
your code seems right but still dont know why it s not giving me the right values for the closing balance
this is the dataset:
Calendar_Day | Status | Sum |
12/23/2012 | Terminated | 1 |
12/24/2012 | Restarted | 2 |
12/24/2012 | Start | 3 |
12/24/2012 | Suspension | 4 |
12/24/2012 | Terminated | 5 |
12/25/2012 | Restarted | 6 |
12/25/2012 | Terminated | 7 |
and this is what i am supposed to get
date opening closing
12/23/2012 5 1
12/24/2012 4 0
12/25/2012 0 -1
12/23/2012 opening balance=5,closing balance=5-1=4
12/24/2012 opening blance =4 closing balance=(4+2+3-4-5)=0
12/25/2012 opening balance=0,closing balance=0+6-7=-1 (sorry made a typo in the previous post the top closing balance should read 4 not 1.
So the formulas seem right in the code but still dont get the right values
I think this might DOW what you need 😉
data raw ;
input cal_d status : $15. sumv ;
attrib cal_d label='Calendar Day' format= date9. informat = mmddyy10. ;
list;cards;
12/23/2012 Terminated 1
12/24/2012 Restarted 2
12/24/2012 Start 3
12/24/2012 Suspension 4
12/24/2012 Terminated 5
12/25/2012 Restarted 6
12/25/2012 Terminated 7
;
data d_bals ;
retain open_bal 5 close_bal ;
do n_trans = 1 by 1 until( last.cal_d ) ;
set raw ;
by cal_d ;
if status = 'Terminated' then t_term = sumv ;
else
if status = 'Restarted' then t_rest = sumv ;
else
if status = 'Suspension' then t_susp = sumv ;
else
if status = 'Start' then t_strt = sumv ;
else
do; putlog 'please explain:' _all_ ;
stop ;
end ;
end ;
close_bal = Sum( open_bal, t_strt, t_rest, 0)
- sum( t_susp, t_term, 0 ) ;
output ;
open_bal = close_bal ;
run ;
It provides just one row for each cal date, like
open_bal close_bal n_trans cal_d status sumv t_term t_rest t_susp t_strt
5 4 1 23DEC2012 Terminated 1 1 . . .
4 0 4 24DEC2012 Terminated 5 5 2 4 3
0 -1 2 25DEC2012 Terminated 7 7 6 . .
of course, you can add a keep statement to reduce the results to just the columns youy want, but I left them in to let you see the "workings"
yea it works.Thanks Peter .I still wonder why Arthur's code was not giving me the right closing balances when the code seemed so perfect.But thanks again to you two guys.Great job
: Glad you have code now that does what you want. My code didn't do what you were expecting because I didn't understand that opening_balance was supposed to be updated with each transaction. As such, you really don't even need opening balance I think. Try the following:
data active_suscribers_summed (drop=status);
set active_suscribers_grouped;
by Calendar_Day;
retain closing_balance;
if _n_ eq 1 then Closing_balance=5;
if status='Restarted' or status='Start' then Closing_balance=sum(Closing_balance,Sum);
else Closing_balance=sum(Closing_balance,-Sum);
if last.Calendar_Day then output;
run;
yea,the opening balance is actually the closing balance from the previous day which is a calculation of all the transaction within a given day
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.