Help using Base SAS procedures

dow loops

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 444
Accepted Solution

dow loops

hi guys,

I need your help here please Smiley Happy

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_DayStatusSum
12/23/2012Terminated1
12/24/2012Restarted2
12/24/2012Start3
12/24/2012Suspension4
12/24/2012Terminated5
12/25/2012Restarted6
12/25/2012Terminated7

so i need to get something like this:

Calendar_DayStatusSumopening_balanceclosing_balance
12/23/2012Terminated154
12/24/2012Restarted240
12/24/2012Start340
12/24/2012Suspension440
12/24/2012Terminated540
12/25/2012Restarted60-1
12/25/2012Terminated70-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 Smiley Happy


Thanks


Accepted Solutions
Solution
‎01-20-2013 02:38 PM
Valued Guide
Posts: 2,177

Re: dow loops

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"

View solution in original post


All Replies
PROC Star
Posts: 7,491

Re: dow loops

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;

Super Contributor
Super Contributor
Posts: 444

Re: dow loops

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.

PROC Star
Posts: 7,491

Re: dow loops

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

Super Contributor
Super Contributor
Posts: 444

Re: dow loops

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 Smiley Happy

Thanks a lot for your code

Super Contributor
Super Contributor
Posts: 444

Re: dow loops

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

PROC Star
Posts: 7,491

Re: dow loops

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

Super Contributor
Super Contributor
Posts: 444

Re: dow loops

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_DayStatusSum
12/23/2012Terminated1
12/24/2012Restarted2
12/24/2012Start3
12/24/2012Suspension4
12/24/2012Terminated5
12/25/2012Restarted6
12/25/2012Terminated7

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

Super Contributor
Super Contributor
Posts: 444

Re: dow loops

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

Solution
‎01-20-2013 02:38 PM
Valued Guide
Posts: 2,177

Re: dow loops

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"

Super Contributor
Super Contributor
Posts: 444

Re: dow loops

yea it works.Thanks Peter Smiley Happy .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 Smiley Happy

PROC Star
Posts: 7,491

Re: dow loops

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

Super Contributor
Super Contributor
Posts: 444

Re: dow loops

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 361 views
  • 3 likes
  • 3 in conversation