```Date: Wed, 31 Jan 2007 22:37:12 -0500 Reply-To: "Howard Schreier " Sender: "SAS(r) Discussion" From: "Howard Schreier " Subject: Re: Updating data from a distance table On Wed, 31 Jan 2007 09:36:36 -0800, Terjeson, Mark wrote: >Hi Julien, > >Here is one approach: > >(proc transpose -- the unsung hero) > > > >data cities; > input city1 \$ city2 \$; >cards; >v01000 v13000 >v38000 v69000 >v01000 v38000 >; >run; > > > >data distances; > input id \$ v01000 v13000 v38000 v69000; >cards; >v01000 0 125 24 312 >v13000 125 0 76 94 >v38000 24 76 0 215 >v69000 312 94 215 0 >; >run; > > >proc transpose data=distances > out=dist2(rename=( > id=city1 > _name_=city2 > col1=distance > )); > by id; >run; The transpose is indeed the critical thing. Here's a variation. First insert a WHERE filter so that the PROC TRANSPOSE excludes the trivial and repetitious cells from the symmetric distance matrix: proc transpose data=distances out=dist2(rename=( id=city1 _name_=city2 col1=distance ) where=(city2>city1) ); by id; run; Sort the other data set: proc sort data=cities; by city1 city2; run; Then it's a rather simple merge: data result; merge cities(in=wanted) dist2; by city1 city2; if wanted; run; > > >proc sql; > create table result as > select > a.city1, > a.city2, > b.distance > from > cities as a > left join > dist2 as b > on > a.city1 eq b.city1 > and > a.city2 eq b.city2 > ; >quit; > > > > >Hope this is helpful. > > >Mark Terjeson >Senior Programmer Analyst, IM&R >Russell Investment Group > > >Russell >Global Leaders in Multi-Manager Investing > > > > > > >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >Julien Barnier >Sent: Wednesday, January 31, 2007 9:04 AM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Updating data from a distance table > >Hi to all, > >I am quite new to SAS, and here is a problem I can't figure how to >solve. > >I have two datasets. The first one has two columns which represent city >codes : > >city1 city2 >v01000 v13000 >v38000 v69000 >v01000 v38000 > >The second one is a sort of distance table which contains distance >between cities identified by their codes : > >id v01000 v13000 v38000 v69000 >v01000 0 125 24 312 >v13000 125 0 76 94 >v38000 24 76 0 215 >v69000 312 94 215 0 > >What I'd like to do is to add a "distance" variable to the first dataset >with the distance between the two cities : > >city1 city2 distance >v01000 v13000 125 >v38000 v69000 215 >v01000 v38000 24 > >I've looked at macros, at proc sql, but I can't manage to get what I >want. > >If you have an idea or a link to a tutorial which could explain this, it >would be greatly appreciated ! > >Thanks in advance, > >-- >Julien ```

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