Returns all children from a hierarchy table for a specified parent. More...

Go to the source code of this file.

Detailed Description

Where data stores hierarchies in a simple parent / child mapping, it is not always straightforward to extract all the children for a particular parent. This problem is known as a recursive self join. This macro will extract all the descendents for a parent. Usage:

data have;
  p=1;c=2;output;
  p=2;c=3;output;
  p=2;c=4;output;
  p=3;c=5;output;
  p=6;c=7;output;
  p=8;c=9;output;
run;

%mp_recursivejoin(base_ds=have
  ,outds=want
  ,matchval=1
  ,parentvar=p
  ,childvar=c
  )

Parameters

base_ds=	base table containing hierarchy (not modified)
outds=	the output dataset to create with the generated hierarchy
matchval=	the ultimate parent from which to filter
parentvar=	name of the parent variable
childvar=	name of the child variable (should be same type as parent)
mdebug=	set to 1 to prevent temp tables being dropped

Returns

outds contains the following variables:

• level (0 = top level)
• &parentvar
• &childvar (null if none found)

Version

9.2

Author

Allan Bowe

Definition in file mp_recursivejoin.sas.