Calculating means for every year and then the average percentage change

[Kamil Kisiel]

Hi,

I am newbee at stata. I have a panel data for different states and years (from 1970 to 2003). I need to calculate an average percentage change of cigarettes consumption. The data looks like:
year state cigarettes population
1970 A 100 10
1970 B 90 8
1980 A 110 11
1980 B 95 9
1990 A 105 12
1990 B 90 10
2000 A 115 13
2000 B 100 11

First, I need to summarize per capita consumption of cigarettes in every year, also sum of consumption in all states divided by their sum of populations, then storage the results (also save mean for every year), and then calculate the average percentage change.

Best regards,
K.

[Nick Cox]

This is a little sketchy, but here is some technique.

clear 
input year str1 state cons pop
1970 A 100 10
1970 B 90 8
1980 A 110 11
1980 B 95 9
1990 A 105 12
1990 B 90 10
2000 A 115 13
2000 B 100 11
end 

egen tot_cons = total(cons), by(year) 
egen tot_pop = total(pop), by(year)
gen pc = tot_cons/tot_pop 

egen tag = tag(year) 

sort tag year 
by tag: gen pc_change = 100 * (pc - pc[_n-1]) / pc[_n-1] if tag 

list, sepby(tag) 

     +-----------------------------------------------------------------+
  1. | year | state | cons | pop | tot_cons | tot_pop |       pc | tag |
     | 1970 |     B |   90 |   8 |      190 |      18 | 10.55556 |   0 |
     |-----------------------------------------------------------------|
     |                            pc_change                            |
     |                                    .                            |
     +-----------------------------------------------------------------+

     +-----------------------------------------------------------------+
  2. | year | state | cons | pop | tot_cons | tot_pop |       pc | tag |
     | 1980 |     B |   95 |   9 |      205 |      20 |    10.25 |   0 |
     |-----------------------------------------------------------------|
     |                            pc_change                            |
     |                                    .                            |
     +-----------------------------------------------------------------+

     +-----------------------------------------------------------------+
  3. | year | state | cons | pop | tot_cons | tot_pop |       pc | tag |
     | 1990 |     B |   90 |  10 |      195 |      22 | 8.863636 |   0 |
     |-----------------------------------------------------------------|
     |                            pc_change                            |
     |                                    .                            |
     +-----------------------------------------------------------------+

     +-----------------------------------------------------------------+
  4. | year | state | cons | pop | tot_cons | tot_pop |       pc | tag |
     | 2000 |     B |  100 |  11 |      215 |      24 | 8.958333 |   0 |
     |-----------------------------------------------------------------|
     |                            pc_change                            |
     |                                    .                            |
     +-----------------------------------------------------------------+

     +-----------------------------------------------------------------+
  5. | year | state | cons | pop | tot_cons | tot_pop |       pc | tag |
     | 1970 |     A |  100 |  10 |      190 |      18 | 10.55556 |   1 |
     |-----------------------------------------------------------------|
     |                            pc_change                            |
     |                                    .                            |
     +-----------------------------------------------------------------+

     +-----------------------------------------------------------------+
  6. | year | state | cons | pop | tot_cons | tot_pop |       pc | tag |
     | 1980 |     A |  110 |  11 |      205 |      20 |    10.25 |   1 |
     |-----------------------------------------------------------------|
     |                            pc_change                            |
     |                            -2.894735                            |
     +-----------------------------------------------------------------+

     +-----------------------------------------------------------------+
  7. | year | state | cons | pop | tot_cons | tot_pop |       pc | tag |
     | 1990 |     A |  105 |  12 |      195 |      22 | 8.863636 |   1 |
     |-----------------------------------------------------------------|
     |                            pc_change                            |
     |                             -13.5255                            |
     +-----------------------------------------------------------------+

     +-----------------------------------------------------------------+
  8. | year | state | cons | pop | tot_cons | tot_pop |       pc | tag |
     | 2000 |     A |  115 |  13 |      215 |      24 | 8.958333 |   1 |
     |-----------------------------------------------------------------|
     |                            pc_change                            |
     |                             1.068376                            |
     +-----------------------------------------------------------------+