BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mark_ph
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

9 REPLIES 9
Ksharp
Super User

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

mark_ph
Calcite | Level 5

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.

Ksharp
Super User

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

mark_ph
Calcite | Level 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.

Ksharp
Super User

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.

mark_ph
Calcite | Level 5

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

Ksharp
Super User

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.

Ksharp
Super User

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

mark_ph
Calcite | Level 5

Thank you so much !

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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