RE: st: RE: looping panel data

[Nick Cox <n.j.cox@durham.ac.uk>]

I don't know a way to do this all in -egen-. As far as -egen- is concerned the argument supplied is a single expression. 

But it sounds like 

gen total = 0 
gen work = . 

foreach j in 9401000  9401870 9402500 9402600 9405400 9408160 9410100 9410200 9450850 9451200 9451000 { 
	replace work = exp_share`j' * avgp`j' 
      replace total = total + work if !missing(work) 
}

egen P_nt = total(total), by(store week) 

Using an intermediate variable -work- is not essential. 

Nick 
n.j.cox@durham.ac.uk 


-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Tomáš Houška
Sent: 04 October 2011 12:50
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: RE: looping panel data

Hi Nick,

thank you very much for the answer and for pointing out the typo. I
forgot about --by-- option with --egen-- command.

One more thing I would like overcome is that when any observation of
any of the variables in the expresion is considered missing, the
result for that whole row is zero - intuitivelly it should skip
summing up of missing values and just give the result of the rest of
summing the expression. But even when only one multiplication contains
missing values, the overall result for that row is zero. That is the
result is zero even though there are pairs of prices and exp_shares
that would produce a real number after multiplication. Is there some
way to deal with that?
I have tried replacing all the missing obs in all variables with zeros
and then I get an actual number in the sum - but I feel it would be
cleaner to define that in --egen-- commnad rather than to change all
variables involved in the expression.

Thank you!
Tomas




2011/10/4 Nick Cox <n.j.cox@durham.ac.uk>:
> You are a bit confused about -foreach- syntax, but that is secondary. (I think you meant to use `store' within the loop, not `store_number'.)
>
> However, I don't see a need for any loops here. This should be one line of Stata. The basic trick is
>
> egen P_nt = total(expression), by(store week)
>
> Your expression is
>
> exp_share9401000* avgp9401000+
> exp_share9401870* avgp9401000+ exp_share9402500* avgp9402500+
> exp_share9402600* avgp9402600+ exp_share9405400* avgp9405400+
> exp_share9408160* avgp9408160+ exp_share9410100* avgp9410100+
> exp_share9410200* avgp9410200+ exp_share9450850* avgp9450850+
> exp_share9451200* avgp9451200+ exp_share9451000* avgp9451000
>
> except that it looks as if there is a typo there and it should be
>
> exp_share9401000* avgp9401000+
> exp_share9401870* avgp9401870+ exp_share9402500* avgp9402500+
> exp_share9402600* avgp9402600+ exp_share9405400* avgp9405400+
> exp_share9408160* avgp9408160+ exp_share9410100* avgp9410100+
> exp_share9410200* avgp9410200+ exp_share9450850* avgp9450850+
> exp_share9451200* avgp9451200+ exp_share9451000* avgp9451000
>
> Nick
> n.j.cox@durham.ac.uk
>
> Tomáš Houška
>
> I would like to ask you for a help with my looping problem. I have
> sales panel data with variables week ID, store ID, product ID, price,
> expenditure_share (share of expenditures for a given product across
> all products in dataset)
>
> I would like to compute Stone price index defined as P_nt=SUM
> (expenditure_share_int * P_int ). This means that for each combination
> of store (n) and week (t) I need to compute a multiple of
> expenditure_share and price for each product (i) and then sum it up
> over all products sold in the particular store that week. The result
> is again a panel data variable P_nt which is a price index for each
> week and store. It is an expenditure system index, so the more you
> spend on more expensive products (keeping prices constant), the higher
> the index.
>
> And I cannot figure out a way how to calculate P_nt index. I have come
> up with a loop, which saves the results in a new dataset, but it is
> not working. I would be very glad for any help on how to calculate
> this variable.
>
> Here is my looping command (j=week number, store_number is a local
> macro with list of store numbers - they are from 2-131 but with
> missing values (i.e. not consecutive))
>
> postfile index_file store week index using stone_index_res, replace
> foreach store in `store_number' {
>    forvalues j=91/317 {
>         gen index`store_number'`j' = exp_share9401000* avgp9401000+
> exp_share9401870* avgp9401000+ exp_share9402500* avgp9402500+
> exp_share9402600* avgp9402600+ exp_share9405400* avgp9405400+
> exp_share9408160* avgp9408160+ exp_share9410100* avgp9410100+
> exp_share9410200* avgp9410200+ exp_share9450850* avgp9450850+
> exp_share9451200* avgp9451200+ exp_share9451000* avgp9451000 if
> week==`j' & store==`store_number'
>          post index_file (`week') (`store') (index`store_number'`j')
>         }
> }
> postclose index_file
>

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/