topic Re: Interpolating values in SAS in SAS Programming

Interpolating values in SAS

akhilesh_joshi — Tue, 15 Nov 2016 16:22:13 GMT

Hi All,

I want to find the values of column T1 for a_14 and a_15, and then a_15 below. The data type for column column_name is charater while for T1 and Match its numeric. I looked at expand function in SAS, but I have a formula to calculate values between a_13 and a_16 or a_14 and a_16. so I cannot use expand.

The data looks like -

column_name T1 MATCH
a_13 38.5 1
a_14
a_15
a_16 50.2
a_1
a_2
a_3
a_4
a_5
a_6
a_7
a_8
a_9
a_10
a_11
a_12
a_13
a_14 68.9 1
a_15
a_16 82.1

Can anyone tell how can I achieve it using data step or proc? Thanks.

Re: Interpolating values in SAS

Astounding — Tue, 15 Nov 2016 17:52:04 GMT

It's not clear what you are looking for. You will need to post the results you want.

Re: Interpolating values in SAS

akhilesh_joshi — Tue, 15 Nov 2016 18:07:48 GMT

Hi Astounding. I need to compute values corresponding to a_14 and a_15 in T1 column using a formula that I have. Right now I have values for a_13 and a_16.

Re: Interpolating values in SAS

Astounding — Tue, 15 Nov 2016 18:39:57 GMT

You explained a little, so I can only add a little. A DATA step would let you code:

if column='a_14' then do;

* Apply your formula for computing T1 for a_14;

end;

else if column='a_15' then do;

* Apply your formula for computing T1 for a_15;

end;

Re: Interpolating values in SAS

akhilesh_joshi — Tue, 15 Nov 2016 18:49:05 GMT

Hi Astounding,

Column_name is a column that contains values a_1 to a_16 repetitively i.e cycles of a_1 to a_16. I have to interpolate values in T1 for column_name having A_(1-13) to A_16, with A_(1-13) starting from where MATCH=1.

Re: Interpolating values in SAS

SuryaKiran — Tue, 15 Nov 2016 19:08:48 GMT

Depending on your requirement you can modefy the IF-THEN ELSE condition.

data one;

infile datalines missover;

input column_name $ T1 MATCH ;

datalines;

a_13 38.5 1

a_14

a_15

a_16 50.2

a_1

a_2

a_3

a_4

a_5

a_6

a_7

a_8

a_9

a_10

a_11

a_12

a_13

a_14 68.9 1

a_15

a_16 82.1

;

run;

data two;

set one;

IF INPUT(SCAN(column_name,2,'_'),3.)<14 then do;

T1=10.2;

MATCH=1;

end;

Else do;

T1=20;

MATCH=2;

end;

run;

Re: Interpolating values in SAS

Rick_SAS — Tue, 15 Nov 2016 19:33:55 GMT

If you look at the article "Linear Interpolation in SAS," you will find links to a solution that uses PROC EXPAND, the DATA step, and PROC IML. Perhaps the existing code and examples can help clarify what you want to accomplish.

Re: Interpolating values in SAS

Tommywhosc — Tue, 15 Nov 2016 22:48:45 GMT

Assuming the number of observations is multiple of 16, and

the first observation is 'a_1', and

data is sorted from 'a_1' to 'a_16' for each Match, and

you want interpolated values for T1 on each obs between Match=1 and 'a_16'...

Give this code a try:

data want; length colname $ 4;

set have(where = (Match = 1) rename = (T1 = firstval column_name = col1));

x=input(substr(col1,3),2.);

set have(rename = (T1 = lastval column_name=col2) drop = match where = (col2 = 'a_16') );

interpol = (lastval - firstval)/(16-x);

do i = 1 to 16;

ColName = cats('a_',i);

if i < x then do; newval=.; Xmatch=.; end;

else if i = x then do; newval=firstval; Xmatch=1; end;

else do; newval+interpol; Xmatch=.; end;

