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.
... View more