DATA Step, Macro, Functions and more

Proper way to write a Recursive macro function

Accepted Solution Solved
Reply
Highlighted
Contributor
Posts: 29
Accepted Solution

Proper way to write a Recursive macro function

[ Edited ]

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. 

Attachment
Attachment

Accepted Solutions
Solution
a week ago
Super User
Super User
Posts: 8,125

Re: Proper way to write a Recursive macro function

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.

View solution in original post


All Replies
Valued Guide
Posts: 580

Re: Proper way to write a Recursive macro function

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

Contributor
Posts: 29

Re: Proper way to write a Recursive macro function

Posted in reply to andreas_lds
I've attached the sample data sets from RACE EGRC server.
Trusted Advisor
Posts: 1,256

Re: Proper way to write a Recursive macro function

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.

Contributor
Posts: 29

Re: Proper way to write a Recursive macro function

Posted in reply to FreelanceReinhard
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.
Super Contributor
Posts: 340

Re: Proper way to write a Recursive macro function

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.

 

Contributor
Posts: 29

Re: Proper way to write a Recursive macro function

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

ERROR: Invalid symbolic variable name

Super Contributor
Posts: 340

Re: Proper way to write a Recursive macro function

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

%local i;
Solution
a week ago
Super User
Super User
Posts: 8,125

Re: Proper way to write a Recursive macro function

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.

Contributor
Posts: 29

Re: Proper way to write a Recursive macro function

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.

Super User
Super User
Posts: 8,125

Re: Proper way to write a Recursive macro function


@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.

Contributor
Posts: 29

Re: Proper way to write a Recursive macro function

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

and it solved my problem.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 95 views
  • 0 likes
  • 5 in conversation