Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_recursivejoin.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Returns all children from a hierarchy table for a specified parent
4  @details Where data stores hierarchies in a simple parent / child mapping,
5  it is not always straightforward to extract all the children for a
6  particular parent. This problem is known as a recursive self join. This
7  macro will extract all the descendents for a parent.
8  Usage:
9 
10  data have;
11  p=1;c=2;output;
12  p=2;c=3;output;
13  p=2;c=4;output;
14  p=3;c=5;output;
15  p=6;c=7;output;
16  p=8;c=9;output;
17  run;
18 
19  %mp_recursivejoin(base_ds=have
20  ,outds=want
21  ,matchval=1
22  ,parentvar=p
23  ,childvar=c
24  )
25 
26  @param base_ds= base table containing hierarchy (not modified)
27  @param outds= the output dataset to create with the generated hierarchy
28  @param matchval= the ultimate parent from which to filter
29  @param parentvar= name of the parent variable
30  @param childvar= name of the child variable (should be same type as parent)
31  @param mdebug= set to 1 to prevent temp tables being dropped
32 
33 
34  @returns outds contains the following variables:
35  - level (0 = top level)
36  - &parentvar
37  - &childvar (null if none found)
38 
39  @version 9.2
40  @author Allan Bowe
41 
42 **/
43 
44 %macro mp_recursivejoin(base_ds=
45  ,outds=
46  ,matchval=
47  ,parentvar=
48  ,childvar=
49  ,iter= /* reserved for internal / recursive use by the macro itself */
50  ,maxiter=500 /* avoid infinite loop */
51  ,mDebug=0);
52 
53 %if &iter= %then %do;
54  proc sql;
55  create table &outds as
56  select 0 as level,&parentvar, &childvar
57  from &base_ds
58  where &parentvar=&matchval;
59  %if &sqlobs.=0 %then %do;
60  %put NOTE: &sysmacroname: No match for &parentvar=&matchval;
61  %return;
62  %end;
63  %let iter=1;
64 %end;
65 %else %if &iter>&maxiter %then %return;
66 
67 proc sql;
68 create table _data_ as
69  select &iter as level
70  ,curr.&childvar as &parentvar
71  ,base_ds.&childvar as &childvar
72  from &outds curr
73  left join &base_ds base_ds
74  on curr.&childvar=base_ds.&parentvar
75  where curr.level=%eval(&iter.-1)
76  & curr.&childvar is not null;
77 %local append_ds; %let append_ds=&syslast;
78 %local obs; %let obs=&sqlobs;
79 insert into &outds select distinct * from &append_ds;
80 %if &mdebug=0 %then drop table &append_ds;;
81 
82 %if &obs %then %do;
83  %mp_recursivejoin(iter=%eval(&iter.+1)
84  ,outds=&outds,parentvar=&parentvar
85  ,childvar=&childvar
86  ,base_ds=&base_ds
87  )
88 %end;
89 
90 %mend;