Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_recursivejoin.sas File Reference

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.