topic Re: dow loops in SAS Procedures

dow loops

Tal — Sat, 19 Jan 2013 01:36:56 GMT

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

Re: dow loops

art297 — Sat, 19 Jan 2013 03:45:51 GMT

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;

Re: dow loops

Tal — Sat, 19 Jan 2013 19:20:59 GMT

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.

Re: dow loops

art297 — Sat, 19 Jan 2013 20:09:51 GMT

: 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;

Re: dow loops

Tal — Sat, 19 Jan 2013 22:37:21 GMT

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

Re: dow loops

Tal — Sat, 19 Jan 2013 22:51:24 GMT

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

Re: dow loops

art297 — Sat, 19 Jan 2013 23:37:49 GMT

: 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;

Re: dow loops

Tal — Sun, 20 Jan 2013 19:29:07 GMT

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

Re: dow loops

Tal — Sun, 20 Jan 2013 19:35:15 GMT

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

Re: dow loops

Peter_C — Sun, 20 Jan 2013 19:38:42 GMT

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"

Re: dow loops

Tal — Sun, 20 Jan 2013 19:44:27 GMT

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

Re: dow loops

art297 — Sun, 20 Jan 2013 20:39:59 GMT

: 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;

Re: dow loops

Tal — Mon, 21 Jan 2013 00:04:29 GMT

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