Hi RichardADeVenezia, I tested your code and it works, but with corrections. Datasets need to be sorted and the sequence in set 2 must start over for each group gender,ageclass. So the correct code is: proc sort data=one;
by gender ageclass;
run;
proc sort data=two;
by gender ageclass
run;
data one_v;
set one;
by gender ageclass;
if first.ageclass then pair=0; else pair+1;
run;
data two_v;
set two;
by gender ageclass;
if first.ageClass then seq=0; else seq+1;
pair = floor(seq/2);
rowseq + 1;
run;
proc sql;
create table want as
select two.subset, two.gender, two.code, two.ageclass, one.code as assigned_code
from two_v as two
left join one_v as one
on one.gender=two.gender
& one.ageclass=two.ageclass
& one.pair=two.pair
order by rowseq
; I will mark your post as the solution but readers mind to read this post too because without the corrections the algorithm doesn't work. I'll use this post to share a solution I elaborated myself and hopefully to get your comments on it: /* inner loop, on dataset two */
%macro loop_two(code_v,gender_v,ageclass_v);
data two (drop = counter);
set two;
length assigned_code $25;
if _n_ = 1 then
counter = 0;
if (counter<4 & assigned_code = ' ' & gender = "&gender_v" & ageclass = "&ageclass_v") then
do;
assigned_code = "&code_v";
counter+1;
end;
run;
%mend;
/* outer loop, on dataset one */
data _null_;
set one;
call execute('%loop_two('||code||','||gender||','||ageclass||')' );
run; It works but it's slow, because it calls a macro for each observation in dataset one. I wonder if there is a faster way to do it with a double loop... A way to build an array in memory with a single read from dataset one, maybe with macro arrays? But macro arrays can only get a single column of a dataset, they can't take the whole dataset as a matrix, can they?
... View more
