# st: RE: Panel Data Manipulation

 From "Nick Cox" To Subject st: RE: Panel Data Manipulation Date Fri, 4 Nov 2005 17:46:53 -0000

```1.

gen byte sold = volume > 0 & volume < .
bysort sold seller buyer (year) : gen firstyear = year[1] if sold
bysort seller buyer (firstyear) : replace firstyear = firstyear[1]

2.

I did one; now you do one.

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

Jason Hwang

> I would appreciate help with the following panel data manipulation
> problem. I have data of the form:
>
> seller	year	product	buyer	volume
> ...
> 100	1990	1111	200	100
> 100	1991	1111	200	200
> 100	1992	1111	200	250
> 100	1990	2222	200	.
> 100	1991	2222	200	80
> 100	1992	2222	200	83
> 100	1990	2222	500	100
> 100	1991	2222	500	125
> 100	1992	2222	500	200
> ...
>
> The dot in volume means sales volume in that year was zero. I
> would like
> summarize the data by answering two questions. First, each
> seller sells
> many products to many buyers. Let "firstyear" be the first
> year a given
> seller sells anything to a given buyer. In the example above, for the
> seller-buyer pair 100-200, "firstyear" would be 1990. Then
> for every other
> product 100 sells to 200 in subsequent year, I want to
> calculate the gap
> between year of first sale and "firstyear". I would like to generate a
> table of the form:
>
> seller	product buyer timegap	firstyear
> ...
> 100	1111	200	0	1990
> 100	2222	200	1	1991
> 100	2222	300	0	1990
> ...
>
> Second, I would like to ask: for each seller, how much of the
> increase in
> total sales of each product is driven by changes in sales to existing
> buyers or sales to new buyers. I would like to generate a table of the
> form:
>
> seller	product year 	change	same 	new
> ...
> 100	1111	1991	100	100	0
> 100	2222	1991	105	25	80
> ...
>
> Note that from 1990 to 1991, the total sales of 2222
> increased by 105, of
> which 25 came from increasing sales to an existing customer
> 500, and the
> rest 80 came from selling to a new customer 200.
>
> One complication is that sometimes a seller will make sales
> with a given
> buyer in one year, make zero sales the next year, and
> generate positive
> sales another year. In the case of this last year, I would
> like to count
> the sales as coming from "same" customer.
>
> I would very much appreciate your help. Thanks very much.

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