You might look at <ftp://ftp.sas.com/techsup/download/sample/samp_lib/basesampFunInteresting_Applicati00000033.html >.

If I had to do this, I'd create a helper table containing all the mappings, and use that rather than doing a recursive join each time. That is, rather than trying to map A to B and B to C and C to D each time, I'd create a table that maps A to D, B to D, and C to D. Then it's a simple join in subsequent programs.

On May 23, 2008, at 10:26 AM, Bucher Scott wrote:

> Hi, > > I have a lookup table (dsn1) comprised of two variables, the old code > (v1) and the new code (v2). Within this file a new code can also be an > old code somewhere else in the file; this can happen any number of > times. I need a lookup table that allows me to lookup the latest code > for any given value (dsn2). > > I have had some fun trying to solve this using a series of reflexive > joins, but cant quite get all the way from dsn1 to dsn2. Anyone know > the > name of this general problem or have any ideas on how to approach it? > > Also, on a related note, has anyone found any good online resources > for > learning SQL, especially T-SQL. I'm hoping there is sort of a SUGI for > SQL out there somewhere, but so far it seems the online resources are > rather diffuse and of spotty quality. Thanks. > > > data dsn1; > input v1 $ v2 $; > datalines; > a1 a150 > b c > a150 a110 > d e > a110 a4 > f g > a4 a5 > ; > run; > > Desired output: > > data dsn2; > infile datalines missover; > input v1 $ v2 $ v3 $ v4 $ v5 $; > datalines; > a1 a150 a110 a4 a5 > b c > d e > f g > ; > run; > > > Regards, > Scott Bucher > SAS Programmer > Office of Accountability > NYC Dept. of Education