BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Peter_C
Rhodochrosite | Level 12

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

12 REPLIES 12
art297
Opal | Level 21

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;

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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.

art297
Opal | Level 21

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

art297
Opal | Level 21

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Peter_C
Rhodochrosite | Level 12

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"

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

art297
Opal | Level 21

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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