Fwd: Re: st: Collapsing Over Limited Set

[Nick Winter <nw53@cornell.edu>]

Sorry ... sent the last email by mistake.  Trying again:

I believe this will do it:

  bysort product: egen n_p = count( markup<. & company<. )
  by product: egen sum_p = sum( markup * !mi(company) )

  bysort company product : egen n_cp = count( markup<. )
  by company product: egen sum_cp = sum(markup)

  gen averagemarkup = (sum_p-sum_cp) / (n_p-n_cp)


If you know you don't have missing values on company or on markup, 
then you can simplify:

  bysort product: gen n_p = _N
  by product: egen sum_p = sum(markup)


  bysort company product : gen n_cp = _N
  by company product: egen sum_cp = sum(markup)

  gen averagemarkup = (sum_p-sum_cp) / (n_p-n_cp)


That is, get the sum and number of observations for each product 
overall, then get the sum and number of observations for each 
company-product pair...then subtract off these latter quantities from 
the former to calculate the average you want.

Note in the first approach, "markup<." means "nonmissing on markup", 
but the egen function count() doesn't allow the missing() function to 
appear within it.



--Nick Winter







> At 03:11 PM 9/15/2005, you wrote:
> > Hi there,
> >
> > I would really appreciate someone's help with this question.
> >
> > I'm trying to generate a dataset of statistics by collapsing another
> > dataset, but each of the id's I'm collapsing by, I want to use every
> > observation in the dataset except the one for the id under consideration.
> > Would there be a way to do this?
> >
> > For example, I have data of the form:
> >
> > company product markup
> > 100     31      .3
> > 100     55      .2
> > 111     31      0
> > 111     55      .1
> > 120     31      .1
> > 120     55      .1
> >
> > Now I want to ask the question: for each company, calculate the average
> > markup of each product it produces, where the average is taken over all
> > companies that sell the product except the company itself. So I want to
> > end up with
> >
> > company product averagemarkup
> > 100     31      .05
> > 100     55      .1
> > 111     31      .2
> > 111     55      .15
> > 120     31      .15
> > 120     55      .15
> >
> > Obviously collapsing the data the standard way is not going to do this. I
> > need to do this for hundreds of thousands of observations (hundreds of
> > companies and thousands of products) so am looking for a way to do this
> > that would be relatively quick. I would be grateful for any suggestions.
> >
> > Thanks very much.
> >
> > Jason
> > *
> > *   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/
> ________________________________________________________
> Nicholas J. G. Winter                     607.255.8819 t
> Assistant Professor                       607.255.4530 f
> Department of Government              nw53@cornell.edu e
> Cornell University        falcon.arts.cornell.edu/nw53 w
> 308 White Hall
> Ithaca, NY 14853-4601
________________________________________________________
Nicholas J. G. Winter                     607.255.8819 t
Assistant Professor                       607.255.4530 f
Department of Government              nw53@cornell.edu e
Cornell University        falcon.arts.cornell.edu/nw53 w
308 White Hall
Ithaca, NY 14853-4601 

*
*   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/