Obtaining annual observations based on the means of quarterly data.

[Omer Faruk Buyukkurt]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ID int year str6 qdate int ID2 double COMPACT byte QCOMPACT
1 2001 "2001q1"   3 -.33564 1
1 2001 "2001q1"  15   -.304 1
1 2001 "2001q2"  78    -.42 2
1 2001 "2001q2"  51  -.4168 2
1 2001 "2001q2"  54 -.40089 2
1 2001 "2001q2" 987     -.4 2
1 2001 "2001q3"  94    -.33 3
1 2001 "2001q3"  73   -.301 3
1 2001 "2001q3"  82   -.253 3
1 2001 "2001q3"  81   -.207 3
1 2001 "2001q3"   5    -.18 3
1 2001 "2001q3"   9   -.139 3
1 2001 "2001q4" 936    -.59 4
1 2001 "2001q4"   6  -.5501 4
1 2001 "2001q4"  91    -.52 4
1 2002 "2002q1"  12   -.657 5
1 2002 "2002q1"  46   -.524 5
1 2002 "2002q2"  79  -.8412 6
1 2002 "2002q3"   3   -.982 7
1 2002 "2002q3"  68   -.922 7
1 2002 "2002q3"  93  -.8126 7
1 2002 "2002q3"  96   -.735 7
1 2002 "2002q3"  16 -.62456 7
1 2002 "2002q3"  88  -.5921 7
1 2002 "2002q4"  82  -.4156 8
1 2002 "2002q4"  80  -.4056 8
1 2002 "2002q4"  70  -.3995 8
1 2002 "2002q4"  35  -.3524 8
end

Dear All,

First of all I would like to thank to creators and users (especially to the ones that spend their time to help others) of this very helpful forum.

I am trying to obtain annual data via calculating means of quarterly data.

As you can see above I have different number of quarterly observations for each quarter. As first step, I need to calculate means of each quarterly observations. Let's say: for 2001q1, I have 2 observations but 2001q2 I have 4 observations. By using these observations I need to obtain mean of COMPACT for firm 1 2001q1, 2001q2, 2001q3 and so on... I also need to implement same method to firm 2, 3 and so on.

After I calculate means of each quarter, then I need to use means of 4 quarters to get value for each year to be used as a dependent variable in a regression.

Only suitable command I think I could use to do this was -collapse- however, once I execute the command I have only 1 observation for each ID and rest of the observations and variables (such as ID2) disappeared.

Do you think I should stick with -collapse- or is there any other command that I can try?

Kind Regards

Omer Faruk

[Igor Paploski]

Hi Omer,

I assume that the variable that codes for firm is ID, and in the dataset you posted, all observations are ID==1. So the code below does not take firm into account (but it would be easy to add it). Try running:

bysort qdate: egen mean = mean(COMPACT) //creates a var that is the mean of COMPACT for each group of qdate
bysort qdate: gen n = _n //creates the number a observation is inside a qdate
bysort year: egen avg_year = mean(COMPACT) if n==1 // creates average per year of compact using only the first observation in each quarter

You could add firm to the code by doing something like:

bysort qdate firm: egen mean = mean(COMPACT)

on all commands above.

Best;

[Jorrit Gosens]

You coudl something like this:

* means by quarter and ID
collapse (mean) COMPACT (firstnm) QCOMPACT ID2, by(ID qdate)

*create year variable
gen year= substr(qdate,1,4)
destring year, replace
*means by year and ID 
collapse (mean) COMPACT (firstnm) ID2, by(ID year)

Note that you dont have to specify (mean) as this is the default. I included it because you can choose some other variants of collapse, such as the first non-missing value of a variable. This way that variable will be preserved (and only those which you include in your collapse command will be preserved).
Selecting the first non-missing value makes sense for the QCOMPACT variable, for the ID2 variable I would not be so sure which way you would want to preserve it. That is, if the quarterly mean of COMPACT is calculated for e.g., 3 quarterly observations, what value of ID2 should be selected?. Have a look at the collapse helpfile to see what options you can use instead of (mean) or (firstnm).

[Nick Cox]

