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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.