generate new variables ( dividing variable by specific obs)

[Budu Gulo]

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(date amzn ibm)
18627    180 146.76
18630 184.22 147.48
18631 185.01 147.64
18632 187.42 147.05
18633 185.86 148.66
18634 185.49 147.93
18637 184.68 147.64
18638 184.34 147.28
18639 184.08  149.1
18640 185.53 148.82
end
format %td date

. list date amzn ibm in 1/10

     +-----------------------------+
     |      date     amzn      ibm |
     |-----------------------------|
  1. | 31dec2010      180   146.76 |
  2. | 03jan2011   184.22   147.48 |
  3. | 04jan2011   185.01   147.64 |
  4. | 05jan2011   187.42   147.05 |
  5. | 06jan2011   185.86   148.66 |
     |-----------------------------|
  6. | 07jan2011   185.49   147.93 |
  7. | 10jan2011   184.68   147.64 |
  8. | 11jan2011   184.34   147.28 |
  9. | 12jan2011   184.08    149.1 |
 10. | 13jan2011   185.53   148.82 |
     +-----------------------------+

I would like to generate two variables amzidx and ibmidx: amzidx will be amzn divided by 1st obs of amzn (180) and ibmidx will be ibm divided by 1st obs of ibm (146.76). It should look like the following.

. list in 1/10

     +---------------------------------------------------+
     |      date     amzn      ibm    amznidx     ibmidx |
     |---------------------------------------------------|
  1. | 31dec2010      180   146.76          1          1 |
  2. | 03jan2011   184.22   147.48   1.023444   1.004906 |
  3. | 04jan2011   185.01   147.64   1.027833   1.005996 |
  4. | 05jan2011   187.42   147.05   1.041222   1.001976 |
  5. | 06jan2011   185.86   148.66   1.032556   1.012946 |
     |---------------------------------------------------|
  6. | 07jan2011   185.49   147.93     1.0305   1.007972 |
  7. | 10jan2011   184.68   147.64      1.026   1.005996 |
  8. | 11jan2011   184.34   147.28   1.024111   1.003543 |
  9. | 12jan2011   184.08    149.1   1.022667   1.015944 |
 10. | 13jan2011   185.53   148.82   1.030722   1.014037 |
     +---------------------------------------------------+

[Sergiy Radyakin]

Clyde has already shown you how to address the first observation in this thread.

generate double amzidx=amzn/amzn[1]
generate double ibmidx=ibm/ibm[1]

[Budu Gulo]

Sergiy Radyakin Many thanks! The code works perfectly. Suppose, there are many more variables. Instead of generating variables one bye one, how can I implement your code in a general setting?

[David Benson]

One of two ways:

foreach v of varlist ibm amzn fb aapl goog {
gen double `v'_idx = `v'/ `v'[1]
}

* OR, you could put all the variables in a local macro
local myvars "ibm amzn fb aapl goog yhoo orcl ge gm f"
foreach v of local myvars {
gen double `v'_idx = `v'/ `v'[1]
}

[Budu Gulo]

David Benson Thanks a lot! The first option yields the desired output. But, the second option gives me invalid syntax error.

[David Benson]

Sorry, the word "of" was repeated twice in the second loop. (I edited the post to delete it). See if it works now.

[Budu Gulo]

David Benson : Thanks! It works now.

Suppose the data is in long format. How could I generate a new variable price_idx, which divides price by initial price of each firm?

* Example generated by -dataex-. To install: ssc install dataex
clear
input float date str4 firm_id float price
18627 "amzn"    180
18627 "ibm"  146.76
18630 "amzn" 184.22
18630 "ibm"  147.48
18631 "amzn" 185.01
18631 "ibm"  147.64
18632 "amzn" 187.42
18632 "ibm"  147.05
18633 "amzn" 185.86
18633 "ibm"  148.66
18634 "amzn" 185.49
18634 "ibm"  147.93
18637 "amzn" 184.68
18637 "ibm"  147.64
18638 "amzn" 184.34
18638 "ibm"  147.28
18639 "amzn" 184.08
18639 "ibm"   149.1
18640 "amzn" 185.53
18640 "ibm"  148.82
end
format %td date

[Nick Cox]

Is this what you want? See https://www.stata-journal.com/sjpdf....iclenum=dm0055 for a survey of related technique.

. bysort firm_id (date) : gen wanted = price/price[1] 

. 
. list, sepby(firm_id) 

     +-----------------------------------------+
     |      date   firm_id    price     wanted |
     |-----------------------------------------|
  1. | 31dec2010      amzn      180          1 |
  2. | 03jan2011      amzn   184.22   1.023444 |
  3. | 04jan2011      amzn   185.01   1.027833 |
  4. | 05jan2011      amzn   187.42   1.041222 |
  5. | 06jan2011      amzn   185.86   1.032556 |
  6. | 07jan2011      amzn   185.49     1.0305 |
  7. | 10jan2011      amzn   184.68      1.026 |
  8. | 11jan2011      amzn   184.34   1.024111 |
  9. | 12jan2011      amzn   184.08   1.022667 |
 10. | 13jan2011      amzn   185.53   1.030722 |
     |-----------------------------------------|
 11. | 31dec2010       ibm   146.76          1 |
 12. | 03jan2011       ibm   147.48   1.004906 |
 13. | 04jan2011       ibm   147.64   1.005996 |
 14. | 05jan2011       ibm   147.05   1.001976 |
 15. | 06jan2011       ibm   148.66   1.012946 |
 16. | 07jan2011       ibm   147.93   1.007972 |
 17. | 10jan2011       ibm   147.64   1.005996 |
 18. | 11jan2011       ibm   147.28   1.003543 |
 19. | 12jan2011       ibm    149.1   1.015944 |
 20. | 13jan2011       ibm   148.82   1.014037 |
     +-----------------------------------------+

[Budu Gulo]

Nick Cox : Thanks! This is what I wanted.

[Rachel Chan]

Hi Sir Nick Cox I hope you could help me too. I have a slightly similar problem, but instead of indexing the observations, I need to index the variables. I have variables Jan_13 to Mar_21, referring to months January 2013 to March 2021. I need to create new variables containing the results of current month/previous month. Ex. Feb_13_PR = Feb_13/Jan_13.

How can I automate this computation for all months through looping? Thank you.

[Nick Cox]

#10 was repeated at https://www.statalist.org/forums/for...hrough-looping