topic Efficient linear interpolation in SAS Programming

Efficient linear interpolation

mark_ph — Wed, 24 Sep 2014 12:28:44 GMT

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;

Any help would be highly appreciated.

Re: Efficient linear interpolation

Ksharp — Wed, 24 Sep 2014 13:29:07 GMT

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

Re: Efficient linear interpolation

mark_ph — Wed, 24 Sep 2014 14:14:41 GMT

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.

Re: Efficient linear interpolation

Ksharp — Thu, 25 Sep 2014 11:56:46 GMT

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

Re: Efficient linear interpolation

mark_ph — Thu, 25 Sep 2014 12:37:41 GMT

, 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.

Re: Efficient linear interpolation

Ksharp — Thu, 25 Sep 2014 12:47:07 GMT

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.

Re: Efficient linear interpolation

mark_ph — Thu, 25 Sep 2014 12:52:26 GMT

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

Re: Efficient linear interpolation

Ksharp — Thu, 25 Sep 2014 13:49:35 GMT

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.

Re: Efficient linear interpolation

Ksharp — Thu, 25 Sep 2014 13:53:17 GMT

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

Re: Efficient linear interpolation

mark_ph — Thu, 25 Sep 2014 15:30:47 GMT

Thank you so much !