Date:         Wed, 27 May 2009 21:59:49 +0100
Reply-To:     karma <dorjetarap@GOOGLEMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         karma <dorjetarap@GOOGLEMAIL.COM>
Subject:      Re: Hierarchical data - return all downstream nodes for a given
              parent
Comments: To: Tree Frog <tree.frog2@hotmail.com>
In-Reply-To:  <50c2d033-1a5c-477f-8ff5-dcd7a6bb9e8f@p21g2000prn.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1

Hi Tree Frog,

You didn't mention how you wanted your data to be displayed, so here
is a path string example.

HTH


data have ;
   input id subid ;
   infile datalines dsd ;
   cards ;
1,2
1,3
2,5
4,8
5,7
;;;

data want (drop = _:) ;
   length path $200 ;
   declare hash hh   (dataset: "have") ;
   hh.definekey  ("id") ;
   hh.definedata ("subid") ;
   hh.definedone () ;
   hh.output(dataset:'tt') ;
   set have ;
   _id = id ;
   _subid = subid ;
   do until(hh.find() ne 0) ;
      path = catx(',', path , subid) ;
      id   = subid ;
      end ;
   id    = _id ;
   subid = _subid ;
   run ;
proc print;run ;

output:


                                 Obs    path     id    subid

                                      1     2,5,7     1      2
                                      2     3         1      3
                                      3     5,7       2      5
                                      4     8         4      8
                                      5     7         5      7


2009/5/27 Tree Frog <tree.frog2@hotmail.com>:
> Hi there
>
> I have data (simplified below) representing a parent-child hierarchy.
>
> Given a single value of id as a parent, I need to determine the keys
> of all downstream children, in order to query another table.
>
> For example, in the case of id=1, the result should be 2,3,5 and 7,
> and for id=4 the result should be 8.
>
> Part of what's tricky here is that the ids in the actual data are
> guids, so there's no opportunity to rely on sorting of the data.  Nor
> would I know how many levels there are below a given parent.
>
> I've been reading up on crosswalks and hash tables.  Has anyone got
> any initial advice as I embark on this?
>
> data _source;
>  input id subid 8.;
>        infile datalines dsd;
>        datalines;
> 1,2
> 1,3
> 2,5
> 4,8
> 5,7
> ;;;
> run;
>