On 17 Feb, 15:58, dorjeta...@GOOGLEMAIL.COM (karma) wrote: > 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.leu...@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- Hide quoted text - > > - Show quoted text -

Thanks Karma... this is getting there.

One problem is that it seems to be a year off for all rates <1. For example, APPL has a xrate of 0.9 and it should select the brate which is greater than this rate. Therefore the year should be 11 not 10.

Jer