Thanks for the data example. Does this help? (Not essential for this, but for other purposes your string date variable will be problematic.)

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ID int year str6 qdate int ID2 double COMPACT byte QCOMPACT
1 2001 "2001q1"   3 -.33564 1
1 2001 "2001q1"  15   -.304 1
1 2001 "2001q2"  78    -.42 2
1 2001 "2001q2"  51  -.4168 2
1 2001 "2001q2"  54 -.40089 2
1 2001 "2001q2" 987     -.4 2
1 2001 "2001q3"  94    -.33 3
1 2001 "2001q3"  73   -.301 3
1 2001 "2001q3"  82   -.253 3
1 2001 "2001q3"  81   -.207 3
1 2001 "2001q3"   5    -.18 3
1 2001 "2001q3"   9   -.139 3
1 2001 "2001q4" 936    -.59 4
1 2001 "2001q4"   6  -.5501 4
1 2001 "2001q4"  91    -.52 4
1 2002 "2002q1"  12   -.657 5
1 2002 "2002q1"  46   -.524 5
1 2002 "2002q2"  79  -.8412 6
1 2002 "2002q3"   3   -.982 7
1 2002 "2002q3"  68   -.922 7
1 2002 "2002q3"  93  -.8126 7
1 2002 "2002q3"  96   -.735 7
1 2002 "2002q3"  16 -.62456 7
1 2002 "2002q3"  88  -.5921 7
1 2002 "2002q4"  82  -.4156 8
1 2002 "2002q4"  80  -.4056 8
1 2002 "2002q4"  70  -.3995 8
1 2002 "2002q4"  35  -.3524 8
end

gen nqdate = quarterly(qdate, "YQ")
drop qdate
rename nqdate qdate
format qdate %tq

bysort ID year qdate : egen qmean = mean(COMPACT)
egen tag = tag(ID qdate)
by ID year : egen ymean = mean(cond(tag, qmean, .))

list, sepby(ID year)
     +-----------------------------------------------------------------------------+
     | ID   year   ID2   COMPACT   QCOMPACT    qdate       qmean   tag       ymean |
     |-----------------------------------------------------------------------------|
  1. |  1   2001     3   -.33564          1   2001q1     -.31982     1   -.3794023 |
  2. |  1   2001    15     -.304          1   2001q1     -.31982     0   -.3794023 |
  3. |  1   2001    78      -.42          2   2001q2   -.4094225     1   -.3794023 |
  4. |  1   2001    51    -.4168          2   2001q2   -.4094225     0   -.3794023 |
  5. |  1   2001    54   -.40089          2   2001q2   -.4094225     0   -.3794023 |
  6. |  1   2001   987       -.4          2   2001q2   -.4094225     0   -.3794023 |
  7. |  1   2001    94      -.33          3   2001q3       -.235     1   -.3794023 |
  8. |  1   2001    73     -.301          3   2001q3       -.235     0   -.3794023 |
  9. |  1   2001    82     -.253          3   2001q3       -.235     0   -.3794023 |
 10. |  1   2001    81     -.207          3   2001q3       -.235     0   -.3794023 |
 11. |  1   2001     5      -.18          3   2001q3       -.235     0   -.3794023 |
 12. |  1   2001     9     -.139          3   2001q3       -.235     0   -.3794023 |
 13. |  1   2001   936      -.59          4   2001q4   -.5533667     1   -.3794023 |
 14. |  1   2001     6    -.5501          4   2001q4   -.5533667     0   -.3794023 |
 15. |  1   2001    91      -.52          4   2001q4   -.5533667     0   -.3794023 |
     |-----------------------------------------------------------------------------|
 16. |  1   2002    12     -.657          5   2002q1      -.5905     1   -.6507546 |
 17. |  1   2002    46     -.524          5   2002q1      -.5905     0   -.6507546 |
 18. |  1   2002    79    -.8412          6   2002q2      -.8412     1   -.6507546 |
 19. |  1   2002     3     -.982          7   2002q3   -.7780433     1   -.6507546 |
 20. |  1   2002    68     -.922          7   2002q3   -.7780433     0   -.6507546 |
 21. |  1   2002    93    -.8126          7   2002q3   -.7780433     0   -.6507546 |
 22. |  1   2002    96     -.735          7   2002q3   -.7780433     0   -.6507546 |
 23. |  1   2002    16   -.62456          7   2002q3   -.7780433     0   -.6507546 |
 24. |  1   2002    88    -.5921          7   2002q3   -.7780433     0   -.6507546 |
 25. |  1   2002    82    -.4156          8   2002q4    -.393275     1   -.6507546 |
 26. |  1   2002    80    -.4056          8   2002q4    -.393275     0   -.6507546 |
 27. |  1   2002    70    -.3995          8   2002q4    -.393275     0   -.6507546 |
 28. |  1   2002    35    -.3524          8   2002q4    -.393275     0   -.6507546 |
     +-----------------------------------------------------------------------------+

[Omer Faruk Buyukkurt]

Dear Igor, Jorrit and Nick;

First of all thank you all for your replies. Thanks to the solutions you provided I could manage to solve my issue.

Kind Regards

Omer Faruk