converting data from quarterly to yearly

[Khati Zolfaghari]

hello and good day, I am so bigger in stata. I have 2-panel data. both included 143 countries and the time is started from 1952q1 to 2020q3. I want to merge these two-panel data with annual frequency. I saw in terms of year to the quart. but I do not know what is the command to merge and also convert from quarterly to yearly? I hope I receive your guidance so soon. than you so much.

[Andrew Musau]

What assumption lies behind the reduction from quarterly data to annual data? Do you average the quarterly values or use the value at the end of the 4th quarter to represent the annual value? Or maybe something else? How you answer these questions will suggest what code is needed. Also, beware of averaging logged values as you end up with a geometric mean and not an arithmetic mean. For the latter, average first before taking logs. Here is an example of averaging and using end of 4th quarter values.

webuse xtcoint, clear
keep if inrange(time, tq(1975q1), tq(1980q4)) & id<=4
gen year=yofd(dofq(time))
order id year
preserve
*AVERAGE ACROSS QUARTERS
collapse rd* productivity, by(id year)
l, sepby(id)
restore, preserve
*USE END OF 4TH QUARTER VALUE
bys id year (time): keep if _n==_N
drop time
l, sepby(id)
restore

Once you have the annual observations, you just do a simple merge

merge 1:1 id year using dataest2

Res.

*ORIGINAL QUARTERLY DATA

