Generate a new var

[Jade Li]

Dear all,


I have a panel data between 1997and 2010. I could only provide an example and not the data I am using.

I have this dataex:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(period firm_id1 firm_id2) float expenditure
2018 110 110 1500
2018 110 120 3000
2018 110 130 4500
2018 120 110 7500
2018 120 120 1500
2018 120 130 9000
2018 130 110 3750
2018 130 120 2250
2018 130 130 4500
end

--------------

I apply this code to get:

bysort firm_id1 period : egen sum_id1=total(expenditure)

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(period firm_id1 firm_id2) float(expenditure sum_id1)
2018 110 110 1500  9000
2018 110 120 3000  9000
2018 110 130 4500  9000
2018 120 110 7500 18000
2018 120 120 1500 18000
2018 120 130 9000 18000
2018 130 110 3750 10500
2018 130 120 2250 10500
2018 130 130 4500 10500
end

I need to get the expenditures from the perspective of firm2 such that I use the same code above, but obtain this data:
I generated "expenditure_2_01 " manually, but I still need the right code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(period firm_id1 firm_id2 expenditure sum_id1 f_id2 f_id1 expenditure_2_01)
2018 110 110 1500  9000 110 110 1500
2018 110 120 3000  9000 110 120 7500
2018 110 130 4500  9000 110 130 3750
2018 120 110 7500 18000 120 110 3000
2018 120 120 1500 18000 120 120 1500
2018 120 130 9000 18000 120 130 2250
2018 130 110 3750 10500 130 110 4500
2018 130 120 2250 10500 130 120 9000
2018 130 130 4500 10500 130 130 4500
end

then I will use this code:

bysort f_id2 period : egen sum_id2=total(expenditure_2_01)

I will obtain

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(period firm_id1 firm_id2 expenditure sum_id1 f_id2 f_id1 expenditure_2_01) float sum_id2
2018 110 110 1500  9000 110 110 1500 12750
2018 110 120 3000  9000 110 120 7500 12750
2018 110 130 4500  9000 110 130 3750 12750
2018 120 110 7500 18000 120 110 3000  6750
2018 120 120 1500 18000 120 120 1500  6750
2018 120 130 9000 18000 120 130 2250  6750
2018 130 110 3750 10500 130 110 4500 18000
2018 130 120 2250 10500 130 120 9000 18000
2018 130 130 4500 10500 130 130 4500 18000
end

------------------ copy up to and including the previous line ------------------

Please could you tell how to generate the variable "expenditure_2_01 "?

Thank you,

[Carlo Lazzaro]

Jade:
do you mean something along the following lines?:

. foreach var of varlist firm_id1-firm_id2  {
  2. bysort `var' period : egen sum_`var'=total(expenditure)
  3.  }

. list

     +---------------------------------------------------------------+
     | period   firm_id1   firm_id2   expend~e   sum_fi~1   sum_fi~2 |
     |---------------------------------------------------------------|
  1. |   2018        130        110       3750      10500      12750 |
  2. |   2018        110        110       1500       9000      12750 |
  3. |   2018        120        110       7500      18000      12750 |
  4. |   2018        120        120       1500      18000       6750 |
  5. |   2018        130        120       2250      10500       6750 |
     |---------------------------------------------------------------|
  6. |   2018        110        120       3000       9000       6750 |
  7. |   2018        120        130       9000      18000      18000 |
  8. |   2018        110        130       4500       9000      18000 |
  9. |   2018        130        130       4500      10500      18000 |
     +---------------------------------------------------------------+

.

[Jade Li]

Dear Carlo, Thank you for your reply. In fact, it is exactly what I wanted!

[Jade Li]

Dear Carlos,
Can we adjust the code to obtain the variable "expenditure_2_01 "as well?

[Carlo Lazzaro]

Jade:
provided that I'm unclear with what you're after, please find below a tentative reply:

. foreach var of varlist firm_id1- firm_id2 {
  2. bysort `var' period : egen sum_`var'=total( expenditure_2_01 )
  3.   }

. list

     +----------------------------------------------------------------------------------------------------+
     | period   firm_id1   firm_id2   expend~e   sum_id1   f_id2   f_id1   expen~01   sum_fi~1   sum_fi~2 |
     |----------------------------------------------------------------------------------------------------|
  1. |   2018        130        110       3750     10500     130     110       4500      18000       9000 |
  2. |   2018        110        110       1500      9000     110     110       1500      12750       9000 |
  3. |   2018        120        110       7500     18000     120     110       3000       6750       9000 |
  4. |   2018        110        120       3000      9000     110     120       7500      12750      18000 |
  5. |   2018        130        120       2250     10500     130     120       9000      18000      18000 |
     |----------------------------------------------------------------------------------------------------|
  6. |   2018        120        120       1500     18000     120     120       1500       6750      18000 |
  7. |   2018        110        130       4500      9000     110     130       3750      12750      10500 |
  8. |   2018        120        130       9000     18000     120     130       2250       6750      10500 |
  9. |   2018        130        130       4500     10500     130     130       4500      18000      10500 |
     +----------------------------------------------------------------------------------------------------+

.

[Jade Li]

Dear Carlos,
Thank you. Pardon, I did not make it clearer. I need a code that generates "expenditure_2_01". I can generate the sum_`var' now, that's not an issue. The "expenditure_2_01" in the example above is not generated by stata. I have just added the values manually.

[Nick Cox]

This may help. See https://journals.sagepub.com/doi/pdf...867X0800800414 for the main twist.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(period firm_id1 firm_id2 expenditure sum_id1 f_id2 f_id1 expenditure_2_01)
2018 110 110 1500  9000 110 110 1500
2018 110 120 3000  9000 110 120 7500
2018 110 130 4500  9000 110 130 3750
2018 120 110 7500 18000 120 110 3000
2018 120 120 1500 18000 120 120 1500
2018 120 130 9000 18000 120 130 2250
2018 130 110 3750 10500 130 110 4500
2018 130 120 2250 10500 130 120 9000
2018 130 130 4500 10500 130 130 4500
end

gen first = cond(firm_id1 <= firm_id2, firm_id1, firm_id2)
gen second = cond(firm_id1 > firm_id2, firm_id1, firm_id2)

bysort period first second : gen wanted = cond(_N == 1, expenditure, expenditure[3 - _n])

list period first second ex* wanted, sepby(period first second)

     +--------------------------------------------------------+
     | period   first   second   expend~e   expen~01   wanted |
     |--------------------------------------------------------|
  1. |   2018     110      110       1500       1500     1500 |
     |--------------------------------------------------------|
  2. |   2018     110      120       7500       3000     3000 |
  3. |   2018     110      120       3000       7500     7500 |
     |--------------------------------------------------------|
  4. |   2018     110      130       3750       4500     4500 |
  5. |   2018     110      130       4500       3750     3750 |
     |--------------------------------------------------------|
  6. |   2018     120      120       1500       1500     1500 |
     |--------------------------------------------------------|
  7. |   2018     120      130       9000       2250     2250 |
  8. |   2018     120      130       2250       9000     9000 |
     |--------------------------------------------------------|
  9. |   2018     130      130       4500       4500     4500 |
     +--------------------------------------------------------+

[Jade Li]

Dear Nick Cox, thank you very much!