Dave,

Presuming that a "Reference" can only exist once in your tree, build a format table that translates/formats the value of Reference to ParentReference. Then use a loop to traverse the tree until you either get to the value you are searching for (e.g., DSQNEW) or the top of the tree. When/if you get to DSQNEW, then output.

Consider the following (UNTESTED) code:

Data makeFormat; Set fulltree; Fmtname = '$parent'; Type = 'C'; If ParentReference = ' ' then ParentReference = '-TOP-'; Rename Reference = start ParentReference = label; Run;

Data branch; Set fulltree; Traverse = Reference; Do until(Traverse = 'DSQNEW' or Traverse = '-TOP=-'); Traverse = put(Traverse,$parent.); End; If Traverse = 'DSQNEW'; Run;

Hope this helps and that the code/logic is close (it is late here on the East Coast of the US) :-).

Regards, -don h

> -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of David > Birch > Sent: Friday, November 03, 2006 12:35 AM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Pruning a Hierarchy > > Listers, > > I have a dataset that represents an organisational structure chart as a > reflexively joined entity - i.e. a classic hierarchy tree. I wish to > subset that data so that I have only one specific branch of that tree. > For example: > > data FullTree; > input Reference $ ParentReference $ ; > datalines; > DISQ > DSQNEW DISQ > DSQPOL DSQNEW > PO111 DSQPOL > PO100 DSQPOL > DSQCES DSQNEW > CE510 DSQCES > DSQASR DSQNEW > ASCO DSQASR > AS410 ASCO > AS411 ASCO > ASGBR DSQASR > AS420 ASGBR > DSQWOD DSQNEW > WD518 DSQWOD > WD519 DSQWOD > DSQEXD DSQNEW > DSQ DISQ > DODG DSQ > DODG000 DODG > DODG100 DODG > DES DSQ > DIR DSQ > OLDDSQ DISQ > NQR OLDDSQ > NQR000 NQR > WBR OLDDSQ > BLOCKED DISQ > UNASSIGN BLOCKED > BKDCORP BLOCKED > BKDDODG BLOCKED > A0000 PO100 > A0001 PO100 > B0001 UNASSIGN > D1000 AS410 > E1000 CE510 > ; > run; > > The result for the 'DSQNEW' branch should look like: > DSQNEW > DSQPOL DSQNEW > PO111 DSQPOL > PO100 DSQPOL > DSQCES DSQNEW > CE510 DSQCES > DSQASR DSQNEW > ASCO DSQASR > AS410 ASCO > AS411 ASCO > ASGBR DSQASR > AS420 ASGBR > DSQWOD DSQNEW > WD518 DSQWOD > WD519 DSQWOD > DSQEXD DSQNEW > A0000 PO100 > A0001 PO100 > D1000 AS410 > E1000 CE510 > > The real datasets have thousands of rows and the 'leaves' of tree can be > at varying depth. Any ideas on how best to solve this? > > Thanks in advance, > > > Dave Birch > SAS Consultant Analyst > Financial Projects > Finance & Administration Branch > Disabilities Services Queensland > phone: 61-7-340 67980 > mob: 61-4-19 647140 > > ================================================= > The information contained in the above e-mail message or messages (which > includes any attachments) is confidential and may be legally privileged. > It is intended only for the use of the person or entity to which it is > addressed. If you are not the addressee any form of disclosure, copying, > modification, distribution or any action taken or omitted in reliance on > the information is unauthorised. Opinions contained in the message(s) do > not necessarily reflect the opinions of the Queensland Government and its > authorities. If you received this communication in error, please notify > the sender immediately and delete it from your computer system network. > =================================================