LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2008, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 23 May 2008 11:05:51 -0700
Reply-To:     Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Re: Repeated recursive joins
Comments: To: Bucher Scott <SBucher@SCHOOLS.NYC.GOV>
In-Reply-To:  <98634D42B37B2E4E96CF3A3BD3CD9AE60182BA7A@EX1VS2.nyced.org>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes

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


Back to: Top of message | Previous page | Main SAS-L page