@kiranv_,
A curious puzzle. The wrinkle is the order in which you want to collect the values from the dupes. Normally, one would fix the first dupe value of the first dupe and collect the rest, then fix the second and collect the rest, and so on. The difference between this "usual" order and your order can be illustrated as follows:
Usual order Your order
----------- ----------
data _null_ ; data _null_ ;
do i = 1 to 2 ; do j = 1 to 2 ;
do j = 1 to 2 ; do i = 1 to 2 ;
put i= j= ; put i= j= ;
end ; end ;
end ; end ;
run ; run ;
------- -------
i=1 j=1 i=1 j=1
i=1 j=2 i=2 j=1
i=2 j=1 i=1 j=2
i=2 j=2 i=2 j=2 Hence, if nested loops are to be used for the task (as done below), they need to be inverted similarly to the above. You can run my code directly against your sample input (and will get the result you want), but for the sake of wider generality I've prepared my own, so that a case with double and triple dupes could be tested - otherwise one can't be sure that the algorithm is valid.
data have ;
input id val1 $ val2 $ ;
cards ;
1 111 AAA
1 222 BBB
1 333 BBB
1 444 CCC
1 555 DDD
1 666 EEE
1 777 EEE
1 888 EEE
1 999 FFF
2 000 AAA
2 111 AAA
2 222 BBB
2 333 CCC
2 444 CCC
2 555 DDD
2 666 EEE
2 777 FFF
2 888 FFF
2 999 GGG
;
run ;
Since we'll need to assemble nested loops, it's convenient to determine the max nesting level (and the size of the needed arrays, which is the same) beforehand, rather than hard code:
proc sql noprint ;
select cats (max (D)) into:D from (select count (id) as D from have group id) ;
select cats (max (L)) into:L from (select length (val1) as L from have) ;
quit ;
Now we need to assemble the nested loop based on these findings:
%macro loop (dim, arrv, arrp, arrs, jvar, csv) ;
%local i ;
array _x [&dim] ;
%do i = &dim %to 1 %by -1 ;
do _x&i = &arrp [1,&i] to &arrp [2,&i] ;
%end ;
do _i = 1 to &jvar ;
&arrs [_i] = &arrv [_x[_i]] ;
end ;
&csv = catx (",", of &arrs[*]) ;
output ;
%do i = &dim %to 1 %by -1 ;
end ;
%end ;
%mend ; After this prelim work is done, the rest is relatively easy:
data want (keep = id val) ;
array v [ &d] $ &L _temporary_ ;
array s [ &d] $ &L _temporary_ ;
array p [2, &d] _temporary_ ;
do _n_ = 1 by 1 until (last.id) ;
set have ;
by id val2 ;
v [_n_] = val1 ;
if first.val2 then do ;
_j = sum (_j, 1) ;
p [1, _j] = _n_ ;
end ;
if last.val2 then p [2, _j] = _n_ ;
end ;
do _n_ = _j + 1 to &d ;
p [1, _n_] = 0 ;
p [2, _n_] = 0 ;
end ;
length val $ %eval (&L * &D + &D - 1) ;
call missing (of s[*]) ;
%loop (&d, v, p, s, _j, val)
run ; The idea is based on keeping track of the endpoints of each consecutive group of VAL2 values. Above, this is done using the 2-dimensional array P. If the value VAL2 in position X is unique, the endpoints P[1,X]=P[2,X], so the corresponding loop level will iterate but once; otherwise, they are not equal and the range in between determines the respective number of iterations. Thus, the logic is the same whether it be a dupe or a singleton. It is assumed that the input is sorted by ID, VAL2. The rest is just a matter or rather simple diligent coding:
data want (keep = id val) ;
array v [ &d] $ &L _temporary_ ;
array s [ &d] $ &L _temporary_ ;
array p [2, &d] _temporary_ ;
do _n_ = 1 by 1 until (last.id) ;
set have ;
by id val2 ;
v [_n_] = val1 ;
if first.val2 then do ;
_j = sum (_j, 1) ;
p [1, _j] = _n_ ;
end ;
if last.val2 then p [2, _j] = _n_ ;
end ;
do _n_ = _j + 1 to &d ;
p [1, _n_] = 0 ;
p [2, _n_] = 0 ;
end ;
length val $ %eval (&L * &D + &D - 1) ;
call missing (of s[*]) ;
%loop (&d, v, p, s, _j, val)
run ; To give you a better idea what kind of code is actually run, here's what the macro would assemble if the longest ID by-group on file had 4 records:
array _x [4] ;
do _x4 = p [1,4] to p [2,4] ;
do _x3 = p [1,3] to p [2,3] ;
do _x2 = p [1,2] to p [2,2] ;
do _x1 = p [1,1] to p [2,1] ;
do _i = 1 to _j ;
s [_i] = v [_x[_i]] ;
end ;
val = catx (",", of s[*]) ;
output ;
end ;
end ;
end ;
end ; HTH
Paul D.
