Converted it to a more sensible dynamic Macro. %Macro Tstruct_Flat(); /*****Check what is the lowest level LCD present in the structure*****/ proc sql; Select max(level) into :max_level from work.lcd_raw ; quit; /*****Collect the highest level LCD's in the table****/ proc sql; Create table Tstruct_temp as select Child,level from work.lcd_raw where level=1 ; quit; %do i = 2 %to &max_level; %let j = %eval(&i. - 1); /*Macro do loop variables to be used inside the loop*/ %let k = %eval(&i. - 2); /*Macro do loop variables to be used inside the loop*/ %let l = %eval(&i. ); /*Macro do loop variables to be used inside the loop*/ %if %eval(&i.*1) = 2 %then %do; proc sql; Create table Tstruct_temp as select a.*, b.child as parent&j. , b.level as level&l. from Tstruct_temp as a left join work.lcd_raw as b on a.child = b.parent and b.level = &i. ; quit; %end; %if %eval(&i.*1) gt 2 %then %do; proc sql; Create table Tstruct_temp as select a.*, b.child as parent&j. , b.level as level&l. from Tstruct_temp as a left join work.lcd_raw as b on a.parent&k. = b.parent and b.level = &i. ; quit; %end; %end; %mend; Thanks, Suvi
... View more