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;
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.
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.
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!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.