. l, sepby(id)

     +-----------------------------------------------+
     | id     time   rddome~c   rdfore~n   product~y |
     |-----------------------------------------------|
  1. |  1   1975q1   9.392248    2.51255    1.328096 |
  2. |  1   1975q2   10.72677   1.938901    1.391271 |
  3. |  1   1975q3   10.65223   2.415033    .3387583 |
  4. |  1   1975q4   11.31236   3.696103    .3621179 |
  5. |  1   1976q1   12.70129   4.959726   -.4885854 |
  6. |  1   1976q2   15.15132   4.951461   -.3087882 |
  7. |  1   1976q3   16.00235   6.699615   -.0578043 |
  8. |  1   1976q4   17.69668   6.790137   -.4564461 |
  9. |  1   1977q1   17.40613    7.37001   -1.027598 |
 10. |  1   1977q2   17.40797   9.035278   -1.906846 |
 11. |  1   1977q3   19.23268   7.760499   -.0188559 |
 12. |  1   1977q4   20.21507    9.53343   -1.438864 |
 13. |  1   1978q1   22.01311   9.883553   -.3531784 |
 14. |  1   1978q2   21.72641   9.667647   -1.595599 |
 15. |  1   1978q3    22.0591   9.281356   -3.092498 |
 16. |  1   1978q4   23.57998   10.36576   -3.530313 |
 17. |  1   1979q1   23.44657   11.08079   -3.964914 |
 18. |  1   1979q2   24.80194   12.56996   -3.635825 |
 19. |  1   1979q3   25.09452   13.18057   -3.646679 |
 20. |  1   1979q4   26.97007   14.73795   -3.505055 |
 21. |  1   1980q1   28.10782   16.53678   -3.657553 |
 22. |  1   1980q2   28.74318   15.91889    -3.97226 |
 23. |  1   1980q3   29.36103   16.57826   -2.606842 |
 24. |  1   1980q4   30.21996   17.70421   -2.489831 |
     |-----------------------------------------------|
 25. |  2   1975q1   4.553992   8.507536   -1.319993 |
 26. |  2   1975q2   5.522433   10.02516   -2.350792 |
 27. |  2   1975q3   6.297344   9.033638   -2.534651 |
 28. |  2   1975q4   7.680171    8.98082   -1.484727 |
 29. |  2   1976q1   8.552213   7.780101   -2.935225 |
 30. |  2   1976q2   10.00575   8.443789   -3.091882 |
 31. |  2   1976q3   11.32789   8.369481   -2.108766 |
 32. |  2   1976q4   12.11687   8.297181   -1.811755 |
 33. |  2   1977q1   12.39447   7.261135   -1.436413 |
 34. |  2   1977q2   13.42066     7.0281   -1.705248 |
 35. |  2   1977q3   13.80598   9.522221   -2.053638 |
 36. |  2   1977q4   14.97161   10.96076   -.5717572 |
 37. |  2   1978q1   14.85047   12.52677   -.6789464 |
 38. |  2   1978q2   15.92457   12.67437    .3952164 |
 39. |  2   1978q3   15.63681   15.97696   -1.303211 |
 40. |  2   1978q4   15.81548   17.47362   -.1747667 |
 41. |  2   1979q1   15.62249   19.35675   -1.246554 |
 42. |  2   1979q2   17.56646   19.78934    1.917058 |
 43. |  2   1979q3   18.63509   19.74702   -.9031868 |
 44. |  2   1979q4   18.62209   20.37446   -.4838904 |
 45. |  2   1980q1   18.84136    20.4088   -.3879068 |
 46. |  2   1980q2    20.6883   21.27801    1.372118 |
 47. |  2   1980q3   24.16245   21.25222    .6271018 |
 48. |  2   1980q4   25.74549   19.98972    2.126752 |
     |-----------------------------------------------|
 49. |  3   1975q1   7.753002   3.682216    1.201494 |
 50. |  3   1975q2   8.312046   5.306005    1.747076 |
 51. |  3   1975q3   9.763663   5.735284     .151503 |
 52. |  3   1975q4   10.48278   5.512055   -.2716026 |
 53. |  3   1976q1    10.4436   7.020366    .5784292 |
 54. |  3   1976q2   12.54817   7.788718    1.164033 |
 55. |  3   1976q3   13.74621   8.854654     2.27354 |
 56. |  3   1976q4   14.19995   11.48065     1.36379 |
 57. |  3   1977q1   13.93576   12.86956    1.274312 |
 58. |  3   1977q2   14.76748   13.65364    1.408756 |
 59. |  3   1977q3   15.71558   13.66497    2.045512 |
 60. |  3   1977q4   15.96161   14.23937     2.55466 |
 61. |  3   1978q1   16.46717   15.82014    1.666179 |
 62. |  3   1978q2   17.23502   17.35035     3.14833 |
 63. |  3   1978q3   17.41443   17.95404     5.45109 |
 64. |  3   1978q4   19.41694   18.08117    5.685671 |
 65. |  3   1979q1   20.48033   18.01187    5.780663 |
 66. |  3   1979q2   21.68079   18.36808    5.461949 |
 67. |  3   1979q3   23.44945   19.01603    6.121177 |
 68. |  3   1979q4   23.17327   20.15713    6.382417 |
 69. |  3   1980q1   21.90948   20.29529    4.732858 |
 70. |  3   1980q2   23.13451    20.0665    6.820376 |
 71. |  3   1980q3   23.74767   21.91979    6.171983 |
 72. |  3   1980q4   25.49979    21.7376      6.3524 |
     |-----------------------------------------------|
 73. |  4   1975q1   11.31471   5.055861    5.337585 |
 74. |  4   1975q2   12.22078   6.035449    3.502034 |
 75. |  4   1975q3   11.99061   8.662561    5.387071 |
 76. |  4   1975q4   14.86112   9.465612    4.446669 |
 77. |  4   1976q1   15.56038   10.18473    4.428362 |
 78. |  4   1976q2   17.22488   10.66438    5.987141 |
 79. |  4   1976q3   17.74116    10.7756    5.584081 |
 80. |  4   1976q4   17.40396   11.48509    3.957751 |
 81. |  4   1977q1     19.114    11.7584    5.188044 |
 82. |  4   1977q2   20.88386   11.85579    2.973782 |
 83. |  4   1977q3   22.32788   12.19108    4.693525 |
 84. |  4   1977q4   23.51385   14.55145     5.16931 |
 85. |  4   1978q1   24.58502   13.24554    2.882385 |
 86. |  4   1978q2   27.57227   14.26571    4.451523 |
 87. |  4   1978q3   28.59177   15.30736    5.350502 |
 88. |  4   1978q4   30.08332   15.35175    2.897232 |
 89. |  4   1979q1   30.31845   16.64528    4.221992 |
 90. |  4   1979q2   30.89393   16.60923    5.877312 |
 91. |  4   1979q3   32.71891   17.17355    6.012998 |
 92. |  4   1979q4   33.17815   18.58682    7.161945 |
 93. |  4   1980q1   34.14151   18.47002    6.264393 |
 94. |  4   1980q2   36.36524   19.40545    5.634045 |
 95. |  4   1980q3   38.21825   19.19447    5.409887 |
 96. |  4   1980q4   41.06352   18.29703     5.09843 |
     +-----------------------------------------------+

. 


*AVERAGE ACROSS QUARTERS

