Methinks you were too quick in dismissing billfish's suggested code. Other than the dates, which you've never mentioned how you want to treat, the following additions to billfish's code results in the table you said you would like to attain:
libname ora_bsi "c:\temp";
data ora_bsi.temp_hsetor;
informat name $20.;
informat date_start date_end mmddyy10.;
format date_start date_end mmddyy10.;
infile cards dlm='09'x;
input KEY NAME FATHER DATE_START DATE_END;
cards;
1 SÃO PAULO 2 01/01/1900 12/31/2015
2 BRASIL . 01/01/1900 12/31/2015
3 SANTOS 1 01/01/1900 12/31/2015
4 PRAIA GRANDE 3 01/01/1900 12/31/2015
5 ESPIRITO SANTO 2 01/01/1900 12/31/2015
6 VITORIA 5 01/01/1900 12/31/2015
7 MATA DA PRAIA 6 01/01/1900 12/31/2015
8 BAHIA 2 01/01/1900 12/31/2015
9 SALVADOR 8 01/01/1900 12/31/2015
;
data t_a;
set ora_bsi.temp_hsetor (rename=(key=zChild father=zParent));
run;
/*************************/
/*** proposed solution ***/
/*************************/
data t_want(keep=t_gen P1 G1-G9999);
length c_end 8.;
t_max=0;
array GG(10000) P1 G1-G9999;
if _N_=1 then do;
if 0 then set t_a;
declare hash ha(dataset:'t_a',multidata:'N' );
ha.definekey('zChild');
ha.definedata('zChild','zParent');
ha.definedone();
declare hash hb(dataset:'t_a',multidata:'N' );
hb.definekey('zParent');
hb.definedata('zChild','zParent');
hb.definedone();
declare hash hd(multidata:'N');
hd.definekey('c_end');
declare hiter dIter('hd');
hd.definedone();
end;
/******************************************/
/*** make list of end-of-line children ***/
/******************************************/
do until (zDone);
set t_a(rename=(zchild=zc zparent=zp)) nobs=n_last end=zDone;
zParent=zC;
if hb.find() then do;
c_end=zParent;
if hd.check() then do; hd.add(); end;
end;
end;
/****************************************************/
/*** for each end-of-line child, find the lineage ***/
/****************************************************/
rc = dIter.first();
do while (rc = 0);
aDone=0;
t_gen=0;
do until (aDone);
if (t_gen=0) then do; t_gen+1; GG(t_gen)= c_end; zChild=c_end; end;
else if not(ha.find()) then do; t_gen+1; GG(t_gen)= zParent; zChild=zParent; end;
else do; aDone=1; end;
end;
/********************************/
/*** reverse lineage sequence ***/
/********************************/
do i=1 to int(t_gen/2);
x1= GG(i);
GG(i)= GG(t_gen+1-i);
GG(t_gen+1-i)=x1;
end;
output;
/********************************************************/
/*** with one lineage completed, clear out array GG() ***/
/********************************************************/
call missing(of GG(*));
t_max=max(t_max,t_gen);
rc = dIter.next();
end;
/***************************************************************/
/*** remove the fields with only null values for all records ***/
/***************************************************************/
call execute('data t_want; set t_want(keep=t_gen P1 G1-'||compress('G'||put(t_max-1,6.))||');run;');
run;
data ctrl;
set ora_bsi.temp_hsetor (rename=(key=start
name=label)) end=last;
retain fmtname 'names' type 'n';
output;
if last then do;
hlo='O';
label='***ERROR***';
output;
end;
run;
proc format library=work cntlin=ctrl;
run;
data want (keep=key: name: level sk);
set t_want;
array oldkey(*) g:;
array key_(4);
array name_(4) $20.;
retain mainkey;
if _n_ eq 1 then do;
do i=1 to t_gen-1;
key_(i)=oldkey(1);
name_(i)=put(oldkey(1),names.);
end;
mainkey=oldkey(1);
sk=1;
level=1;
output;
end;
else do;
key_(1)=mainkey;
name_(1)=put(key_(1),names.);
level=1;
end;
if 2 le t_gen-1 then do;
do i=2 to 4;
key_(i)=oldkey(2);
name_(i)=put(key_(i),names.);
end;
sk+1;
level+1;
output;
end;
if 3 le t_gen-1 then do;
do i=3 to 4;
key_(i)=oldkey(3);
name_(i)=put(key_(i),names.);
end;
sk+1;
level+1;
output;
end;
if 4 le t_gen-1 then do;
key_(4)=oldkey(4);
name_(4)=put(key_(4),names.);
sk+1;
level+1;
output;
end;
run;
data want;
retain sk key_1 name_1 key_2 name_2 key_3 name_3 key_4 name_4 level;
set want;
run;
