Assign repeating locations/values for a variable

hvins — Fri, 06 Sep 2019 20:26:14 GMT

I am attempting to write a program that will assign items a location in 5 row by 5 column boxes. The number of items will vary by the shipment, and I want the program to be able to adapt to whatever that total number of items is.

Using the code below, I have been successful at assigning the proper number of boxes I will need (25 item per box, counting up the number of boxes until all items have a box assigned) however, I am having trouble with the rows and columns. Once a new box starts and the number of respective rows and columns hit 5, I need the values to start back at 1 again for the new box. What I have below just continues the numbering to 6, 7, 8, etc.

data shipping_location;
set manifest;

shipping_box = .;
shipping_row = .;
shipping_col = .;

do b = 1 to _n_;
	if shipping_box = . then do;
		if _n_ =< 81*b then shipping_box = b;

		do r = 1 to _n_;
		if shipping_row = . then do;
			if _n_ =< 9*r then shipping_row = r;

				do c = 1 to _n_; 
					if shipping_col = . then do;
						if _n_ =< c then shipping_col = c;
					
					end;
				end;
			end;
		end;
	end;
end;
run;

I want the output to look like this:

Item	Box	Row	Col
A	1	1	1
B	1	1	2
C	1	1	3
D	1	1	4
E	1	1	5
F	1	2	1
G	1	2	2
H	1	2	3
I	1	2	4
J	1	2	5
K	1	3	1
L	1	3	2
M	1	3	3
N	1	3	4
O	1	3	5
P	1	4	1
Q	1	4	2
R	1	4	3
S	1	4	4
T	1	4	5
U	1	5	1
V	1	5	2
W	1	5	3
X	1	5	4
Y	1	5	5
Z	2	1	1
AA	2	1	2
BB	2	1	3
CC	2	1	4
DD	2	1	5
EE	2	2	1
FF	2	2	2
GG	2	2	3
HH	2	2	4
II	2	2	5

Thank you!

Re: Assign repeating locations/values for a variable

ballardw — Fri, 06 Sep 2019 21:14:41 GMT

Please see how close this comes

data want;
   set have;
   col = mod(_n_-1,5)+1;
   row = mod((floor((_n_-1)/5)+1)-1,5)+1;
   box = mod( floor((_n_-1)/25 +1),25);
run;

Re: Assign repeating locations/values for a variable

hashman — Fri, 06 Sep 2019 21:39:33 GMT

@hvins;

Simple arithmetic:

data want ;                   
  set have ;                  
  box = ceil (_n_ / 25) ;     
  row = ceil (_n_ /  5) ;     
  col = 1 + mod (_n_ - 1,  5) ;
run ;

Kind regards

Paul D.

topic Re: Assign repeating locations/values for a variable in SAS Programming

Assign repeating locations/values for a variable

Re: Assign repeating locations/values for a variable

Re: Assign repeating locations/values for a variable