Recursive Query

[joerimurk]

I've got this table (City_History).

City   Start_Year   End_Year   New_City   New_Start   New_End
  A        2001            2003             B              2004          2006
  B        2004            2006             C              2007          2010
  C        2007            2010             D              2011          2016
  D        2011            2016

And if I have this table (Old_City)

City     Year

 A        2002

 A        2005

 A        2008

 A        2012

I want to join City_Historie to the table Old_City to get this:

City     Year      New_City

 A        2002          A

 A        2005          B

 A        2008          C

 A        2012          D

Does anybody have an idea how to do this without joining multiple times..?

[RW9]

Yes, you don't need recussive joins, you only need to two from the data you posted - note you haven't posted them as datasteps, so this is only pseudocode:

proc sql;
  create table want as 
  select a.city,
            a.year,
            case when b.city="" then a.city else b.city end as new_city
  from   old_city a
  left join city_history b
  on       b.new_start <= a.year <b.new_end;
quit;

However that being said, you would be better off normalising the history table, i.e to:

City start_year end_year

Drop the new ones as these don't seem to have any relevance.

[joerimurk]

Thanks! Im sorry but I didn't explain it good enough. I've got one table with the historie of all city's. Like this:

City   Start_Year   End_Year   New_City   New_Start   New_End

A        2001            2003             B              2004          2006 

B        2004            2006             C              2007          2010

C        2007            2010             D              2011          2016

D        2011            2016

E        2010            2018

F        2007            2009             E              2010          2018

G       1900            2018             

...

In other tables there might be incorrect data. For example information about the city A in 2008. But in 2008 the city is not A anymore but C. This is what i want to correct. So if you got a row:

City    Year       ...         ...

A        2008      ...         ...

I want to add a variable City_correct like this, to correct the wrong city's:

City    Year   City_correct   ...    ...

A        2008          C            ...    ...

Hope this is a better explanation..