Interpolate time series data between variables and new variables

[oriti]

Hello,

I have time series of interest rate data. I enclosed a small example of the data .

Part of the variables start in a later point of time.

Take for example the 16-09-93. I have data on interest rate of  6_month, 1_year 2_year 3_year 5_year 7_year 10_year 30_year. I'm intersted to know also 1_month 3_month 20_year which are missing at that date. In addition I'm interested the interest rate  of  new variable 4_year  6_year 8_year 9_year which are not in the data set.

I would appreciate any help.

Thanks,

Or

observation_date	1_month	3_month	6_month	1_year	2_year	3_year	5_year	7_year	10_year	20_year	30_year
16-09-93			3.16	3.39	3.88	4.19	4.74	5.12	5.37		6.01
17-09-93			3.17	3.39	3.89	4.19	4.75	5.11	5.38		6.04
20-09-93			3.16	3.41	3.92	4.23	4.79	5.13	5.42		6.08
21-09-93			3.14	3.38	3.9	4.21	4.8	5.19	5.47		6.14
22-09-93			3.15	3.39	3.9	4.22	4.81	5.17	5.45		6.1
23-09-93			3.15	3.39	3.87	4.22	4.8	5.15	5.42		6.06
24-09-93			3.15	3.39	3.9	4.22	4.81	5.14	5.42		6.06
27-09-93			3.1	3.32	3.81	4.12	4.69	5.01	5.3		5.97
28-09-93			3.12	3.32	3.79	4.11	4.67	4.98	5.28		5.94
29-09-93			3.15	3.36	3.84	4.19	4.74	5.03	5.35		6
30-09-93			3.13	3.39	3.89	4.24	4.79	5.11	5.4		6.04
01-10-93			3.11	3.35	3.84	4.18	4.72	5.03	5.34	6.12	5.98
04-10-93			3.17	3.35	3.85	4.16	4.71	5.04	5.34	6.1	5.99
05-10-93			3.2	3.38	3.86	4.18	4.72	5.05	5.35	6.12	6.01
06-10-93		3.04	3.19	3.36	3.85	4.16	4.7	5.05	5.35	6.12	6.01
07-10-93		3.05	3.17	3.35	3.83	4.15	4.69	5.05	5.33	6.11	6.01
08-10-93		3.05	3.16	3.33	3.78	4.09	4.61	4.96	5.26	6.02	5.92
12-10-93		3.12	3.21	3.36	3.82	4.11	4.64	4.97	5.27	6.02	5.92
13-10-93		3.1	3.2	3.36	3.84	4.11	4.64	4.98	5.27	6.03	5.92
14-10-93		3.08	3.18	3.36	3.82	4.09	4.61	4.93	5.23	5.98	5.86
I need :	1_month	3_month	6_month	1_year	2_year	3_year	4_year	5_year	6_year	7_year	8_year	9_year	10_year	11_year	12_year	….30_year

. 

[frajuc · Accepted solution]

Hi Oriti,

The below program might help you. You can find more details about the Timeseries, Means and Expand procedures in support.sas.com . You will have to adjust the program with your own choices on how to replace the missing values (previous rate or average rate).

Data interest_rate ;

input observation_date DDMMYY10. month_1 month_3 month_6 year_1 year_2 year_3 year_5 year_7 year_10 year_20 year_30 ;

format observation_date DDMMYY10. ;

cards ;

16/09/1993 . . 3.16 3.39 3.88 4.19 4.74 5.12 5.37 . 6.01

17/09/1993 . . 3.17 3.39 3.89 4.19 4.75 5.11 5.38 . 6.04

20/09/1993 . . 3.16 3.41 3.92 4.23 4.79 5.13 5.42 . 6.08

21/09/1993 . . 3.14 3.38 3.9 4.21 4.8 5.19 5.47 . 6.14

22/09/1993 . . 3.15 3.39 3.9 4.22 4.81 5.17 5.45 . 6.1

23/09/1993 . . 3.15 3.39 3.87 4.22 4.8 5.15 5.42 . 6.06

24/09/1993 . . 3.15 3.39 3.9 4.22 4.81 5.14 5.42 . 6.06

27/09/1993 . . 3.1 3.32 3.81 4.12 4.69 5.01 5.3 . 5.97

28/09/1993 . . 3.12 3.32 3.79 4.11 4.67 4.98 5.28 . 5.94

29/09/1993 . . 3.15 3.36 3.84 4.19 4.74 5.03 5.35 . 6

30/09/1993 . . 3.13 3.39 3.89 4.24 4.79 5.11 5.4 . 6.04

01/10/1993 . . 3.11 3.35 3.84 4.18 4.72 5.03 5.34 6.12 5.98

04/10/1993 . . 3.17 3.35 3.85 4.16 4.71 5.04 5.34 6.1 5.99

05/10/1993 . . 3.2 3.38 3.86 4.18 4.72 5.05 5.35 6.12 6.01

06/10/1993 . 3.04 3.19 3.36 3.85 4.16 4.7 5.05 5.35 6.12 6.01

07/10/1993 . 3.05 3.17 3.35 3.83 4.15 4.69 5.05 5.33 6.11 6.01

08/10/1993 . 3.05 3.16 3.33 3.78 4.09 4.61 4.96 5.26 6.02 5.92

12/10/1993 . 3.12 3.21 3.36 3.82 4.11 4.64 4.97 5.27 6.02 5.92

13/10/1993 . 3.1 3.2 3.36 3.84 4.11 4.64 4.98 5.27 6.03 5.92

14/10/1993 . 3.08 3.18 3.36 3.82 4.09 4.61 4.93 5.23 5.98 5.86

;run ;

/*Create time series and replace with latest rate*/

proc timeseries data = interest_rate out=IR_impute ;

id observation_date interval = day setmissing=prev;

var month_1 month_3 month_6 year_1 year_2 year_3 year_5 year_7 year_10 year_20 year_30 ;

quit ;

/* Replacing with JOIN method */

proc expand data=interest_rate out=IR_impute2 from=day;

id observation_date;

convert month_3=spline_month_3 / method=join;

convert year_20=spline_year_20 / method=join;

run;

/* Replace previous values with the average rate for Month1 month3 and year20 */

proc means data=IR_impute noprint ;

var month_1 month_3 month_6 year_1 year_2 year_3 year_5 year_7 year_10 year_20 year_30 ;

output out=data_out mean(month_1)=M1 mean(month_3)=M3 mean(year_20)=Y20;

run;

data final;

if _n_=1 then set Data_out (drop=_type_ _freq_ );

set interest_rate;

if month_1=. then month_1=M1;

if month_3=. then month_3=M3;

if year_20=. then year_20=Y20;

drop M1 M3 Y20;

run;

[oriti]

Thank you!!

you gave me few ideas and I used the expand procedure to fill missing values.

In addition I added a spline function (msplint) to add more variables that I needed.

[ballardw]

When at all possible please post you example data from a SAS data set using data step code so that we can duplicate the data without having to write a program to test things with. Instructions are here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

The method you used to post data in some display/browser combinations over flows the display area. Also we cannot tell if your observation date variable is actually as SAS date or character which can lead to confusion if we have to write a program to read the example and use a different choice than you made.