11-15-2016 11:22 AM
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 68.9 1
Can anyone tell how can I achieve it using data step or proc? Thanks.
11-15-2016 01:39 PM
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;
else if column='a_15' then do;
* Apply your formula for computing T1 for a_15;
11-15-2016 01:49 PM
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.
11-15-2016 02:08 PM
Depending on your requirement you can modefy the IF-THEN ELSE condition.
infile datalines missover;
input column_name $ T1 MATCH ;
a_13 38.5 1
a_14 68.9 1
IF INPUT(SCAN(column_name,2,'_'),3.)<14 then do;
11-15-2016 02:33 PM
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.
11-15-2016 05:48 PM
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));
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;
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).
11-16-2016 10:15 AM
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.
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.
11-16-2016 12:05 AM
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 .
11-16-2016 10:58 AM
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.
11-16-2016 01:38 PM
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?
11-16-2016 01:43 PM
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,
11-16-2016 10:55 PM
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;
11-17-2016 10:13 AM - edited 11-17-2016 10:13 AM
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.