How to create the counting number with accumulative volume in the dataset?

ta_na_ka — Sun, 13 Jun 2021 14:25:44 GMT

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.

Re: How to create the counting number with accumulative volume in the dataset?

japelin — Mon, 14 Jun 2021 00:36:10 GMT

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;

Re: How to create the counting number with accumulative volume in the dataset?

ta_na_ka — Thu, 17 Jun 2021 08:04:37 GMT

Hello,

Thank you so much for your suggestion. It works perfectly as needed. I am sorry for an inconvenience caused with the dataset.

topic Re: How to create the counting number with accumulative volume in the dataset? in SAS Programming

How to create the counting number with accumulative volume in the dataset?

Re: How to create the counting number with accumulative volume in the dataset?

Re: How to create the counting number with accumulative volume in the dataset?