output;

end;

run;

It calculates a straight-line interpolation value (Interpol) between Match=1 and 'a_16'. Change if needed. It then loops thru 'a_1' to 'a_16', putting the interpolated values in Newval, and outputting everything to data set WANT. I kept all the variables, look over the data carefully; making sure that Xmatch corresponds to Match (from HAVE), and that firstval, lastval match up with T1 (from HAVE).

Re: Interpolating values in SAS

Ksharp — Wed, 16 Nov 2016 05:05:58 GMT

You'd better post the output.
Does a_1,a_2,..... has value for T1 ?

Your best choice maybe proc expand .but you need SAS/ETS module.
and of course Data Step also can do that .

Re: Interpolating values in SAS

akhilesh_joshi — Wed, 16 Nov 2016 15:15:27 GMT

Hi Tommy,

This is the closest I have got to solution. So thanks for it.

1. You are bang on when you say I want to interpolate values between MATCH=1 and a_16.

2. Number of observations indeed are multiple of 16.

3. For each match, data is sorted from that value of a to a_16. For example, if match occurs at a_5, the data is indeed sorted for a_5 to a_16.

However,

1. The first observation need not always be a_1. Can be anything.

2. Also, there should be a condition to not to interpolate for a_16 and a_15. as we already would have those values.

I would be thankful if you can help me address these two issues. Please find attached sample data set.

Re: Interpolating values in SAS

akhilesh_joshi — Wed, 16 Nov 2016 15:58:53 GMT

Hi Ksharp,

Please find attached sample data file.

As you can observe, there is match = 1 for some values in column column_name. Whenever there is match = 1 for any value between a_1 to a_14 in column_name, there are missing values in column T1 for same. I have the formula to calculate those values i.e to interpolate those values. I want to know how I can achieve it using a data step or a macro.

Re: Interpolating values in SAS

Tommywhosc — Wed, 16 Nov 2016 17:44:01 GMT

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.

Re: Interpolating values in SAS

ballardw — Wed, 16 Nov 2016 18:38:16 GMT

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show you how to create datastep code that can be pasted in the forum or attached as text to recreate your data.

Is there some other variable that tells you that the first a_14 value is in (an apparently) different processing group then the second a_14?

Re: Interpolating values in SAS

akhilesh_joshi — Wed, 16 Nov 2016 18:43:59 GMT

Hi Ballard,

I will keep your instruction in mind next time. Yes there is a primary key which can tell if the first a_14 is different than the next a_14.

Thanks and regards,

Akhilesh

Re: Interpolating values in SAS

Ksharp — Thu, 17 Nov 2016 03:55:31 GMT

So match=1 stands for starting interpolate ?




data have;
infile cards truncover;
input column_name $    T1             MATCH;
id=input(scan(column_name,-1,'_'),best8.);
cards;
a_13                     38.5                1
a_14
a_15
a_16                     50.2
a_1
a_2
a_3
a_4
a_5
a_6
a_7
a_8
a_9
a_10
a_11
a_12
a_13
a_14                    68.9                     1
a_15
a_16                    82.1
;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;

Re: Interpolating values in SAS

akhilesh_joshi — Thu, 17 Nov 2016 15:13:59 GMT

Hi KSharp,

It is generating output like :

column_name T1 Match id New_t1
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

It is generating the value against a_16 in T1 again in new_t1 against a_16. But it is not interpolating the values between. FOr example, it hasn't created a value for a_15.

Re: Interpolating values in SAS

Ksharp — Fri, 18 Nov 2016 07:30:49 GMT

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;

Re: Interpolating values in SAS

akhilesh_joshi — Fri, 18 Nov 2016 15:12:24 GMT

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!

Re: Interpolating values in SAS

Ksharp — Sat, 19 Nov 2016 03:12:09 GMT

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;

Re: Interpolating values in SAS

akhilesh_joshi — Mon, 21 Nov 2016 15:57:23 GMT

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.