Help using Base SAS procedures

Create multiple cumulative subtotals

Accepted Solution Solved
Reply
New Contributor PC
New Contributor
Posts: 4
Accepted Solution

Create multiple cumulative subtotals

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,


Accepted Solutions
Solution
‎10-21-2013 12:48 PM
Super User
Posts: 5,516

Re: Create multiple cumulative subtotals

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.

View solution in original post


All Replies
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Create multiple cumulative subtotals

Could you please explain your problem a bit more.

what is cove/hedge etc..

Please put some usuable sample data

New Contributor PC
New Contributor
Posts: 4

Re: Create multiple cumulative subtotals

More details:

sas example.png

Super User
Posts: 11,343

Re: Create multiple cumulative subtotals

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

New Contributor PC
New Contributor
Posts: 4

Re: Create multiple cumulative subtotals

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

Solution
‎10-21-2013 12:48 PM
Super User
Posts: 5,516

Re: Create multiple cumulative subtotals

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.

New Contributor PC
New Contributor
Posts: 4

Re: Create multiple cumulative subtotals

Posted in reply to Astounding

Astounding,

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 281 views
  • 1 like
  • 4 in conversation