Also, if you're interested in adapting the SQL for your new condition, we can use a case expression to perform the correct query

proc sql ; select a.* , case when a.rate > 1 then (select max(year) from decay as b where a.rate le b.rate ) when a.rate < 1 then (select max(year) from decay as b where a.rate ge b.brate ) end as Year from company_data as a ; quit ;

2009/2/17 karma <dorjetarap@googlemail.com>: > Hi Jer, > > I can see what you have tried to do (a decent effort :-), however > there is no need to define two hash tables and traverse them > separately. The real difference in the structure is that one of the > rates is ascending and the other one is descending, so as long as we > take this into account it should work. > > Let me know if the following works for you. > > Thanks > > > data decay; > input year rate brate; > cards; > 0 2.66667 0.44444 > 1 2.02595 0.65802 > 2 1.77244 0.74252 > 3 1.63155 0.78948 > 4 1.54022 0.81993 > 5 1.47549 0.84150 > 6 1.42685 0.85772 > 7 1.38876 0.87041 > 8 1.35800 0.88067 > 9 1.33254 0.88915 > 10 1.31108 0.89631 > 11 1.29270 0.90243 > 12 1.27675 0.90775 > 13 1.26276 0.91241 > 14 1.25037 0.91654 > 15 1.23931 0.92023 > 16 1.22937 0.92354 > 17 1.22037 0.92654 > 18 1.21219 0.92927 > 19 1.20470 0.93176 > 20 1.19783 0.93406 > 21 1.19124 0.93625 > 22 1.18464 0.93845 > 23 1.17805 0.94065 > 24 1.17145 0.94285 > 25 1.16486 0.94505 > 26 1.15827 0.94724 > 27 1.15167 0.94944 > 28 1.14508 0.95164 > 29 1.13848 0.95384 > 30 1.13189 0.95604 > 31 1.12529 0.95824 > 32 1.11870 0.96043 > 33 1.11210 0.96263 > 34 1.10551 0.96483 > 35 1.09892 0.96703 > 36 1.09232 0.96923 > 37 1.08573 0.97142 > 38 1.07913 0.97362 > 39 1.07254 0.97582 > 40 1.06594 0.97802 > 41 1.05935 0.98022 > 42 1.05276 0.98241 > 43 1.04616 0.98461 > 44 1.03957 0.98681 > 45 1.03297 0.98901 > 46 1.02638 0.99121 > 47 1.01978 0.99341 > 48 1.01319 0.99560 > 49 1.00659 0.99780 > 50 1.00000 1.00000 > ; > > Data company_data; > INPUT name $ rate; > CARDS; > MSFT 2.3 > APPL 0.9 > ; > > data want (drop=rc) ; > if _n_=1 then do ; > if 0 then set decay ; > declare hash hh(dataset:'decay', ordered:'a') ; > hh.definekey('rate','brate') ; > hh.definedata('rate','brate','year') ; > hh.definedone() ; > call missing (of _all_) ; > declare hiter iter('hh') ; > end ; > set company_data (rename=(rate=xrate)) ; > iter.first() ; > do until (rc ne 0) ; > if (xrate lt 1 and brate le xrate) or > (xrate gt 1 and rate ge xrate) then do ; > output ; > leave ; > end ; > rc = iter.next() ; > end ; > run ; > > 2009/2/17 Lonjer <j.leung3@lse.ac.uk>: >> On 17 Feb, 14:49, Lonjer <j.leu...@lse.ac.uk> wrote: >>> On 16 Feb, 15:12, dorjeta...@GOOGLEMAIL.COM (karma) wrote: >>> >>> >>> >>> >>> >>> > I'm not sure how to do this simply with a format, but if you have sas9 >>> > you can use the hash object. >>> >>> > The code below creates a lookup table from your decay_curve dataset >>> > ordered ascendingly. It then traverses this lookup hash using the >>> > hiter object and outputs the first rate value in the lookup that is >>> > greater or equal to the rate in your company_data dataset. >>> >>> > data want (drop=rc) ; >>> > if _n_=1 then do ; >>> > if 0 then set decay_curve ; >>> > declare hash hh(dataset:'decay_curve', ordered:'a') ; >>> > hh.definekey('rate') ; >>> > hh.definedata('rate','year') ; >>> > hh.definedone() ; >>> > call missing (of _all_) ; >>> > declare hiter iter('hh') ; >>> > end ; >>> > set company_data (rename=(rate=xrate)) ; >>> > iter.first() ; >>> > do until (rc ne 0) ; >>> > if rate ge xrate then do ; >>> > output ; >>> > leave ; >>> > end ; >>> > rc = iter.next() ; >>> > end ; >>> > run ; >>> >>> > 2009/2/16 lonjer <jerle...@gmail.com>: >>> >>> > > I'm not sure what you mean but if I show you the data maybe someone >>> > > can illustrate with an example? >>> >>> > > Data company_data; >>> >>> > > INPUT name $ rate; >>> > > CARDS; >>> > > MSFT 2.3 >>> > > APPL 1.2 >>> >>> > > ; >>> > > run; >>> >>> > > data decay_curve; >>> > > INPUT year rate; >>> > > CARDS; >>> > > 0 2.66667 >>> > > 1 2.02595 >>> > > 2 1.77244 >>> > > 3 1.63155 >>> > > 4 1.54022 >>> > > 5 1.47549 >>> > > 6 1.42685 >>> > > 7 1.38876 >>> > > 8 1.35800 >>> > > 9 1.33254 >>> > > 10 1.31108 >>> > > 11 1.29270 >>> > > 12 1.27675 >>> > > 13 1.26276 >>> > > 14 1.25037 >>> > > 15 1.23931 >>> > > 16 1.22937 >>> > > 17 1.22037 >>> > > 18 1.21219 >>> > > 19 1.20470 >>> > > 20 1.19783 >>> > > 21 1.19124 >>> > > 22 1.18464 >>> > > 23 1.17805 >>> > > 24 1.17145 >>> > > 25 1.16486 >>> > > 26 1.15827 >>> > > 27 1.15167 >>> > > 28 1.14508 >>> > > 29 1.13848 >>> > > 30 1.13189 >>> > > 31 1.12529 >>> > > 32 1.11870 >>> > > 33 1.11210 >>> > > 34 1.10551 >>> > > 35 1.09892 >>> > > 36 1.09232 >>> > > 37 1.08573 >>> > > 38 1.07913 >>> > > 39 1.07254 >>> > > 40 1.06594 >>> > > 41 1.05935 >>> > > 42 1.05276 >>> > > 43 1.04616 >>> > > 44 1.03957 >>> > > 45 1.03297 >>> > > 46 1.02638 >>> > > 47 1.01978 >>> > > 48 1.01319 >>> > > 49 1.00659 >>> > > 50 1.00000 >>> >>> > > run; >>> >>> > > Apologies if this is really newbie question... >>> >>> > > Thanks- Hide quoted text - >>> >>> > - Show quoted text - >>> >>> Hi Karma, >>> >>> I've been looking at your solution and it works perfectly. >>> >>> I now want to add another "key" called bRate to the hash table which >>> instead goes from a value of 0.44 to 1.0 for year =0 to year =50. >>> >>> I've tried to declare a new hash table and ordered it in descending. >>> I've also inserted an if statement to tell it to use the other hash >>> table if the "xrate" is less than 1. >>> >>> However I don't think I've got it quite right ... >>> >>> data GEVS.Inputs6 (drop=rca rcb) ; >>> if _n_=1 then do ; >>> if 0 then set decay_curve ; >>> declare hash ha(dataset:'decay_curve', ordered:'a') ; >>> declare hash hb(dataset:'decay_curve', ordered:'d') ; >>> ha.definekey('rate') ; >>> hb.definekey('brate') ; >>> ha.definedata('rate','X') ; >>> ha.definedata('brate','X') ; >>> ha.definedone() ; >>> hb.definedone() ; >>> >>> call missing (of _all_) ; >>> >>> declare hiter itera('ha') ; >>> declare hiter iterb('hb') ; >>> end ; >>> set GEVS.inputs5 ; >>> itera.first() ; >>> iterb.first() ; >>> >>> if xrate > 1 then do; >>> do until (rca ne 0) ; >>> if rate ge xrate then do ; >>> output ; >>> leave ; >>> end ; >>> rca = itera.next() ; >>> end ; >>> end; >>> else if xrate <1 then do; >>> do until (rcb ne 0) ; >>> if brate le xrate then do ; >>> output ; >>> leave ; >>> end ; >>> rcb = iterb.next() ; >>> end ; >>> >>> end; >>> run ; >>> >>> Could you take a look and see what I've done wrong? >>> >>> Thanks, >>> >>> Jer- Hide quoted text - >>> >>> - Show quoted text - >> >> Sorry... for completeness this is my new decay dataset >> >> data decay; >> input year rate brate; >> cards; >> 0 2.66667 0.44444 >> 1 2.02595 0.65802 >> 2 1.77244 0.74252 >> 3 1.63155 0.78948 >> 4 1.54022 0.81993 >> 5 1.47549 0.84150 >> 6 1.42685 0.85772 >> 7 1.38876 0.87041 >> 8 1.35800 0.88067 >> 9 1.33254 0.88915 >> 10 1.31108 0.89631 >> 11 1.29270 0.90243 >> 12 1.27675 0.90775 >> 13 1.26276 0.91241 >> 14 1.25037 0.91654 >> 15 1.23931 0.92023 >> 16 1.22937 0.92354 >> 17 1.22037 0.92654 >> 18 1.21219 0.92927 >> 19 1.20470 0.93176 >> 20 1.19783 0.93406 >> 21 1.19124 0.93625 >> 22 1.18464 0.93845 >> 23 1.17805 0.94065 >> 24 1.17145 0.94285 >> 25 1.16486 0.94505 >> 26 1.15827 0.94724 >> 27 1.15167 0.94944 >> 28 1.14508 0.95164 >> 29 1.13848 0.95384 >> 30 1.13189 0.95604 >> 31 1.12529 0.95824 >> 32 1.11870 0.96043 >> 33 1.11210 0.96263 >> 34 1.10551 0.96483 >> 35 1.09892 0.96703 >> 36 1.09232 0.96923 >> 37 1.08573 0.97142 >> 38 1.07913 0.97362 >> 39 1.07254 0.97582 >> 40 1.06594 0.97802 >> 41 1.05935 0.98022 >> 42 1.05276 0.98241 >> 43 1.04616 0.98461 >> 44 1.03957 0.98681 >> 45 1.03297 0.98901 >> 46 1.02638 0.99121 >> 47 1.01978 0.99341 >> 48 1.01319 0.99560 >> 49 1.00659 0.99780 >> 50 1.00000 1.00000 >> >> ; >> run; >> >> So if the xrate <1 then it will lookup from brate, else it will lookup >> from rate. >> >> Thanks, >> >> Jer >> >