. l, sepby(id)

     +---------------------------------------------+
     | id   year   rddome~c   rdfore~n   product~y |
     |---------------------------------------------|
  1. |  1   1975    10.5209   2.640647    .8550606 |
  2. |  1   1976   15.38791   5.850235    -.327906 |
  3. |  1   1977   18.56546   8.424805   -1.098041 |
  4. |  1   1978   22.34465   9.799578   -2.142897 |
  5. |  1   1979   25.07827   12.89232   -3.688118 |
  6. |  1   1980     29.108   16.68454   -3.181622 |
     |---------------------------------------------|
  7. |  2   1975   6.013485   9.136787   -1.922541 |
  8. |  2   1976   10.50068   8.222638   -2.486907 |
  9. |  2   1977   13.64818   8.693055   -1.441764 |
 10. |  2   1978   15.55683   14.66293    -.440427 |
 11. |  2   1979   17.61153   19.81689   -.1791434 |
 12. |  2   1980    22.3594   20.73219    .9345162 |
     |---------------------------------------------|
 13. |  3   1975   9.077873    5.05889    .7071176 |
 14. |  3   1976   12.73448   8.786097    1.344948 |
 15. |  3   1977   15.09511   13.60689     1.82081 |
 16. |  3   1978   17.63339   17.30142    3.987818 |
 17. |  3   1979   22.19596   18.88828    5.936552 |
 18. |  3   1980   23.57286    21.0048    6.019404 |
     |---------------------------------------------|
 19. |  4   1975    12.5968   7.304871     4.66834 |
 20. |  4   1976   16.98259   10.77745    4.989334 |
 21. |  4   1977    21.4599   12.58918    4.506165 |
 22. |  4   1978    27.7081   14.54259    3.895411 |
 23. |  4   1979   31.77736   17.25372    5.818562 |
 24. |  4   1980   37.44713   18.84174    5.601689 |
     +---------------------------------------------+



*USE END OF 4TH QUARTER VALUE
. l, sepby(id)

     +---------------------------------------------+
     | id   year   rddome~c   rdfore~n   product~y |
     |---------------------------------------------|
  1. |  1   1975   11.31236   3.696103    .3621179 |
  2. |  1   1976   17.69668   6.790137   -.4564461 |
  3. |  1   1977   20.21507    9.53343   -1.438864 |
  4. |  1   1978   23.57998   10.36576   -3.530313 |
  5. |  1   1979   26.97007   14.73795   -3.505055 |
  6. |  1   1980   30.21996   17.70421   -2.489831 |
     |---------------------------------------------|
  7. |  2   1975   7.680171    8.98082   -1.484727 |
  8. |  2   1976   12.11687   8.297181   -1.811755 |
  9. |  2   1977   14.97161   10.96076   -.5717572 |
 10. |  2   1978   15.81548   17.47362   -.1747667 |
 11. |  2   1979   18.62209   20.37446   -.4838904 |
 12. |  2   1980   25.74549   19.98972    2.126752 |
     |---------------------------------------------|
 13. |  3   1975   10.48278   5.512055   -.2716026 |
 14. |  3   1976   14.19995   11.48065     1.36379 |
 15. |  3   1977   15.96161   14.23937     2.55466 |
 16. |  3   1978   19.41694   18.08117    5.685671 |
 17. |  3   1979   23.17327   20.15713    6.382417 |
 18. |  3   1980   25.49979    21.7376      6.3524 |
     |---------------------------------------------|
 19. |  4   1975   14.86112   9.465612    4.446669 |
 20. |  4   1976   17.40396   11.48509    3.957751 |
 21. |  4   1977   23.51385   14.55145     5.16931 |
 22. |  4   1978   30.08332   15.35175    2.897232 |
 23. |  4   1979   33.17815   18.58682    7.161945 |
 24. |  4   1980   41.06352   18.29703     5.09843 |
     +---------------------------------------------+

[Khati Zolfaghari]

@ Andrew Musau thank you so much for your reply.

[Fadi Ansar]

Dear Andrew Musau ,

I have a dataset simiar to this and when I was using the same command I am getting error in commands .I want to convert my qarterly data starting from 2005q1 to 2022 q4 in to yearly fromat as rest of my variables are in yearly format .

