Proper way to write a Recursive macro function

[Azeem112]

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

*A parent whose sub branches are required;
%let _MO = 10008;

*Data set to save all of the sub branches;
proc sql noprint;
create table management(
rk int,
ID varchar(4000),
Name varchar(4000)
);
quit;

%macro recursive(MO);

%put &MO;

proc sql noprint;
*Insert parent into dataset;
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;

*Find all the sub branches of that parent;
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';

*Count of sub branches of that parent;
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(|));
        *repeat the process for each sub branch;
	%recursive(&Child);
	%put &i;
%end;

%mend;

%recursive(&_MO);

Dry Run

First Cycle

Lets assume MO 11382 has 3 kids

MO_List = 11383|11384|11385

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. 

[andreas_lds]

Can post test data as data step, so that we have something to play with?

[Azeem112]

I've attached the sample data sets from RACE EGRC server.

[FreelanceReinh]

I haven't checked the details, but your request sounds a bit similar to one I answered yesterday. Please note that I generalized the solution (to the case of multiple "parents" per "child") after the thread opener had marked my initial solution as accepted.

[Azeem112]

Thanks for your reply. my case is little different with this one, there are many kids of a single parent. I'll look in the solution and try to find my solution.

[gamotte]

Hello,

I haven't read the detail of your code but make sure that any counter used in your recursive macro is declared as %local.

[Azeem112]

I used %local instead of %recursive in my macro loop and it gave me following error.

ERROR: Invalid symbolic variable name

[gamotte]

I meant, you have to declare your counter i as local,
for instance at the beginning of your macro.

%local i;

[Tom · Accepted solution]

Please explain what you want OUT of this macro.  It is not clear what you are trying to generate.  If it is a dataset then post example input and output data (in the form of data steps that readers can copy and run).  If it is a macro variable then show example result.

Make sure to make you macro variables LOCAL so that nested calls do not overwrite their values.  In particular COUNT and I.

[Azeem112]

I've posted a sample data set.

Out of macro i want to save all of the children and grand children in management table of a specific parent.

[Tom]

---

@Azeem112 wrote:

I've posted a sample data set.

Out of macro i want to save all of the children and grand children in management table of a specific parent.

---

Datasets that someone would need to download are not as useful as actual code that can just be copied and pasted.

Sounds like a classical connected subgraph problem.  If you search on this site someone has posted a very nice subgraph macro you could probably use.

If you want to make your macro recursive then you will probably need to have it generate a distinct table for each query to avoid conflicts between the versions that executing at the same time.

Otherwise instead of using recursion just make a loop to keep finding and adding new descendants that stops when there are no more to be added.

[Azeem112]

I'v added %local i; %local count; %local MO_List;

and it solved my problem.

[Ambica071]

A very nice Solution!!!