You want the "desired" to start from 1. You also desire 2 or 3 groups within each RANK given in your HAVE data set. Here is a Data Step for SUB SPLITTING. If the RANK numbers is less than 6 I made a split of 2 groups otherwise 3 groups. With your example data set, two groups are made. I have added another 6 rows to show for a split of 3 groups.
data have;
input Test1 :$8. Test2 :$2. Rank;
datalines;
Apple dd 1
Ball dd 1
Cat dd 1
Dog dd 1
Elephant dd 2
dsd dd 2
sdf dd 2
asdf e 3
asdf e 3
asdf e 3
adsf e 3
;
run;
The following program splits into 2 groups:
data want;
do _n_ = 1 by 1 until(last.rank);
set have;
by rank;
end;
ngroups = ceil(ifN(_n_ >= 6, _n_/3, _n_/2));
do _n_ = 1 by 1 until(last.rank);
set have;
by rank;
if _n_ <= ngroups then desired = 1;
else desired + mod(_n_, ngroups);
output;
end;
drop ngroups;
run;
The statement:
ngroups = ceil(ifN(_n_ >= 6, _n_/3, _n_/2));
computes the desired number of rows to be present in each split. The following statements
if _n_ <= ngroups then desired = 1;
else desired + mod(_n_, ngroups);
creates the values for "desired" based on the number of observations per RANK.
Is this what you want?
Here is the additional observations. The same program works.
data have;
input Test1 :$8. Test2 :$2. Rank;
datalines;
Apple dd 1
Ball dd 1
Cat dd 1
Dog dd 1
Elephant dd 2
dsd dd 2
sdf dd 2
asdf e 3
asdf e 3
asdf e 3
adsf e 3
aaa g 4
aaa g 4
aaa g 4
aaa g 4
aaa g 4
aaa g 4
;
run;
The output of this:
Obs Test1 Test2 Rank desired
1 Apple dd 1 1
2 Ball dd 1 1
3 Cat dd 1 2
4 Dog dd 1 2
5 Elephant dd 2 1
6 dsd dd 2 1
7 sdf dd 2 2
8 asdf e 3 1
9 asdf e 3 1
10 asdf e 3 2
11 adsf e 3 2
12 aaa g 4 1
13 aaa g 4 1
14 aaa g 4 2
15 aaa g 4 2
16 aaa g 4 3
17 aaa g 4 3
... View more