input byte id str6 quarter float(grossadvances grossnpas)
1 "2005q1"  12966.59    409.28
1 "2005q1"   20301.3    553.28
1 "2005q1"  15697.17    311.11
1 "2005q1" 2544.7046  239.8698
1 "2005q1" 2983.0964  159.1819
1 "2005q1" 1374.5968    57.454
1 "2005q1"   2066.79    121.64
1 "2005q1"   2370.55    169.76
1 "2005q1"   2175.38    310.75
1 "2005q1"   9302.81    677.79
1 "2005q1"  105.7294   19.0696
1 "2005q1"   86826.2 2770.4285
1 "2005q1"  11791.26      85.4
1 "2005q1"   9076.01    320.53
1 "2005q1"   9081.57    194.26
1 "2005q1"  11671.45    317.25
1 "2005q1"  6621.066  501.7795
1 "2005q1"  4741.012  241.9128
1 "2005q1" 2379.1282  187.4447
1 "2005q1"      1419     90.64
1 "2005q1"  368.5182    9.4689
1 "2005q1"  446.2701   46.0033
1 "2005q1"  850.9689   76.1292
1 "2005q1"  280.6157   67.5167
1 "2005q1"   5517.41  366.0642
1 "2005q1"   2852.52    321.13
1 "2005q1"   1475.54     125.5
1 "2005q1" 4198.3047  450.1967
1 "2005q1" 22151.523 1284.2653
1 "2005q1"  17905.84    440.93
1 "2005q1"  37632.56   3161.49
1 "2005q1"  44389.01   2877.65
1 "2005q1"  13742.84    961.94
1 "2005q1"  60385.23    2361.5
1 "2005q1"  29084.54   2621.31
1 "2005q1" 18967.992  647.2509
1 "2005q1" 11865.127 1147.5446
1 "2005q1"  17870.78    741.89
1 "2005q1" 24531.977   1362.06
1 "2005q1"  27496.16   2512.82
1 "2005q1"   6973.66   1197.41
1 "2005q1"  62726.49   3741.34
1 "2005q1"  12248.61    399.51
1 "2005q1" 16002.063  553.3246
1 "2005q1"  738771.2  46589.72
1 "2005q1"  9253.446  303.4795
1 "2005q1"  9111.719   415.375
1 "2005q1"  15822.78     653.1
1 "2005q1"    6806.7    183.84
1 "2005q1"  15191.89    652.13
1 "2005q1"  24377.23   1429.66
1 "2005q1"  26646.43   1379.41
1 "2005q1"  41102.86   2058.15
1 "2005q1" 11838.966    726.37
1 "2005q1" 14682.808  431.6375
2 "2005q2"  14423.28    414.95
2 "2005q2"   19849.6    558.34
2 "2005q2"  15803.54    393.24
2 "2005q2" 2389.2275  239.9203
2 "2005q2"  2697.602  155.9722
2 "2005q2" 1393.2086     57.36
2 "2005q2"   2095.88    125.06
2 "2005q2"   2467.86    172.23
2 "2005q2"   2045.22    316.99
2 "2005q2"    9227.1    665.02
2 "2005q2"  103.5858   19.1154
2 "2005q2"  92645.58  2936.779
2 "2005q2"  48275.13  1042.373
2 "2005q2"   8944.85    344.88
2 "2005q2"   9019.64  182.9831
2 "2005q2"  11394.41    296.82
2 "2005q2"  6551.736  511.6447
2 "2005q2" 4733.2847   243.805
2 "2005q2" 2137.9585  190.1359
2 "2005q2"   1341.32      86.1
2 "2005q2"  406.5245   10.2894
2 "2005q2"  418.0113   45.1179
2 "2005q2"  536.3358   80.3232
2 "2005q2"  290.8478   67.3298
2 "2005q2"  5424.112  349.1331
2 "2005q2"   2784.42     339.4
2 "2005q2"   1486.48    127.44
2 "2005q2"  3829.995  451.3892
2 "2005q2"  23267.51 1249.3356
2 "2005q2"  18098.58    460.51
2 "2005q2"     38486    3223.5
2 "2005q2"  44683.94   2815.08
2 "2005q2" 13520.083   1014.76
2 "2005q2"  59937.46   2331.01
2 "2005q2"  28446.41   2729.65
2 "2005q2" 19258.252  695.1162
2 "2005q2"  11847.75 1149.2977
2 "2005q2"  48275.13  1042.373
2 "2005q2"  18994.96    728.54
2 "2005q2"   25536.3  1358.435
2 "2005q2"  27513.93   2481.41
2 "2005q2"   6884.24   1084.52
2 "2005q2"  58492.54   3530.28
2 "2005q2"  12492.51    418.71
2 "2005q2" 16593.873  551.1576

Can you please help me with this .

[Andrew Musau]

I have a dataset simiar to this and when I was using the same command I am getting error in commands .I want to convert my qarterly data starting from 2005q1 to 2022 q4 in to yearly fromat as rest of my variables are in yearly format .

You need to specify how the data should be aggregated. Do you want to sum four quarterly values to obtain the annual value, or do you want to use the end-of-4th-quarter values to represent the annual value, as in #1?

[Nick Cox]

And why do you have same values even for the same id and quarterly date?

[Dr. Iqra Yaseen]

