Interpolating values in SAS

[akhilesh_joshi]

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.

[Astounding]

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

[akhilesh_joshi]

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.

[Astounding]

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;

[akhilesh_joshi]

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.

[SuryaKiran]

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;

[Rick_SAS]

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.

[Tommywhosc]

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). 

[akhilesh_joshi]

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. 

[Ksharp]

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 .

[akhilesh_joshi]

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. 

[ballardw]

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-dat... 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?

[akhilesh_joshi]

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

[Ksharp]

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;

[akhilesh_joshi]

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.