DATA Step, Macro, Functions and more

Interpolate time series data between variables and new variables

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Interpolate time series data between variables and new variables

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


Accepted Solutions
Solution
‎12-20-2016 05:43 AM
New Contributor
Posts: 2

Re: Interpolate time series data between variables and new variables

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;

 

 

View solution in original post


All Replies
Solution
‎12-20-2016 05:43 AM
New Contributor
Posts: 2

Re: Interpolate time series data between variables and new variables

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;

 

 

Contributor
Posts: 39

Re: Interpolate time series data between variables and new variables

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.

 

Super User
Posts: 11,343

Re: Interpolate time series data between variables and new variables

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 141 views
  • 2 likes
  • 3 in conversation