generating new variable

[georgina]

Hi everyone,

I would appreciate if anyone could help me with the following problem:

I have 4 brands - A,B,C,D - for each of them I have monthly advertising expenditure (Adv) and now I want to create a new variable (AdvComp) which is the level of advertising of the main competitor on the market.

For brand A, the main competitor is brand C (for C the main competitor is A), and for brand B the main competitor is brand D.

So I need my new variable, AdvComp, to look like in the example below:

EXAMPLE:

brand data Adv AdvComp

A 01/05/11 5 2
A 02/05/11 5 2
A 03/05/11 6 1
B 01/05/11 4 6
B 02/05/11 4 7
B 03/05/11 3 8
C 01/05/11 2 5
C 02/05/11 2 5
C 03/05/11 1 6
D 01/05/11 6 4
D 02/05/11 7 4
D 03/05/11 8 3


Any insights on how to create my new variable, AdvComp ?

Greatly appreciate any help

Gia

[bukharin]

It seems that there are only 2 "groups" of brands (one group is A + C, the other is B + D)

You could take advantage of this fact as follows:

gen group=brand=="A" | brand=="C" if !missing(brand)
bysort date group: gen advcomp=adv[2] if _n==1 & _N==2
bysort date group: replace advcomp=adv[1] if _n==2 & _N==2

The first line generates a binary indicator, group, which is equal to 1 for brands A + C and 0 for the other brands.

The second line combines the observations by date and group, and for each combo makes advcomp equal to adv of the other observation. _n is the number of the observation within that combo, _N is the total number of observations in that combo (so the & _N==2 is a sanity check to make sure there are only 2 observations per group as expected), and the [1] and [2] refer to the first and second observations in each group.

Result:

. list, compress noobs sep(4)

  +-------------------------+
  |      date   brand   adv |
  |-------------------------|
  | 01may2011       A     5 |
  | 01may2011       B     4 |
  | 01may2011       C     2 |
  | 01may2011       D     6 |
  |-------------------------|
  | 02may2011       A     5 |
  | 02may2011       B     4 |
  | 02may2011       C     2 |
  | 02may2011       D     7 |
  |-------------------------|
  | 03may2011       A     6 |
  | 03may2011       B     3 |
  | 03may2011       C     1 |
  | 03may2011       D     8 |
  +-------------------------+

. gen group=brand=="A" | brand=="C" if !missing(brand)

. bysort date group: gen advcomp=adv[2] if _n==1 & _N==2
(6 missing values generated)

. bysort date group: replace advcomp=adv[1] if _n==2 & _N==2
(6 real changes made)

. sort date brand
. list date brand adv advcomp, compress noobs sep(4)

  +---------------------------------+
  |      date   brand   adv   adv~p |
  |---------------------------------|
  | 01may2011       A     5       2 |
  | 01may2011       B     4       6 |
  | 01may2011       C     2       5 |
  | 01may2011       D     6       4 |
  |---------------------------------|
  | 02may2011       A     5       2 |
  | 02may2011       B     4       7 |
  | 02may2011       C     2       5 |
  | 02may2011       D     7       4 |
  |---------------------------------|
  | 03may2011       A     6       1 |
  | 03may2011       B     3       8 |
  | 03may2011       C     1       6 |
  | 03may2011       D     8       3 |
  +---------------------------------+

[georgina]

Thanks a lot, it works swell