Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# Re: st: Subtract Closest Cell Which has A Value

 From Nick Cox To statalist@hsphsun2.harvard.edu Subject Re: st: Subtract Closest Cell Which has A Value Date Fri, 26 Oct 2012 20:06:33 +0100

```All that said, the first step here is an interpolation that treats
each series as a step function. Have you considered other
interpolation methods, including

linear   (official -ipolate-)

cubic   (SSC -cipolate)

cubic spline (SSC -csipolate-)

?

Nick

On Fri, Oct 26, 2012 at 9:49 AM, Nick Cox <njcoxstata@gmail.com> wrote:
> This can be solved using the same techniques as for your previous problem.
>
> See my previous posting.
>
> You need a variable that is the previously observed value and one that
> is the next observed value. You get those variables by copying down in
> a cascade in the usual (panel id, time) order and then again after
> reversing time.
>
> Then you use the previous value if the timeline date is positive and
> the next value if it is negative.
>
>
> FAQ     . . . . . . . . . . . . . . . . . . . . . . . Replacing missing values
>         . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
>         2/03    How can I replace missing values with previous or
>                 following nonmissing values?
>                 http://www.stata.com/support/faqs/data/missing.html
>
> (Reversing time is one of the top tricks many people need occasionally
> in Stata but often don't seem to know about.)
>
> NIck
>
> On Fri, Oct 26, 2012 at 3:41 AM, Lisa Wang <lhwang0925@gmail.com> wrote:
>> Dear all,
>>
>> I have another question. I have two columns of values - variables A
>> and B to make it simple. I would like to A-B in a new column C;
>> however, column B has a lot of missing values (.), so it doesn't seem
>> that simple. Variable A is complete, so no missing data. I would like
>> Stata to find the nearest cell (data is already sorted by column D)
>> with a value in column B to subtract off for each panel that I have.
>>
>> For instance,
>>
>> 51      .       31
>> 50      .       30
>> 47      20      27
>> 51      .       31
>> 25      .       5
>> 49      .       29
>> 45      .       25
>> 24      .       4
>> 51      .       1
>> 22      .       -28
>> 27      .       -23
>> 57      .       7
>> 20      .       -30
>> 57      50      7
>> 59      .       9
>> 33      .       -17
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/
```