Create multiple cumulative subtotals

[PC]

Hello All,

I have this dataset:

Case	Amount	Need1	Need2	Need3
1	30	20	15	10
2	12	20	15	10
3	10	20	15	10
4	8	20	15	10
5	5	20	15	10
6	2	20	15	10

And need your help to obtain Cumulative_subtotal and Cumulative_subtotal_id fields:

Case	Amount	Need1	Need2	Need3	Cumulative_subtotal	Cumulative_subtotal_id
1	30	20	15	10	30	1
2	12	20	15	10	12	2
3	10	20	15	10	22	2
4	8	20	15	10	8	3
5	5	20	15	10	13	3
6	2	20	15	10	2	0

Create multiple cumulative subtotals of amount that cover/hedge Needi.

Cumulative_subtotal=30>=Need1=20 end of id=1;

Cumulative_subtotal=22>=Need2=15 end of id=2;

Cumulative_subtotal=13>=Need3=10 end of id=3;

Cumulative_subtotal_id=0 means not needed.

Thank you for your help,

[Mit]

Could you please explain your problem a bit more.

what is cove/hedge etc..

Please put some usuable sample data

[PC]

More details:

[ballardw]

What in the data tells that an id group is starting or ending?

[PC]

An id identifies a subtotal: amounts that fill needi must have id=i. The id=i  ends when amount is no longer required for fill needi.

Thanks

[Astounding · Accepted solution]

Your explanation helps.  Here is one possibility.

data want;

   cumulative_subtotal=0;

   retain cumulative_subtotal_id 1;

   array need {3} ;

   if cumulative_subtotal_id = 0 then total_need = 9999999999999999;

   else total_need = need{cumulative_subtotal_id};

   do until (cumulative_subtotal >= total_need);

      set have;

      cumulative_subtotal + amount;

      output;

   end;

   cumulative_subtotal_id + 1;

   if cumulative_subtotal_id > 3 then cumulative_subtotal_id=0;

run;

It's untested code, so you may need to tweak it.  But it should have enough working pieces.  Usually when a program is this clunky there is an easier way.  I'm just not seeing it right now.

Good luck.

[PC]

Astounding,

Thank you for your help. Works perfectly in my example . I just did a very small adjustment for cases when I have Needi=0 (ex. Need2=0, no amount is necessary to fill).