Date: Tue, 17 Feb 2009 15:58:56 +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
In-Reply-To: <7f36060c-ee2b-45f5-9cae-f1acc6867085@w34g2000yqm.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1
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
>
| 
