I might point out semantically that your example is backwards.  XXX is the parent of YYY, not the child. But..to finish the question...Here's a more direct, brute force method: proc sql; create table work.want (      child char(3),      parent1 char(3),      parent2 char(3),      parent3 char(3),      parent4 char(3),      parent5 char(3),      parent6 char(3)); insert into work.want (child) select child from have where level=1; update work.want t1 set parent1=(select parent from have where child=t1.child); update work.want t1 set parent2=(select parent from have where child=t1.parent1); update work.want t1 set parent3=(select parent from have where child=t1.parent2); update work.want t1 set parent4=(select parent from have where child=t1.parent3); update work.want t1 set parent5=(select parent from have where child=t1.parent4); update work.want t1 set parent6=(select parent from have where child=t1.parent5); quit; ... View more

I could see it working this way: proc sql; create table work.base as select distinct      id,      surg_date,      . format=date9. as Prior_Tab_Date,      '' length=1 as Prior_Flag,      . format=date9. as Post_Tab_Date,      '' length=1 as Post_Flag from      work.have; update base as t1 set      prior_tab_date = (select max(tablet) from work.have where id=t1.id and tablet < t1.surg_date),      post_tab_date=(select min(tablet) from work.have where id=t1.id and tablet >= t1.surg_date); /*if you really need the flags*/ update base set      prior_flag=case when prior_tab_date is not null then 'yes' end,      post_flag = case when post_tab_date is not null then 'yes 'end; quit; ... View more

proc sql ; select name from have group by name having max(coalesce(end_date,'31Dec9999'd)) < '31DEC9999'd ; quit; ... View more

perhaps you need to specify a different lrecl... infile cards delimiter="-," lrecl=1000 recfm=v; ... View more