BookmarkSubscribeRSS Feed
bqk
Calcite | Level 5 bqk
Calcite | Level 5
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
5 REPLIES 5
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
bqk
Calcite | Level 5 bqk
Calcite | Level 5
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
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
bqk
Calcite | Level 5 bqk
Calcite | Level 5
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
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2716 views
  • 0 likes
  • 2 in conversation