Efficient linear interpolation

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Efficient linear interpolation

Dear All,

I have dataset db1 containing a vector of x-points and a vector of y-points for each date.

data db1;

     input date     x      y ;

datalines;

20130101     9      0.055

20130101     77    0.049

20130101     140  0.067

20130102     11    0.034

20130102     47    0.041

20130102     82    0.057;


I also have dataset db2 containing the points (variable sel_x) at which I would like to linearly interpolate the variable y for each date.


data db2;

     input date     sel_x;

datalines;

20130101     17

20130101     45

20130101     80

20130102     22

20130102     38

20130102     59;


I would like to obtain the interpolated values of the variable y (interp_y):


data want;

     input date     sel_x     interp_y;

datalines;

20130101     17     0.05429   

20130101     45     0.05182

20130101     80     0.04985

20130102     22     0.03613

20130102     38     0.03925

20130102     59     0.04648;


My unsuccessful attempt involves a double do loop, it stores the x and y when db1.date = db2.date and performs the linear interpolation by using code from this blog Linear interpolation in SAS - The DO Loop:


DO i = 1 TO nrow(db1);

  DO j=1 to nrow(db2);

if db1.date = db2.date, then

do;

         x = db1.x;

         y = db1.y;

interp_y = LinInterp1(x, y, sel_x);

     end;

   END;

   END;

Any help would be highly appreciated.


Accepted Solutions
Solution
‎09-25-2014 09:53 AM
Grand Advisor
Posts: 9,593

Re: Efficient linear interpolation

Haha , Mark.

Finally I get the answer ,it is so easy.

data db1;

     input date     x      y ;

datalines;

20130101     9      0.055

20130101     77    0.049

20130101     140  0.067

20130102     11    0.034

20130102     47    0.041

20130102     82    0.057

;

data db2;

     input date     sel_x;

datalines;

20130101     17

20130101     45

20130101     80

20130102     22

20130102     38

20130102     59

;

run;

data x;

set db1(rename=(x=sel_x )) db2(in=inb);

b=inb;

run;

proc sort data=x ; by  date sel_x ;run;

proc expand data=x out=want(where=(b=1)) method=JOIN;

by date;

id sel_x;

convert y ;

run;

Xia Keshan

View solution in original post


All Replies
Grand Advisor
Posts: 9,593

Re: Efficient linear interpolation

PROC EXPAND if you have SAS/ETS

data db1;
     input date     x      y ;
datalines;
20130101     9      0.055
20130101     77    0.049
20130101     140  0.067
20130102     11    0.034
20130102     47    0.041
20130102     82    0.057
;
data db2;
     input date     sel_x;
datalines;
20130101     17
20130101     45
20130101     80
20130102     22
20130102     38
20130102     59
;
run;
data x;
 set db1(rename=(x=sel_x )) db2(in=inb);
 b=inb;
run;
proc sort data=x ; by  date sel_x ;run;
proc expand data=x out=want(where=(b=1)) method=JOIN;
var y;
run;

Xia Keshan

Contributor
Posts: 35

Re: Efficient linear interpolation

Dear , thank you for your help.


Running the code I get the following output

TIME     y          date              sel_x     b

1          0.053    20130101     17           1

2          0.051    20130101     45           1

4          0.058    20130101     80           1

7          0.036    20130102     22           1

8          0.038    20130102     38           1

10        0.049    20130102     59           1

Only one remark, I find the value of the third observation (0.058) a bit weird, because it should be very close to 0.049 being the value of sel_x (80) close to 77.

Grand Advisor
Posts: 9,593

Re: Efficient linear interpolation

Sorry. For Method JOIN ,interval midpoints are used as the break points . therefore I guess 0.058 should be corresponded to sel_x=(77+140) / 2 = 108.5

Contributor
Posts: 35

Re: Efficient linear interpolation

, thank you again for your reply.

I was reading the SAS documentation on method JOIN SAS/ETS(R) 9.22 User's Guide. Interval midpoints should be used as break points when you have "interval total or average data".

So basically the interpolation isn't taking into account the values in sel_x, but it takes place at interval midpoints. I'm not sure I get it.

Grand Advisor
Posts: 9,593

Re: Efficient linear interpolation

Yeah. You got it. If you need take sel_x as breakpoint , code it by using the formula .

And one more thing , try to post it at  SAS Forecasting and Econometrics .Maybe udo@sas.com would give you incredible answer.

Contributor
Posts: 35

Re: Efficient linear interpolation

Thank you. Yes, I needed to use sel_x as breakpoints. I will try to post it there.

Grand Advisor
Posts: 9,593

Re: Efficient linear interpolation

and also don't forget to post it at SAS/IML Software and Matrix Computations  Rich also could give you answer ,since you are using IML code.

Solution
‎09-25-2014 09:53 AM
Grand Advisor
Posts: 9,593

Re: Efficient linear interpolation

Haha , Mark.

Finally I get the answer ,it is so easy.

data db1;

     input date     x      y ;

datalines;

20130101     9      0.055

20130101     77    0.049

20130101     140  0.067

20130102     11    0.034

20130102     47    0.041

20130102     82    0.057

;

data db2;

     input date     sel_x;

datalines;

20130101     17

20130101     45

20130101     80

20130102     22

20130102     38

20130102     59

;

run;

data x;

set db1(rename=(x=sel_x )) db2(in=inb);

b=inb;

run;

proc sort data=x ; by  date sel_x ;run;

proc expand data=x out=want(where=(b=1)) method=JOIN;

by date;

id sel_x;

convert y ;

run;

Xia Keshan

Contributor
Posts: 35

Re: Efficient linear interpolation

Thank you so much !

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 477 views
  • 0 likes
  • 2 in conversation