topic Recursive Query in SAS Enterprise Guide

Recursive Query

joerimurk — Wed, 12 Sep 2018 09:39:04 GMT

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..?

Re: Recursive Query

RW9 — Wed, 12 Sep 2018 09:51:07 GMT

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.

Re: Recursive Query

joerimurk — Wed, 12 Sep 2018 11:02:38 GMT

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..