# st: RE: Re: Panel data manipulation question

 From "Nick Cox" <[email protected]> To <[email protected]> Subject st: RE: Re: Panel data manipulation question Date Tue, 16 Jul 2002 10:30:46 +0100

```Taggert J Brooks <[email protected]>

> I can't seem to figure out how to do this easily,
> although I'm quite sure
> it must be possible.  I have a panel data set on individual
> countries and I
> want to create a new variable.  The variable is going to be
> the ratio of a
> variable I already have in my dataset, we'll call it P.  I
> want to create a
> variable we'll call X where x=p/p(i==72).  That is to say I
> want to deflate
> all the values of p, by the value of p for country 72, for
> the corresponding
> year.  I thought about trying to grab the time series for
> that specific
> country and appending it to every other country, but that
> seems awkward.
> I've thought about reshaping the data, but that seems
> tedious as well.  Any
> suggestions are always appreciated.

Scott Merryman

> I think something like this should work.  Using data
> available from Stata
> Press website:
>
> use http://www.stata-press.com/data/r7/grunfeld.dta, clear
> gen m10 = mv if company == 10
> for num 180(-20)20 : replace m10 = m10[_n +X ] if  m10== .
> gen X = mv / m10
>
> This will create variable X, the market value (mv) of each
> firm divided by
> the market value of firm 10.

Scott's neat idea can be implemented this way, which allows
the general structure to be seen differently.

Step 1
======

One company (country, whatever) is chosen as reference:

gen M10 = mv if company == 10

Step 2
======

We need to smear that variable over all other
companies. Scott's approach exploits the fact
that the data occur in blocks of 20 in the
data set chosen as example.

Another equivalent way to do this is to
see that at most one company is non-missing on -M10-
for each -time-. Therefore after a sort it will
be the first value for each -time-:

bysort time (M10) : replace M10 = M10[1]

If the reference value is in fact missing
this still holds.

Step 3
======

Express each value as a ratio as desired:

gen X2 = mv / M10

Nick
[email protected]
*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```