Thank you all for your great help! I tested Dan's code, it takes less than 0.03 second. it is perfect...that is what I define as "Better Solution", it runs fast, and looks neat. :D I was using do loop, but I was not smart enough to add 'if ...then... leave', and it took me 3 naps time to run. :D Enjoy spring, xr

On Mar 8, 4:30 pm, djnordl...@VERIZON.NET (Daniel Nordlund) wrote: > > -----Original Message----- > > From: SAS(r) Discussion [mailto:SA...@LISTSERV.UGA.EDU] On > > Behalf Of xinrong > > Sent: Sunday, March 08, 2009 2:12 PM > > To: SA...@LISTSERV.UGA.EDU > > Subject: SAS question, better solution > > > and here is my challenge: > > I want to add new a numeric column 'newcol' to tb1, > > and the value of newcol will be decided by item: > > if item is 'A' then newcol take valA; > > if item is 'B' then newcol take valB; > > ...so on and so forth > > > tb1 has 32 columns: > > > time item valA valB valC valD valE valF....valAD > > t1 A 0.1 0.2 . . 0.4 > > t1 B . . 0.1 0.2 . . 0.4 > > t1 c . 0.2 0.3 . . . 0.4 > > . > > . > > . > > t100 A 0.5 0.2 . . 0.1 > > t100 B . . 0.3 0.4 . . 0.1 > > t100 c . 0.2 0.4 . . . 0.2 > > > I come up with following code, it works but I think there must be > > some > > better ways to do it, any ideas? > > > Proc sql; > > select distinct item into :lst separated by ' ' from tb1; > > quit; > > > data tb1; > > set tb1; > > array imp{30} valA valB valC valD valE ....valAD; > > select (item); > > when ("%scan(&lst,1)") newcol=imp[1]; > > when ("%scan(&lst,2)") newcol=imp[2]; > > when ("%scan(&lst,3)") newcol=imp[3]; > > when ("%scan(&lst,4)") newcol=imp[4]; > > when ("%scan(&lst,5)") newcol=imp[5]; > > . > > . > > . > > when ("%scan(&lst,29)") newcol=imp[29]; > > when ("%scan(&lst,30)") newcol=imp[30]; > > otherwise newcol=.; > > end; > > You will have to define "better". But, here is a solution that doesn't use > the wallpaper code above (and also doesn't add any error checks , etc., and > is not tested. How about that for qualification? :-) What do you do if item > is not in your list? > > data tb1; > set tb1; > array imp{30} valA valB valC valD valE ....valAD; > do _n_ = 1 to 30; > if item = scan("&lst",_n_) then leave; > end; > newcol=imp[_n_]; > run; > > Hope this is helpful, > > Dan > > Daniel Nordlund > Bothell, WA USA- Hide quoted text - > > - Show quoted text -