Hi @anandbillava
Here is a different approach where splitting the input data set is avoided. I think it gives the wanted output.
Orphans are identified by a SQL join, where the input data set is joined by itself with match on user as as a join criteria, so it holds childs and parents with the same user together.
Ancestors are handled by a format to set parents on childs, and this it done in a loop, until the chain is exhausted. It makes use of extra variables, where user-base, resp user-cur, are concatenated, so user becomes part of the key.
/* test data 1 */
data have1;
format user 8. cur base 10.1;
user=0; base=5.1; cur=5.5; output;
user=0; base=5.2; cur=5.5; output;
user=0; base=5.0; cur=5.2; output;
user=0; base=4.7; cur=5.0; output;
user=0; base=4.6; cur=5.2; output;
user=0; base=.; cur=4.6; output;
user=2; base=5.4; cur=6.1; output;
user=2; base=5.3; cur=6.1; output;
user=2; base=5.0; cur=5.3; output;
user=2; base=4.7; cur=5.0; output;
user=2; base=4.5; cur=4.7; output;
run;
/* find orphans */
proc sql;
create table orphans as
select a.user, coalesce(a.base,a.cur) as orphan format=3.1
from have1 as a left join have1 as b
on a.user = b.user and (b.cur = a.base or b.cur = .)
where b.user = .;
quit;
/* test data 2 */
data have2;
format user 8. cur base 10.1;
user=1; base=4.1; cur=4.2; output; /* 4.1 to 4.2 - Ancestor here is 4.4 */
user=1; base=4.2; cur=4.4; output; /* 4.2 to 4.4 - Ancestor here is 4.4 */
user=1; base=4.6; cur=5.5; output; /* 4.6 to 5.5 - Ancestor here is 6.1 */
user=1; base=5.5; cur=6.1; output; /* 5.5 to 6.1 - Ancestor here is 6.1 */
user=2; base=4.2; cur=4.6; output; /* 4.2 to 4.6 - Ancestor here is 6.1 */
user=2; base=4.6; cur=5.5; output; /* 4.6 to 5.5 - Ancestor here is 6.1 */
user=2; base=5.5; cur=6.1; output; /* 5.0 to 6.1 - Ancestor here is 6.1 */
user=3; base=4.0; cur=4.2; output; /* 4.0 to 4.2 - Ancestor here is 5.5 */
user=3; base=4.2; cur=4.6; output; /* 4.2 to 4.6 - Ancestor here is 5.5 */
user=3; base=4.6; cur=5.5; output; /* 4.6 to 6.1 - Ancestor here is 5.5 */
run;
/* Find ancestors */
data w; set have2 end=eof;
type = 'C'; hlo=''; fmtname = 'par';
start = put(user,z8.)||'-'||put(base,z10.1);
label = put(user,z8.)||'-'||put(cur,z10.1);
output;
if eof then do;
hlo = 'O'; label='';
output;
end;
run;
proc format cntlin=w;
run;
data ancestors;
drop type hlo fmtname start label c w inter;
set w (where=(hlo=''));
format ancestor 10.1;
c = 0;
do until (w = .);
c = c + 1;
if c = 1 then inter = put(start,$par.); else inter = put(inter,$par.);
w = input(scan(inter,2,'-'),10.1);
if w ne . then ancestor = w;
end;
run;
... View more