Creating new variable based on observations in 3 dimensional panel data

[Elsa Horn]

Hi!

I have 3 dimensional data which looks as follows:

(1) Prices of 3000 different products, observed in (2) 21 different regions over (3) six years.

I need to create an instrumental variable for my analysis - I need the mean price of each product in the same year but averaged for all other regions.

I.e. for product x sold at price y in region z at time t, I want my instrument to tell me the mean price "y_hat" of product x at time t for all regions Z - z.

What ways are there to go about this?

Thank you in advance,

Elsa

[Robert Picard]

You are a bit vague about your data. The following assumes that there's only one instance per year and region for any given product. You'll need to install rangestat (from SSC). The intuition is to group observations by product and year and then calculate the mean price excluding the price of the current observation.

clear
set seed 432423
set obs 3000
gen product = _n
expand 21
bysort product: gen region = _n
expand 6
bysort product region: gen year = _n
gen price = runiform()

rangestat (mean) price, interval(region . .) by(product year) excludeself

* spot check results for the product in observation 1
sum price if year == year[1] & product == product[1] & region != region[1]
list in 1

* spot check results for the product in observation 500
sum price if year == year[500] & product == product[500] & region != region[500]
list in 500

and the results

. * spot check results for the product in observation 1
. sum price if year == year[1] & product == product[1] & region != region[1]

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
       price |         20    .4982441     .246741   .1396611   .9693198

. list in 1

     +------------------------------------------------+
     | product   region   year      price   price_m~n |
     |------------------------------------------------|
  1. |       1        1      1   .8824946   .49824407 |
     +------------------------------------------------+

.
. * spot check results for the product in observation 500
. sum price if year == year[500] & product == product[500] & region != region[500]

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
       price |         20    .5860424     .281782    .048788   .9678169

. list in 500

     +-----------------------------------------------+
     | product   region   year      price   price_~n |
     |-----------------------------------------------|
500. |       4       21      2   .0019944   .5860424 |
     +-----------------------------------------------+

[Elsa Horn]

Dear Robert,

Thank you very much for your response. Sorry I wasn't being clear, but yes, the data set is balanced i.e. each product is sold in every period in every region.

However, I can't seem to make the code you provided work. It simply deletes all of my observation and generates a mean price averaging around 0,5. I'm guessing there is something I have misunderstood in how to run the code?

I am now only using a subset of my data which is 3696 observations and in total 27 products.

Best regards and thank you again,

Elsa

[Nick Cox]

A report of "doesn't work" unfortunately doesn't help us answer.

rangestat (SSC; I am on the team) never deletes observations if by delete you mean drop. At most it might ignore them.

You need to show us your exact code with an example big enough to show concretely what doesn't do what you expect. Please review FAQ Advice #12.