Loop through data with certain var value and replacing the value from another obs

[Hossein Naderi]

Hi everyone. I am a Matlab user new to Stata and I have a question.
I have a dataset with more than 150 thousand observations. It is of the following form:

CompanyID

eventyear

quarter

notAnnual

152440	2009	3	0
152440	2009	3	0
152440	2009	3	0
152440	2009	4	1
152440	2009	4	1
152455	2008	1	0
152455	2008	1	0
152455	2008	1	0
152455	2008	1	0
152455	2009	1	0
152455	2009	1	0
152455	2010	1	0
152455	2010	1	0
152455	2010	1	0
152455	2011	1	0
152455	2011	1	0
152455	2011	3	1
152455	2012	1	0
152455	2012	1	0

I want to create a new variable called newquarter and do the following: if the meeting is annual newquarter = quarter
if the meeting is notAnnual newquarter equals the quarter of an annual observation in the same eventyear. I have the following code (or rather algorithm) which I know won't work due to syntax and is probably not efficient:

gen identifier = .
replace identifier = _n if notAnnual == 1

foreach identifier != . {
tempid = identifier-5/identifier+5
foreach tempid <= identifier+5 {
if (CompanyID[identifier] == CompanyID[tempid] & eventyear[identifier] == eventyear[tempid]) {
replace newquarter[identifier] = quarter[tempid]
tempid = tempid + 6
}
}

}

I appreciate it if you could help me with this.
Thanks.

[Hossein Naderi]

Apparently I don't know how to paste the data here.
So the data above has four variable

CompanyID
eventyear
quarter
notAnnual

[Nick Cox]

I can't follow this. Here is your example so that others can copy and paste more easily.

clear 
input CompanyID eventyear quarter notAnnual
152440    2009    3    0
152440    2009    3    0
152440    2009    3    0
152440    2009    4    1
152440    2009    4    1
152455    2008    1    0
152455    2008    1    0
152455    2008    1    0
152455    2008    1    0
152455    2009    1    0
152455    2009    1    0
152455    2010    1    0
152455    2010    1    0
152455    2010    1    0
152455    2011    1    0
152455    2011    1    0
152455    2011    3    1
152455    2012    1    0
152455    2012    1    0
end 

list , sepby(CompanyID eventyear) 

     +------------------------------------------+
     | Compan~D   eventy~r   quarter   notAnn~l |
     |------------------------------------------|
  1. |   152440       2009         3          0 |
  2. |   152440       2009         3          0 |
  3. |   152440       2009         3          0 |
  4. |   152440       2009         4          1 |
  5. |   152440       2009         4          1 |
     |------------------------------------------|
  6. |   152455       2008         1          0 |
  7. |   152455       2008         1          0 |
  8. |   152455       2008         1          0 |
  9. |   152455       2008         1          0 |
     |------------------------------------------|
 10. |   152455       2009         1          0 |
 11. |   152455       2009         1          0 |
     |------------------------------------------|
 12. |   152455       2010         1          0 |
 13. |   152455       2010         1          0 |
 14. |   152455       2010         1          0 |
     |------------------------------------------|
 15. |   152455       2011         1          0 |
 16. |   152455       2011         1          0 |
 17. |   152455       2011         3          1 |
     |------------------------------------------|
 18. |   152455       2012         1          0 |
 19. |   152455       2012         1          0 |
     +------------------------------------------+

Which observations are "meetings"? What does that mean? How does it relate to the variables you have shown?

[Hossein Naderi]

Thanks for the reply. sorry, I should have been more clear. This data set is about company meetings; so each observation is detail of a company meeting. In my data meetings are usually annual.

about 5% of the meetings aren't annual.

I need to collapse my data by year (meaning I need to average the data yearly ) and in order to do that, I need to assign a single value for quarter per each year. For example:

in observations 1 to 5, the annual meeting's quarter is 3. There are two observations (4 and 5) that aren't annual so I need to change quarter for those observation from 4 to 3.
another example is observation 17. for that observation,the value of quarter needs to change from 3 to 1 .

I hope it's more clear now.

[Hossein Naderi]

so I'd like to generate a variable called newquarter as shown below, but I did it here manually:

CompanyID eventyear quarter notAnnual newquarter
152440 2009 3 0 3
152440 2009 3 0 3
152440 2009 3 0 3
152440 2009 4 1 3
152440 2009 4 1 3
152455 2008 1 0 1
152455 2008 1 0 1
152455 2008 1 0 1
152455 2008 1 0 1
152455 2009 1 0 1
152455 2009 1 0 1
152455 2010 1 0 1
152455 2010 1 0 1
152455 2010 1 0 1
152455 2011 1 0 1
152455 2011 1 0 1
152455 2011 3 1 1
152455 2012 1 0 1
152455 2012 1 0 1

[Hossein Naderi]

anyone?

[Nick Cox]

Still not sure I follow this, but this works for your example

bysort CompanyID eventyear (notAnnual) : gen newquarter = quarter[1]

[Hossein Naderi]

Still not sure I follow this, but this works for your example

bysort CompanyID eventyear (notAnnual) : gen newquarter = quarter[1]

Thanks a lot Nick. This helped a lot. bysort was what I was looking for. :-)