Generate a series of monthly dates by groups

[Yapa Bandara]

Hi,
I have share holdings details of companies as it would look like as follows.

Company	Stock ID
Birla	AAA
Birla	ABB
Birla	ACC
Aditya	AAA
Aditya	ABB
Aditya	ADD

According to this table, Company Birla has 3 stocks AAA, ABB and ACC. Aditya has AAA, ABB and ADD. I have around 1000 such companies with many stocks for each company. I want to create month/ year series of dates for each stock by company from let's say Jan/ 2013 to Dec/ 2013. So it should add these dates to each stock by company and should look like this.

Company	Stock ID	Date
Birla	AAA	2013m1
		2013m2
		2013m4
		2013m5
		2013m6
		2013m7
		2013m8
		2013m9
		2013m10
		2013m11
		2013m12
Birla	ABB	2013m1
		2013m2
		2013m3
		2013m4
		2013m5
		2013m6
		2013m7
		2013m8
		2013m9
		2013m10
		2013m11
		2013m12

Can you please help
Thank you

[Maarten Buis]

. // make an example dataset from your table
. clear all

. input str6 company str3 stockid

       company    stockid
  1. "Birla"         "AAA"
  2. "Birla"         "ABB"
  3. "Birla"         "ACC"
  4. "Aditya"        "AAA"
  5. "Aditya"        "ABB"
  6. "Aditya"        "ADD"
  7. end

.
. // look at the example
. list, sepby(company)

     +-------------------+
     | company   stockid |
     |-------------------|
  1. |   Birla       AAA |
  2. |   Birla       ABB |
  3. |   Birla       ACC |
     |-------------------|
  4. |  Aditya       AAA |
  5. |  Aditya       ABB |
  6. |  Aditya       ADD |
     +-------------------+

.
. // you want to add 12 months for each company stock combination
. expand 12
(66 observations created)

.
. //look at the example data
. sort company stockid

. list if company == "Aditya" & inlist(stockid, "AAA", "ABB"), sepby(stockid)

     +-------------------+
     | company   stockid |
     |-------------------|
  1. |  Aditya       AAA |
  2. |  Aditya       AAA |
  3. |  Aditya       AAA |
  4. |  Aditya       AAA |
  5. |  Aditya       AAA |
  6. |  Aditya       AAA |
  7. |  Aditya       AAA |
  8. |  Aditya       AAA |
  9. |  Aditya       AAA |
 10. |  Aditya       AAA |
 11. |  Aditya       AAA |
 12. |  Aditya       AAA |
     |-------------------|
 13. |  Aditya       ABB |
 14. |  Aditya       ABB |
 15. |  Aditya       ABB |
 16. |  Aditya       ABB |
 17. |  Aditya       ABB |
 18. |  Aditya       ABB |
 19. |  Aditya       ABB |
 20. |  Aditya       ABB |
 21. |  Aditya       ABB |
 22. |  Aditya       ABB |
 23. |  Aditya       ABB |
 24. |  Aditya       ABB |
     +-------------------+

.
. // now we need a Stata date (monthly) variable
. // it should start on January 2013, this is in Stata time:
. di ym(2013,1)
636

.
. // so Ferbruary 2013 = 637, March 2013 = 638, etc
. // _n = 1 for the first observation, 2 for the second, etc
. // So for each company we could create the Stata date we want with
. // _n + 635, that way the first observation is 1 + 635= 636, i.e. January 2013,
. // the second observation is 2 + 635 = 637, i.e. Ferbruary 2013, etc.
.
. bysort company stockid : gen date = _n + 635

.
. // look at the example data
. list if company == "Aditya" & inlist(stockid, "AAA", "ABB"), sepby(stockid)

     +--------------------------+
     | company   stockid   date |
     |--------------------------|
  1. |  Aditya       AAA    636 |
  2. |  Aditya       AAA    637 |
  3. |  Aditya       AAA    638 |
  4. |  Aditya       AAA    639 |
  5. |  Aditya       AAA    640 |
  6. |  Aditya       AAA    641 |
  7. |  Aditya       AAA    642 |
  8. |  Aditya       AAA    643 |
  9. |  Aditya       AAA    644 |
 10. |  Aditya       AAA    645 |
 11. |  Aditya       AAA    646 |
 12. |  Aditya       AAA    647 |
     |--------------------------|
 13. |  Aditya       ABB    636 |
 14. |  Aditya       ABB    637 |
 15. |  Aditya       ABB    638 |
 16. |  Aditya       ABB    639 |
 17. |  Aditya       ABB    640 |
 18. |  Aditya       ABB    641 |
 19. |  Aditya       ABB    642 |
 20. |  Aditya       ABB    643 |
 21. |  Aditya       ABB    644 |
 22. |  Aditya       ABB    645 |
 23. |  Aditya       ABB    646 |
 24. |  Aditya       ABB    647 |
     +--------------------------+

.
. // now it would be nice if the Stata date is displayed in a way that humans
. // can easily read it (In principle I could count the number of months since
. // January 1960, and thus decode that number, but I don't want to)
. format date %tm

. list if company == "Aditya" & inlist(stockid, "AAA", "ABB"), sepby(stockid)

     +-----------------------------+
     | company   stockid      date |
     |-----------------------------|
  1. |  Aditya       AAA    2013m1 |
  2. |  Aditya       AAA    2013m2 |
  3. |  Aditya       AAA    2013m3 |
  4. |  Aditya       AAA    2013m4 |
  5. |  Aditya       AAA    2013m5 |
  6. |  Aditya       AAA    2013m6 |
  7. |  Aditya       AAA    2013m7 |
  8. |  Aditya       AAA    2013m8 |
  9. |  Aditya       AAA    2013m9 |
 10. |  Aditya       AAA   2013m10 |
 11. |  Aditya       AAA   2013m11 |
 12. |  Aditya       AAA   2013m12 |
     |-----------------------------|
 13. |  Aditya       ABB    2013m1 |
 14. |  Aditya       ABB    2013m2 |
 15. |  Aditya       ABB    2013m3 |
 16. |  Aditya       ABB    2013m4 |
 17. |  Aditya       ABB    2013m5 |
 18. |  Aditya       ABB    2013m6 |
 19. |  Aditya       ABB    2013m7 |
 20. |  Aditya       ABB    2013m8 |
 21. |  Aditya       ABB    2013m9 |
 22. |  Aditya       ABB   2013m10 |
 23. |  Aditya       ABB   2013m11 |
 24. |  Aditya       ABB   2013m12 |
     +-----------------------------+

[Yapa Bandara]

Thanks a lot Maarten, this is very helpful