Hi, I am trying to add last 2 columns in my data "Time_yrs_lower_limit' and "Time_yrs_higher_limit". For doing this, here are the steps 1. Identify previous value of 'Time_yrs' for which 'Rate' value is not null 2. Put the value in cell "Time_yrs_lower_limit' 3.Identify next value of 'Time_yrs' for which 'Rate' value is not null 4. Put the value in cell "Time_yrs_higher_limit'   DATE Time_yrs Rate Time_yrs_Lower_limit Time_yrs_higher_limit 31-May-21 0.999315537 0.166285199     15-Mar-22 1.787816564   0.999315537 1.998631075 15-Apr-22 1.872689938   0.999315537 1.998631075 15-May-22 1.954825462   0.999315537 1.998631075 31-May-22 1.998631075 0.210218179         Here is the code data have; INFILE DATALINES DSD; INPUT DATE : DATE9. Time_yrs Rate ; FORMAT DATE DATE9. ; DATALINES; 31May2021, 0.999315537, 0.166285199 15Mar2022, 1.787816564, 15Apr2022, 1.872689938, 15May2022, 1.954825462, 31May2022, 1.998631075, 0.210218179 , ; run; Thank you.   ... View more

Hi, I am having a dataset which has a column 'RATE'  with some missing values and my objective is to populate that column. To fill the missing values, I need to extrapolate some values and interpolate some. 1. For a particular 'CURRENCY' the first missing values and last missing values will be exactly the same as the nearest starting or ending value for the same CURRENCY. 2. For middle missing values, I need to calculate 2 different weights. Weight 1 = (Nearest higher PERIOD_YRS - PERIOD_YRS) / ((Nearest higher PERIOD - (Nearest lower PERIOD_YRS). Weight 2 = 1 - Weight1 Expected value = (Weight 1 * NEAREST LOWER PERIOD_YRS) + (Weight 2 * NEAREST HIGHER PERIOD_YRS) Here is the sample data CURRENCY SEQ PERIOD COUNTER_IN_GROUP PERIOD_YRS RATE EXP RESULT Weight1 Weight2 AUD 1 3_MONTH 1 0.25   3.689629437     AUD 1 2_YEAR 2 2   3.689629437     AUD 1 20_YEAR 3 20 3.689629437 3.689629437     CAD 2 6_MONTH 1 0.5   0.65523605     CAD 2 1_YEAR 2 1 0.65523605 0.65523605     CAD 2 3_YEAR 3 3   1.028227829 0.333333333 0.666666667 CAD 2 4_YEAR 4 4 1.214723719 1.214723719     CAD 2 5_YEAR 5 5   1.389134405 0.666666667 0.333333333 CAD 2 7_YEAR 6 7 1.737955777 1.737955777     CAD 2 10_YEAR 7 10   1.737955777       Weight 1 = (4YR - 3YR)/(4YR - 1YR) Weight 2 = (1 - Weight 1)   My SAS Code   proc transpose data= WORK.Sample out=trnspose prefix=_yc;   by SEQ;   id COUNTER_IN_GROUP;   var RATE WEIGHT; RUN; DATA WORK.CONSOL_3 (drop=_:);   set WORK.CONSOL_2 ;   if COUNTER_IN_GROUP=1 then do;  /* Read and correct the transposed set of rate values */     set trnspose (keep=_yc:);     array _yc {*} _yc: ;     /* fill in leading missings */     _x=coalesce(of _yc{*});          /* Get first non-missing */     do _s=1 by 1 while (_yc{_s}=.);       _yc{_s}=_x;     end;         do _s=dim(_yc) by -1 while (_yc{_s}=.);      /* Get index of last non-missing*/     end;     if _s<dim(_yc) then do _s=_s+1 to dim(_yc);  /* Fill in trailing missings    */       _yc{_s}=_yc{_s-1};     end;     /* Fill in interior holes - straight line interpolation */     do while (nmiss(of _yc{*})>0);       do _before_s=1 by 1 until(_yc{_before_s+1}=.);        /* find SEQ before the hole*/       end;       do _after_s=_before_s+1 by 1 while (_yc{_after_s}=.); /* find SEQ after the hole */       end;       _slope= (_yc{_after_s}-_yc{_before_s}) / (_after_s-_before_s) ;       do _s=_before_s+1 to _after_s-1;    /* Traverse the hole */         _yc{_s} = _yc{_before_s} + _slope * (_s - _before_s);       end;     end;   end;   if RATE=. then RATE =_yc{seq}; run;       ... View more

Hi,   I have a dataset which has missing values in a column. If the missing value is in the first cell of the column it will be equal to the next value available. If the missing value is in 3rd, 4th, 5th cell, then the 3rd value will be equal to (2/3)rd of 2nd val and (1/3)rd of 6th cell, 4th value is 50% of each and so on. can someone help?   thank you. ... View more