Please see data as suggested. I think it worked out. But I'm curious if any comments. Does it below linkage make sense if i needed impose certain conditions on the linkage? @art297 /*MENTORS*/
data mentor;
input mentor_ID $User_X User_Y group age gender;
cards;
U1 53.8 -32.1 0 23 1
U2 23.8 -96.3 1 25 2
U3 34.5 -70.7 0 28 1
U4 28.7 -76.5 1 35 2
;
/*STUDENTS*/
data students;
input student_ID $ User_X User_Y group age gender;
cards;
A1 53.6 -32.1 1 63 1
A2 35.6 -12.3 0 25 2
A3 63.4 -85.4 1 69 1
A4 34.5 -70.9 0 45 2
A5 37.8 -77.7 1 55 1
;
proc sql;
create table distance(drop=x1 x2 y1 y2 user_x user_y) as
select t1.*,
t1.user_x as x1,
t1.user_y as y1,
t2.user_x as x2,
t2.user_y as y2,
t2.student_id,
t2.age as student_age,
t2.gender as student_gender,
t2.group as student_group,
geodist(x1, y1, x2, y2) as distance
from mentor t1, students t2
order by mentor_id, calculated distance
/* group by t1.mentor_id */
/* having (calculated distance)=calculated distance */
;
quit;
data want(keep=mentor_ID group age gender student_ID distance student_age student_gender student_group);
array students(999) $;
set distance;
retain i needone students:;
by mentor_id;
if _n_ eq 1 then i=0;
if first.mentor_id then needone=1;
if needone then do;
if student_id not in students then do;
i+1;
needone=0;
students(i)=student_id;
output;
end;
end;
run;
/*MENTORS*/
data mentor;
input mentor_ID $User_X User_Y group age gender;
cards;
U1 53.8 -32.1 0 23 1
U2 23.8 -96.3 1 25 2
U3 34.5 -70.7 0 28 1
U4 28.7 -76.5 1 35 2
;
/*STUDENTS*/
data students;
input student_ID $ User_X User_Y group age gender;
cards;
A1 53.6 -32.1 1 63 1
A2 35.6 -12.3 0 25 2
A3 63.4 -85.4 1 35 2
A4 34.5 -70.9 0 45 2
A5 37.8 -77.7 1 25 2
;
proc sql;
create table distance1 as
select t1.*,
t1.user_x as x1,
t1.user_y as y1,
t2.user_x as x2,
t2.user_y as y2,
t2.student_id,
t2.age as student_age,
t2.gender as student_gender,
t2.group as student_group,
geodist(x1, y1, x2, y2) as distance
from mentor t1 join students t2
on t1.age=t2.age and t1.group=t2.group and t1.gender=t2.gender
order by mentor_id, calculated distance
/* group by t1.mentor_id */
/* having (calculated distance)=calculated distance */
;
quit;
data want1 (keep=mentor_id student_id distance age group gender student_age student_gender student_group);
array students(999) $;
set distance1;
retain i needone students:;
by mentor_id;
if _n_ eq 1 then i=0;
if first.mentor_id then needone=1;
if needone then do;
if student_id not in students then do;
i+1;
needone=0;
students(i)=student_id;
output;
end;
end;
run;
proc print data=want;
title "Linked by nearest distance";
run;
proc print data=want1;
title "Linked by nearest distance given other conditions met";
run;
... View more