Andrew Musau I need to change quarter data into year data based on average of the four quarters, but without collapsing the data, rather creating a new variable. earlier i used

gen year = 1960 + floor(m/12)

to convert month data to yearly data and it worked well. but when i use

gen year = 1960 + floor(q/4)

i get error of "q not found". i also use capital q (Q) to see if it matters, but got the same error. i also used all other codes given under different threads but nothing worked well. the dataset example is

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 year str3 iso3_d double TPU
"2008q1" "AUS"  1
"2008q2" "AUS"  3
"2008q3" "AUS"  2
"2008q4" "AUS"  2
"2009q1" "AUS"  1
"2009q2" "AUS"  0
"2009q3" "AUS"  1
"2009q4" "AUS"  1
"2010q1" "AUS"  1
"2010q2" "AUS"  1
"2010q3" "AUS"  3
"2010q4" "AUS"  2
"2011q1" "AUS"  2
"2011q2" "AUS"  1
"2011q3" "AUS"  6
"2011q4" "AUS"  1
"2012q1" "AUS"  1
"2012q2" "AUS"  3
"2012q3" "AUS"  1
"2012q4" "AUS"  1
"2013q1" "AUS"  1
"2013q2" "AUS"  1
"2013q3" "AUS"  0
"2013q4" "AUS"  0
"2014q1" "AUS"  0
"2014q2" "AUS"  4
"2014q3" "AUS"  1
"2014q4" "AUS"  1
"2015q1" "AUS"  2
"2015q2" "AUS"  2
"2015q3" "AUS"  4
"2015q4" "AUS"  1
"2016q1" "AUS"  5
"2016q2" "AUS"  1
"2016q3" "AUS"  5
"2016q4" "AUS"  3
"2017q1" "AUS"  4
"2017q2" "AUS"  4
"2017q3" "AUS"  4
"2017q4" "AUS"  2
"2018q1" "AUS"  2
"2018q2" "AUS"  1
"2018q3" "AUS"  3
"2018q4" "AUS"  3
"2019q1" "AUS"  2
"2019q2" "AUS"  6
"2019q3" "AUS"  4
"2019q4" "AUS"  0
"2020q1" "AUS"  5
"2020q2" "AUS"  3
"2020q3" "AUS"  7
"2020q4" "AUS"  5
"2021q1" "AUS"  3
"2021q2" "AUS"  5
"2021q3" "AUS"  4
"2021q4" "AUS"  2
"2022q1" "AUS"  5
"2022q2" "AUS"  5
"2022q3" "AUS"  1
"2022q4" "AUS"  3
"2023q1" "AUS"  2
"2023q2" "AUS"  1
"2008q1" "IDN"  4
"2008q2" "IDN"  4
"2008q3" "IDN"  2
"2008q4" "IDN"  0
"2009q1" "IDN"  1
"2009q2" "IDN"  0
"2009q3" "IDN"  0
"2009q4" "IDN"  2
"2010q1" "IDN"  1
"2010q2" "IDN"  1
"2010q3" "IDN"  1
"2010q4" "IDN"  0
"2011q1" "IDN"  0
"2011q2" "IDN"  0
"2011q3" "IDN"  3
"2011q4" "IDN"  2
"2012q1" "IDN"  1
"2012q2" "IDN" 12
"2012q3" "IDN"  3
"2012q4" "IDN"  1
"2013q1" "IDN"  2
"2013q2" "IDN"  1
"2013q3" "IDN"  2
"2013q4" "IDN"  0
"2014q1" "IDN"  3
"2014q2" "IDN"  2
"2014q3" "IDN"  3
"2014q4" "IDN"  1
"2015q1" "IDN"  3
"2015q2" "IDN"  0
"2015q3" "IDN"  0
"2015q4" "IDN"  4
"2016q1" "IDN"  0
"2016q2" "IDN"  1
"2016q3" "IDN"  3
"2016q4" "IDN"  3
"2017q1" "IDN"  0
"2017q2" "IDN"  0
end

[Nick Cox]

You copied code from somewhere (cross-reference please?) in which presumably m was the name of a numeric monthly date variable. Manifestly, your quarterly date variable is string -- and you have no variables in your example whose names begin with q or Q. That makes two reasons why the code didn't work.

In your case,

gen wanted = real(substr(year, 1, 4))

should get you where you want to be, noting that you should choose a better name than wanted and that year would be a good name, except that (poor practice) it is already the name of your quarterly date variable.

[Dr. Iqra Yaseen]

Nick Cox yes, i copied it from your comment on time series - Stata: Convert date, quarter to year - Stack Overflow.

#8 code worked well

thanks a lot for your response.