Proper way to write recursive code in SAS

Question

I've two tables that I'm working with.

First has the basic information like RK, ID and name etc

second table creates the hierarchy and have columns as RK and Parent_RK

I want to get all the children of a record under his full hierarchy.

My code is

%let _MO = 11382;

proc sql noprint;
create table management(
rk int,
ID varchar(4000),
Name varchar(4000)
);
quit;

%macro recursive(MO);

%put &MO;

proc sql noprint;
insert into management
select distinct Par.management_org_rk, Mo.management_org_id,Mo.Organization_nm
from Sasoprsk.Management_org_assoc_l par
inner join Sasoprsk.Management_org_l Mo on Mo.management_org_rk = par.management_org_rk
where par.management_org_rk = &MO;

select distinct management_org_rk
into: MO_List separated by "|"
from Sasoprsk.Management_org_assoc_l
where Parent_management_org_rk = &MO
and MANAGEMENT_ORG_ASSOC_TYPE_CD = 'DEF';

select count(distinct management_org_rk)
into: count
from Sasoprsk.Management_org_assoc_l
where Parent_management_org_rk = &MO
and MANAGEMENT_ORG_ASSOC_TYPE_CD = 'DEF';
quit;

%put &MO_List;
%put &count;

%if &count ne 0 %then 
%do i=1 %to &count;
    %let Child=%scan(%bquote(&MO_List), %bquote(&i) ,%str(|));
    %recursive(&Child);
    %put &i;
%end;

%mend;

%recursive(&_MO);

Dry Run

First Cycle

Lets assume MO 11382 has 3 kids

MO_List = 11383|11384|1138
count = 3
child = 11383

Second Cycle

MO 11383 has 2 Kids

MO_List = 11386|11387
count =2
child = 11386

Third Cycle

Mo 11386 has no kids

MO_List =11386|11387;
count = 0;
i=1;

Now system will go back to second cycle

MO= 11387 has no kids

MO_List =11386|11387;
count = 0;
i=2;

Now system will go back to first cycle

since MO_List has been updated it won't be able to proceed. Kindly suggest me a better approach to get all of the children of a MO. In server there are ten levels of hierarchy so I can't hard-code.

Can we do multi threading at do loop that instead of executing with first child he should first send all of the children to macro then do same with their kids.

Hierarchy is like a tree with multiple nodes.

Answer 1

The main fix for that macro is to make sure to define your macro variables as local. This is important for any macro that might by called by other macros to prevent it from overwriting macro variables that already exist with those names. Obviously there is definitely a potential for macro variable name conflicts when calling a macro recursively.

%macro recursive(MO);
  %local mo_list count i ;
  ...

Note that you could also eliminate the duplicate query to count number of values written to MO_LIST. SAS will have counted that for you already.

select distinct management_org_rk
  into :MO_List separated by "|"
from Sasoprsk.Management_org_assoc_l
where Parent_management_org_rk = &MO
  and MANAGEMENT_ORG_ASSOC_TYPE_CD = 'DEF'
;
%let count=&sqlobs;