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 09:49:39 +0100

```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
>
> Would this be possible?
>
> Many thanks,
> Lisa
> *
> *   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/
*
*   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/
```