Interpolating values in SAS

[Ksharp]

That is really weird .I got this . check attachement.




data have;
infile cards truncover;
input column_name $    T1             MATCH;
id=input(scan(column_name,-1,'_'),best8.);
cards;
a_10                   .            .           10           .
a_11                   .           .            10           .
a_12                  .            .            10           .
a_13                  .           .             10           .
a_14                  .           .             10           .
a_15             30.70       1             10           .
a_16             28.27        .             11     28.27
a_2                  .             .             12           .
a_3                 .              .             12           .
a_4                 .              .             12           .
a_5                 .              .             12           .
a_6                 .              .             12           .
a_7                .               .             12           .
a_8                .               .             12           .
a_9                .               .             12           . 
a_1                .               .             12           .
a_10              .               .             12           .
a_11              .               .             12           .
a_12              .               .             12           .
a_13              .               .             12           .
a_14         38.60            1            12           .
a_15              .                .            13           .
a_16         66.17             .            13         66.17
;
run;
data want;
retain _id _t1 _match;

do until(not missing(t1) or end1);
 set have end=end1;
end;
last_id=id;last_t1=t1;
do until(not missing(t1) or end2);
 set have end=end2;
 if _match=1 then do;
    new_t1=_t1+(id-_id)* (last_t1-_t1)/(last_id-_id);output;
 end;
 else do;new_t1=t1;output;end;
end;
_id=id;_t1=t1;_match=match;
drop _: last_: t1;
run;

[akhilesh_joshi]

Hi KSharp,

It is working correctly where Match occurs at anything between a_1 to a_14. I don't want to iterpolate when match = 1 and column_name=a_15 or  column_name=a_16. 

Other than that, it is perfect. Would be glad if you can clarify on that part. Thank you so much for your help!

[Ksharp]

Add one more condition in it.

data have;
infile cards truncover;
input column_name $    T1             MATCH;
id=input(scan(column_name,-1,'_'),best8.);
cards;
a_10                   23.5            1          10           .
a_11                   .           .            10           .
a_12                  .            .            10           .
a_13                  56.7           .             10           .
a_14                  .           .             10           .
a_15             30.70       1             10           .
a_16             28.27        .             11     28.27
a_2                  .             .             12           .
a_3                 .              .             12           .
a_4                 .              .             12           .
a_5                 .              .             12           .
a_6                 .              .             12           .
a_7                .               .             12           .
a_8                .               .             12           .
a_9                .               .             12           . 
a_1                .               .             12           .
a_10              .               .             12           .
a_11              .               .             12           .
a_12              .               .             12           .
a_13              .               .             12           .
a_14         38.60            1            12           .
a_15              .                .            13           .
a_16         66.17             .            13         66.17
;
run;
data want;
retain _id _t1 _match;

do until(not missing(t1) or end1);
 set have end=end1;
end;
last_id=id;last_t1=t1;
do until(not missing(t1) or end2);
 set have end=end2;
 if _match=1 and last_id not in (15 16) then do;
    new_t1=_t1+(id-_id)* (last_t1-_t1)/(last_id-_id);output;
 end;
 else do;new_t1=t1;output;end;
end;
_id=id;_t1=t1;_match=match;
drop _: last_: t1;
run;

[akhilesh_joshi]

Hi KSharp,

No it doesn't work. I am getting values interpolated for a_2 to a_16 after the first match at a_16 which I don't desire. Also, while interpolating, it is taking value of a_16 as the interpolation value instead of computing it. And in some cases, values are not interpolated despite match. Please find attached screenshot for your reference. 

[Ksharp]

Can you post your real data in TEXT file or Data Step Code?
And don't forget post the output either .

[Tommywhosc]

Try this:

Leave the code as is, but drop MATCH from WANT (or you can leave it in there, and have it over-written in the Merge below). You'll have a data set with 16x obs, the same number of obs in data set HAVE.

Then do a 1-1 merge:

Data Both;

merge   WANT   HAVE;

run;

In BOTH, column_name, etc. should be in the same order as HAVE, with variable Newval having the interpolated values between the 'a_xx' & 'a_16', with T1/Newval for  'a_16' unchanged. I don't know what you need for T1 at 'a_15' - is it the same as for 'a_16'???

The data set looks messy; that's on purpose so that you can check how the code is working, and make sure the data values are in the correct observations. Easy enough to clean up.