topic Re: about Interpolation and Extrapolation in SAS Procedures

about Interpolation and Extrapolation

bqk — Tue, 10 May 2011 09:16:13 GMT

i have 2 datasets, data1, data2.as following

data data1;
input date : yymmdd10. exdate:yymmdd10. TTM ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2004/1/2 2004/1/17 15
2004/1/2 2004/2/21 50
2004/1/2 2004/3/20 78
2004/1/5 2004/1/17 12
2004/1/5 2004/2/21 47
2004/1/5 2004/3/20 75
;
run;

data data2;
input date : yymmdd10. days rate ;
format date yymmdd10.;
datalines;
2004/1/2 19 1.11326
2004/1/2 47 1.156422
2004/1/2 75 1.178702
2004/1/5 16 1.108422
2004/1/5 44 1.154383
2004/1/5 72 1.170505
;
run;

i want to calculate the rate by linear Interpolation and linear Extrapolation as following.

data data3;
input date : yymmdd10. exdate:yymmdd10. TTM rate ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
date exdate TTM rate
2004/1/2 2004/1/17 15 1.107094
2004/1/2 2004/2/21 50 1.158809143
2004/1/2 2004/3/20 78 1.181089143
2004/1/5 2004/1/17 12 1.101856143
2004/1/5 2004/2/21 47 1.156110357
2004/1/5 2004/3/20 75 1.172232357
;
run;

please help me, thx

Re: about Interpolation and Extrapolation

SPR — Tue, 10 May 2011 14:03:52 GMT

Hello BQK,

I am not sure that understand you correctly but this is my solution:
[pre]
data data1;
input date : yymmdd10. exdate:yymmdd10. TTM ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2004/1/2 2004/1/17 15
2004/1/2 2004/2/21 50
2004/1/2 2004/3/20 78
2004/1/5 2004/1/17 12
2004/1/5 2004/2/21 47
2004/1/5 2004/3/20 75
run;
data data2;
input date : yymmdd10. days rate ;
format date yymmdd10.;
datalines;
2004/1/2 19 1.11326
2004/1/2 47 1.156422
2004/1/2 75 1.178702
2004/1/5 16 1.108422
2004/1/5 44 1.154383
2004/1/5 72 1.170505
run;
proc reg data=data2 outest=reg;
model rate=days;
by date;
run;
data data3;
retain date exdate TTM rate;
merge data1 reg (keep=date intercept days);
by date;
rate=intercept+days*TTM;
run;
[/pre]
Sincerely,
SPR

Re: about Interpolation and Extrapolation

bqk — Tue, 10 May 2011 15:44:00 GMT

thank SPR!!!

i think you use the linear regression to calculate the Interpolation.

i think it is different with that i think at first.

i calculate the interpolation by
(y-y_a) (x-x_a)
-------- = ----------
(y_b-y_a) (x_b-x_a)

and extrapolation by the same way.

in any case, thanks SPR much~

Message was edited by: bqk Message was edited by: bqk

Re: about Interpolation and Extrapolation

SPR — Tue, 10 May 2011 16:26:59 GMT

OK, this is it:
[pre]
data t;
set data1 data2(rename=(days=TTM));
run;
proc sort data=t;
by date TTM;
run;
proc expand data=t out=data3 (where=(exdate NE .)) METHOD=JOIN EXTRAPOLATE;
var rate;
id TTM;
by date;
run;
[/pre]
However, instead of linear you can use spline interpolation (drop METJOD=JOIN), and I prefer of using regression...
SPR

Re: about Interpolation and Extrapolation

bqk — Wed, 11 May 2011 03:13:57 GMT

thank SPR much

excuse me, could i ask another question?

if i have another data1 as following

data data1;
input date : yymmdd10. exdate:yymmdd10. strike_price TTM ;
format date yymmdd10. exdate :yymmdd10. ;
datalines;
2004/1/2 2004/1/17 1140 15
2004/1/2 2004/1/17 1150 15
2004/1/2 2004/2/21 1140 78
2004/1/5 2004/1/17 1140 12
2004/1/5 2004/1/17 1150 12
2004/1/5 2004/2/21 1140 75
;
run;

this data is different with the above becuse it has the same TTM

please help me,thanx

Re: about Interpolation and Extrapolation

SPR — Wed, 11 May 2011 14:37:12 GMT

Hello BQK,

This change to the code does the trick:
[pre]
proc sort data=t out=t nodupkey;
by date TTM;
run;
proc expand data=t out=t1 (where=(exdate NE .)) METHOD=JOIN EXTRAPOLATE;
var rate;
id TTM;
by date;
run;
proc SQL;
create table data3 as select
a.*,b.rate
from data1 as a left join t1 as b
on a.date=b.date and a.TTM=b.TTM
;quit;
[/pre]
Sincerely,
SPR