Hi all,
My data looks like this.
data one;
infile datalines missover;
input column_1 column_2 column_3 column_4 column_5 column_6 column_7 column_8 column_9 column_10 column_11 column_12 column_13 column_14 column_15 column_16 $ Start;
datalines;
. . . . . . . . . 52.5 . . . . . 68.10 10
run;
I want to interpolate values for columns column_11 to column_15 (the missing values between column_10 and column_16) based on formula
column_11 = column_10 + (((Column_16 - Column_10)/(16-10))*(11-10));
The Start denotes the column_name from which the interpolation(filling of missing values) should start. It will always have a value like column_16.
Thanks!
Something like:
data want;
set one;
array c {11:15} column_11-Column_15;
do i = 11 to 15;
if missing c[i] then c[i] = column_10 + (((Column_16 - Column_10)/(16-10))*( i -10));
end;
drop i;
run;
perhaps.
This program will fill in all missing values, even if there are multiple sequences of missing values. But it assumes that at least the COLUMN1 and COLUMN16 values are non-misisng.
data want (drop=diff ncol c begcol endcol);
set have;
array col{*} column_1-column_16;
begcol=whichn(.,of col{*});
do while (1<begcol<dim(cols));
/*find the last missing value in endcol*/
do endcol=begcol to dim(col)-1 until (col{endcol+1}^=.); end;
diff=col{endcol+1}-col{begcol-1};
ncols=1+endcol-begcol;
do c=1 to ncols;
col{begcol+(c-1)} = col{begcol-1} + diff*(c/ncols);
end;
begcol=whichn(.,of col{*});
end;
run;
Transpose your data and use PROC REG to fill in the missing values instead.
But proc reg will use ALL the non-missing values to make estimates. I think the OP just wants to use the nearest non-missing value on each end of the missing sequence.
Maybe. I suspect the exact methodology isn't that specific.
See the SAS Usage Note about interpolation. For a more mathematical treatment, see the article "Linear interpolation in SAS", which also provides links to other SAS techniques.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.