My question is that I want to compute the following situation.
There are sale volume from customers which can vary in size. Each sale volume will be put into a box but the box size is fixed. If the volume is smaller, more than one sale orders can fill in the same box. If it's larger it will fit in several boxes until the over-size amount is gone. The problem is that how can I create the box number which can be counted continuously.
Assume that the size of each box is 2000. The sample data looks like this.
#Case 1: Volume is smaller than box size
Arriving Volume | Volume filled in each box | Accum Volume | Box Number |
400 | 400 | 400 | 1 |
1000 | 1000 | 1400 | 1 |
1500 | 600 | 2000 | 1 |
900 | 900 | 2 | |
300 | 300 | 1200 | 2 |
.... |
#Case 2: Volume is larger than box size
Arriving Volume | Volume filled in each box | Accum Volume | Box Number |
5500 | 2000 | 2000 | 1 |
2000 | 2000 | 2 | |
1500 | 1500 | 3 | |
1300 | 500 | 2000 | 3 |
800 | 800 | 4 | |
600 | 600 | 1400 | 4 |
... | ... | .. |
The data I have is just the arriving volume (1st colume) and the box size. Each item will be treated separately.
I have this:
Obs Item Volume
1 1 400
2 1 1000
3 1 1500
4 1 300
....
101 2 5500
102 2 1300
103 2 600
....
I want this.
Obs Item Volume Accum Box#
1 1 400 400 1
2 1 1000 1400 1
3 1 1500 2000 1 <= 600 in box#1
4 1 900 2 <= 900 in box#2
5 1 300 1200 2
....
101 2 5500 2000 1 <= fill up several boxes
102 2 2000 2
103 2 1500 3 <= remaining portion in box#3
104 2 1300 2000 3 <= fill up with 500
105 2 800 4 <= remaining in box#4
103 2 600 1400 4
I attempt to create the accumulated volume in a box but it cannot handle the data correctly, especially in the second case. I cannot insert additional rows in the table and keep on counting the number of box correctly.
Could anyone kindly suggest how to handle this problem please? Your answer is very much appreciated.
I think you can do it if you retain the total and the difference.
Also, please post your data in a form that can be used for data step.
data have;
input obs Item Volume;
datalines;
1 1 400
2 1 1000
3 1 1500
4 1 300
101 2 5500
102 2 1300
103 2 600
;
run;
data want;
set have;
by item;
retain accum dif box;
if first.item then do;
Accum=0;
box=1;
end;
Accum=sum(Volume,Accum);
do until(Accum<2000);
if Accum>2000 then do;
dif=Accum-2000;
Accum=2000;
output;
Volume=.;
Accum=dif;
box+1;
if Accum<2000 then output;
end;else
do;
output;
end;
end;
drop dif;
run;
Hello,
Thank you so much for your suggestion. It works perfectly as needed. I am sorry for an inconvenience caused with the dataset.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.