How to find great grand parents in the dataset

[mkeintz]

Here is how I would do it in SQL, although I prefer the hash approach:

proc sql;
  create view _3gens as select h.*, h2.parent as grandparent 
   from hierarchy as h
   inner join 
        hierarchy as h2
   on h.parent=h2.child;

  create table want as select _3gens.*, h3.parent as greatgrandparent
   from _3gens
   inner join
       hierarchy as h3
  on _3gens.grandparent=h3.child;
quit;

Or, if you want to do it all in one statement:

proc sql;
  create table want as select _3gens.*,h3.parent as greatgrandparent from
   (select h.*, h2.parent as grandparent from
      hierarchy as h   inner join 
      hierarchy as h2  on h.parent=h2.child) 
   as _3gens
   inner join
     hierarchy as h3
  on _3gens.grandparent=h3.child;
quit;

But imagine how ugly this would look if you wanted, say 6 generations.  The hash coding approach would be far easier to expand.