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 (February 2009, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Tue, 17 Feb 2009 16:18:57 +0000
Reply-To:   karma <dorjetarap@GOOGLEMAIL.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   karma <dorjetarap@GOOGLEMAIL.COM>
Subject:   Re: Lookup closest
Comments:   To: Lonjer <j.leung3@lse.ac.uk>
In-Reply-To:   <d6a0d8f10902170758wf062123lcfaee02c76d22be1@mail.gmail.com>
Content-Type:   text/plain; charset=ISO-8859-1

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 >> >


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