Hello, I have a database of two variable: account # and previous account #, where previous account # (previous_lnacctno), refers back to a loan (lnacctno) in the same table. I am currently trying to link every account # to the very first account, although I am sure there's a proper way to do it instead of doing iteratively as I am doing below. Ideally I would want to have as a final table every account # (lnacctno) and the first account (to be determined) and the number of accounts in between (there could be up to 7 or 8). NOTE: I also noticed that there is a data quality problem where some loans are referencing themselves. I think a simple correction in the initial "previous_acctn" table will fix it (where lnacctno = previous_lnacctno). proc sql;
create table previous_acctn as select
previous_lnacctno,
lnacctno
from have;
quit;
proc sql;
create table previous1 as select
t1.lnacctno, t1.previous_lnacctno, t2.previous_lnacctno as previous_lnacctno2
from previous_acctn t1
left join previous_acctn t2 on (t1.previous_lnacctno = t2.lnacctno);
quit;
proc sql;
create table previous2 as select
t1.lnacctno, t1.previous_lnacctno, t1.previous_lnacctno2, t2.previous_lnacctno as previous_lnacctno3
from previous1 t1
left join previous_acctn t2 on (t1.previous_lnacctno2 = t2.lnacctno);
quit;
proc sql;
create table previous3 as select
t1.lnacctno, t1.previous_lnacctno, t1.previous_lnacctno2, t1.previous_lnacctno3, t2.previous_lnacctno as previous_lnacctno4
from previous2 t1
left join previous_acctn t2 on (t1.previous_lnacctno3 = t2.lnacctno);
quit;
proc sql;
create table previous4 as select
t1.lnacctno, t1.previous_lnacctno, t1.previous_lnacctno2, t1.previous_lnacctno3, t1.previous_lnacctno4, t2.previous_lnacctno as previous_lnacctno5
from previous3 t1
left join previous_acctn t2 on (t1.previous_lnacctno4 = t2.lnacctno);
quit;
proc sql;
create table previous5 as select
t1.lnacctno, t1.previous_lnacctno, t1.previous_lnacctno2, t1.previous_lnacctno3, t1.previous_lnacctno4, t1.previous_lnacctno5, t2.previous_lnacctno as previous_lnacctno6
from previous4 t1
left join previous_acctn t2 on (t1.previous_lnacctno5 = t2.lnacctno);
quit;
proc sql;
create table previous6 as select
t1.lnacctno, t1.previous_lnacctno,
t1.previous_lnacctno2,
t1.previous_lnacctno3,
t1.previous_lnacctno4,
t1.previous_lnacctno5,
t1.previous_lnacctno6,
t2.previous_lnacctno as previous_lnacctno7
from previous5 t1
left join previous_acctn t2 on (t1.previous_lnacctno6 = t2.lnacctno);
quit;
proc sql;
create table previous7 as select
t1.lnacctno, t1.previous_lnacctno,
t1.previous_lnacctno2,
t1.previous_lnacctno3,
t1.previous_lnacctno4,
t1.previous_lnacctno5,
t1.previous_lnacctno6,
t1.previous_lnacctno7,
t2.previous_lnacctno as previous_lnacctno8
from previous6 t1
left join previous_acctn t2 on (t1.previous_lnacctno7 = t2.lnacctno);
quit;
... View more
