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,
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.
Could you please explain your problem a bit more.
what is cove/hedge etc..
Please put some usuable sample data
More details:
What in the data tells that an id group is starting or ending?
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
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.
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).
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.
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.