Cubic spline interpolation of yield curve

[KG2]

 Hello,

I am looking to use the PROC EXPAND statement to interpolate yields (var = yield) between key rates (RTTM_INT) which will act as the knots in the method.

I have never used proc expand and so I am not sure about the parameters and coding.

Could someone help with my coding?

When I run this peice of code on a very small section of data it produces no results but the error is that there are missing ID variables (I set this as the maturity points that are not populated for yields) which is exactly the values I want to be interpolated for.

Let me know if there is a preferred way to share the data. It copies nicely into sas except for the titles.

Data (copy and paste to sas)

8              11           CAN       1              -100571.999        0.26        0.032     100000  3200       3.2          2.6130543552     100.571999                2014       M            2              0

8              11           CAN       6              -110355.001        5.57        0.0425   100000  4250       4.25        2.3777301022     110.355001                2014       M            2              0

8              11           CAN       7              -106625.001        6.51        0.037     100000  3700       3.7          2.6506034043     106.625001                2014       M            2              0

8              11           CAN       10           -86447.4               9.8          0.085     60000    5100       8.5          3.263001463       144.079 2014                M            2              0

8              11           CAN       11           -1062007.703      26.508925078     0.0444   1000000                44400    4.44        4.0578080412                106.2007703       2014       M            2              0

 where Column A = "BOPKEY_SECTOR" (character variable)

           Column B = "province" (character)

           Column C = "CURR" (character variable)

            Column D = "RTTM_INT" (numeric variable)

           Column E = "PV" (numeric)

           Column F = "NPER" (numeric)

           Column A0 = " CPN" (numeric)

           Column A1 = " FV " (numeric)

           Column A2 = "PMT" (numeric)

           Column A3 = "COUPON" (numeric)

           Column A4 = "yield" (numeric)

  Code

/*Step 1 Set input and output data*/ 

data work.m1;
set work.missingsubsetyields;
run;


/* This part of the code deals with cubic spline interpolation of the yield curves by RTTM_INT. 
       The Proc Sort is necessary before applying the Proc Expand statement */


proc sort data=work.m1;
by RTTM_INT;
run;

proc timeseries data=work.m1;
id RTTM_INT interval=year start='01FEB2014'd end='01FEB2024'd;
var yield;
run;

proc expand data=work.m1 out=work.m2;
id RTTM_INT;
convert yield=interpol_yld/method=spline(natural);
run;

[Puwang · Accepted solution]

You can do the following:

data m1;
	format date date9.;
	set m1;
	date = intnx('year','01FEB2014'd, RTTM_INT-1);
run;
proc expand data=work.m1 to=year out=work.m2;
id date;
convert yield=interpol_yld/method=spline(natural);
run;

The resulting table m2 fill in missings for yield. Hope this helps.

[KG2]

This is really amazing thank you! I need to make some adjustments to the data to remove bad behaving RTTM < 1 yields, but this is so great. Thank you!