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
do;
x = db1.x
y = db1.y
interp_y = LinInterp1(x, y, sel_x);
end;
END;
END;
Any help would be highly appreciated.
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
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
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.
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
, 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.
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.
Thank you. Yes, I needed to use sel_x as breakpoints. I will try to post it there.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.