Covert weekly to quarterly data

[Gabriel Temesgen]

Hi all, I have trouble converting my weekly data into quarterly data.
could you please drop me the code? Thanks

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date double RODR
"1999/06/W1" 14.01
"1999/06/W2"    14
"1999/06/W3" 13.77
"1999/06/W4"  13.3
"1999/07/W1"  13.3
"1999/07/W2" 12.85
"1999/07/W3" 12.86
"1999/07/W4" 12.59
"1999/07/W5" 12.09
"1999/08/W1" 12.08
"1999/08/W2" 12.08
"1999/08/W3" 12.05
"1999/08/W4" 12.07
"1999/09/W1" 11.86
"1999/09/W2" 11.62
"1999/09/W3" 11.59
"1999/09/W4" 11.34
"2001/05/W4" 10.44
"2001/06/W1" 10.62
"2001/06/W2" 10.76
"2001/06/W3"  9.45
"2001/06/W4"  9.46
"2001/06/W5"  9.57
"2001/07/W1"  9.58
"2001/07/W2"  9.23
"2001/07/W3"  8.99
"2001/07/W4"  9.04
"2001/08/W1"  9.24
"2001/08/W2"  9.23
"2001/08/W3"  9.23
"2001/08/W4"  9.21
"2001/08/W5"  9.38
"2001/09/W1"  9.21
"2001/09/W2"  9.15
"2001/09/W3"  8.52
"2001/09/W4"  8.54
"2001/10/W1"  8.55
"2001/10/W2"  8.54
"2001/10/W3"  8.65
"2001/10/W4"  8.62
"2001/11/W1"  8.72
"2001/11/W2"  8.81
"2001/11/W3"  8.84
"2001/11/W4"  8.84
"2001/11/W5"  8.84
"2001/12/W1"  8.94
"2001/12/W2"  8.94
"2001/12/W3"  8.97
"2001/12/W4"  8.97
"2002/01/W1"  8.98
"2002/01/W2"  8.96
"2002/01/W3"  9.34
"2002/01/W4"  9.26
"2002/02/W1"  9.26
"2002/02/W2"  9.31
"2002/02/W3"  9.32
"2002/02/W4"  9.32
"2002/03/W1"  9.65
"2002/03/W2"  9.59
"2002/03/W3" 10.07
"2002/03/W4" 10.09
"2002/03/W5" 10.09
"2002/04/W1" 10.25
"2002/04/W2" 10.32
"2002/04/W3" 10.32
"2002/04/W4" 10.55
"2002/05/W1" 10.55
"2002/05/W2"  10.7
"2002/05/W3" 10.71
"2002/05/W4" 10.81
"2002/05/W5" 10.93
"2002/06/W1" 10.95
"2002/06/W2" 11.23
"2002/06/W3" 11.23
"2002/06/W4" 11.42
"2002/07/W1" 11.45
"2002/07/W2" 11.45
"2002/07/W3" 11.45
"2002/07/W4" 11.49
"2002/08/W1"  11.5
"2002/08/W2"  11.5
"2002/08/W3"  11.5
"2002/08/W4"  11.5
"2002/08/W5" 11.65
"2002/09/W1" 11.75
"2002/09/W2" 12.24
"2002/09/W3" 12.34
"2002/09/W4" 12.44
"2002/10/W1" 12.59
"2002/10/W2" 12.59
"2002/10/W3" 12.59
"2002/10/W4" 12.59
"2002/11/W1" 12.73
"2002/11/W2" 12.75
"2002/11/W3" 12.75
"2002/11/W4" 12.75
"2002/11/W5" 12.75
"2002/12/W1" 12.75
"2002/12/W2" 12.75
"2002/12/W3" 12.76
end

[Nick Cox]

On this information your quarterly dates might be calculated like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date double RODR
"1999/06/W1" 14.01
"1999/06/W2"    14
"1999/06/W3" 13.77
"1999/06/W4"  13.3
"1999/07/W1"  13.3
"1999/07/W2" 12.85
"1999/07/W3" 12.86
"1999/07/W4" 12.59
"1999/07/W5" 12.09
"1999/08/W1" 12.08
"1999/08/W2" 12.08
"1999/08/W3" 12.05
"1999/08/W4" 12.07
"1999/09/W1" 11.86
"1999/09/W2" 11.62
"1999/09/W3" 11.59
"1999/09/W4" 11.34
"2001/05/W4" 10.44
"2001/06/W1" 10.62
"2001/06/W2" 10.76
"2001/06/W3"  9.45
"2001/06/W4"  9.46
"2001/06/W5"  9.57
"2001/07/W1"  9.58
"2001/07/W2"  9.23
"2001/07/W3"  8.99
"2001/07/W4"  9.04
"2001/08/W1"  9.24
"2001/08/W2"  9.23
"2001/08/W3"  9.23
"2001/08/W4"  9.21
"2001/08/W5"  9.38
"2001/09/W1"  9.21
"2001/09/W2"  9.15
"2001/09/W3"  8.52
"2001/09/W4"  8.54
"2001/10/W1"  8.55
"2001/10/W2"  8.54
"2001/10/W3"  8.65
"2001/10/W4"  8.62
"2001/11/W1"  8.72
"2001/11/W2"  8.81
"2001/11/W3"  8.84
"2001/11/W4"  8.84
"2001/11/W5"  8.84
"2001/12/W1"  8.94
"2001/12/W2"  8.94
"2001/12/W3"  8.97
"2001/12/W4"  8.97
"2002/01/W1"  8.98
"2002/01/W2"  8.96
"2002/01/W3"  9.34
"2002/01/W4"  9.26
"2002/02/W1"  9.26
"2002/02/W2"  9.31
"2002/02/W3"  9.32
"2002/02/W4"  9.32
"2002/03/W1"  9.65
"2002/03/W2"  9.59
"2002/03/W3" 10.07
"2002/03/W4" 10.09
"2002/03/W5" 10.09
"2002/04/W1" 10.25
"2002/04/W2" 10.32
"2002/04/W3" 10.32
"2002/04/W4" 10.55
"2002/05/W1" 10.55
"2002/05/W2"  10.7
"2002/05/W3" 10.71
"2002/05/W4" 10.81
"2002/05/W5" 10.93
"2002/06/W1" 10.95
"2002/06/W2" 11.23
"2002/06/W3" 11.23
"2002/06/W4" 11.42
"2002/07/W1" 11.45
"2002/07/W2" 11.45
"2002/07/W3" 11.45
"2002/07/W4" 11.49
"2002/08/W1"  11.5
"2002/08/W2"  11.5
"2002/08/W3"  11.5
"2002/08/W4"  11.5
"2002/08/W5" 11.65
"2002/09/W1" 11.75
"2002/09/W2" 12.24
"2002/09/W3" 12.34
"2002/09/W4" 12.44
"2002/10/W1" 12.59
"2002/10/W2" 12.59
"2002/10/W3" 12.59
"2002/10/W4" 12.59
"2002/11/W1" 12.73
"2002/11/W2" 12.75
"2002/11/W3" 12.75
"2002/11/W4" 12.75
"2002/11/W5" 12.75
"2002/12/W1" 12.75
"2002/12/W2" 12.75
"2002/12/W3" 12.76
end

gen qdate = qofd(dofm(monthly(substr(date, 1,7), "YM")))

format qdate %tq

list, sepby(qdate)


     +-----------------------------+
     |       date    RODR    qdate |
     |-----------------------------|
  1. | 1999/06/W1   14.01   1999q2 |
  2. | 1999/06/W2      14   1999q2 |
  3. | 1999/06/W3   13.77   1999q2 |
  4. | 1999/06/W4    13.3   1999q2 |
     |-----------------------------|
  5. | 1999/07/W1    13.3   1999q3 |
  6. | 1999/07/W2   12.85   1999q3 |
  7. | 1999/07/W3   12.86   1999q3 |
  8. | 1999/07/W4   12.59   1999q3 |
  9. | 1999/07/W5   12.09   1999q3 |
 10. | 1999/08/W1   12.08   1999q3 |
 11. | 1999/08/W2   12.08   1999q3 |
 12. | 1999/08/W3   12.05   1999q3 |
 13. | 1999/08/W4   12.07   1999q3 |
 14. | 1999/09/W1   11.86   1999q3 |
 15. | 1999/09/W2   11.62   1999q3 |
 16. | 1999/09/W3   11.59   1999q3 |
 17. | 1999/09/W4   11.34   1999q3 |
     |-----------------------------|
 18. | 2001/05/W4   10.44   2001q2 |
 19. | 2001/06/W1   10.62   2001q2 |
 20. | 2001/06/W2   10.76   2001q2 |
 21. | 2001/06/W3    9.45   2001q2 |
 22. | 2001/06/W4    9.46   2001q2 |
 23. | 2001/06/W5    9.57   2001q2 |
     |-----------------------------|
 24. | 2001/07/W1    9.58   2001q3 |
 25. | 2001/07/W2    9.23   2001q3 |
 26. | 2001/07/W3    8.99   2001q3 |
 27. | 2001/07/W4    9.04   2001q3 |
 28. | 2001/08/W1    9.24   2001q3 |
 29. | 2001/08/W2    9.23   2001q3 |
 30. | 2001/08/W3    9.23   2001q3 |
 31. | 2001/08/W4    9.21   2001q3 |
 32. | 2001/08/W5    9.38   2001q3 |
 33. | 2001/09/W1    9.21   2001q3 |
 34. | 2001/09/W2    9.15   2001q3 |
 35. | 2001/09/W3    8.52   2001q3 |
 36. | 2001/09/W4    8.54   2001q3 |
     |-----------------------------|
 37. | 2001/10/W1    8.55   2001q4 |
 38. | 2001/10/W2    8.54   2001q4 |
 39. | 2001/10/W3    8.65   2001q4 |
 40. | 2001/10/W4    8.62   2001q4 |
 41. | 2001/11/W1    8.72   2001q4 |
 42. | 2001/11/W2    8.81   2001q4 |
 43. | 2001/11/W3    8.84   2001q4 |
 44. | 2001/11/W4    8.84   2001q4 |
 45. | 2001/11/W5    8.84   2001q4 |
 46. | 2001/12/W1    8.94   2001q4 |
 47. | 2001/12/W2    8.94   2001q4 |
 48. | 2001/12/W3    8.97   2001q4 |
 49. | 2001/12/W4    8.97   2001q4 |
     |-----------------------------|
 50. | 2002/01/W1    8.98   2002q1 |
 51. | 2002/01/W2    8.96   2002q1 |
 52. | 2002/01/W3    9.34   2002q1 |
 53. | 2002/01/W4    9.26   2002q1 |
 54. | 2002/02/W1    9.26   2002q1 |
 55. | 2002/02/W2    9.31   2002q1 |
 56. | 2002/02/W3    9.32   2002q1 |
 57. | 2002/02/W4    9.32   2002q1 |
 58. | 2002/03/W1    9.65   2002q1 |
 59. | 2002/03/W2    9.59   2002q1 |
 60. | 2002/03/W3   10.07   2002q1 |
 61. | 2002/03/W4   10.09   2002q1 |
 62. | 2002/03/W5   10.09   2002q1 |
     |-----------------------------|
 63. | 2002/04/W1   10.25   2002q2 |
 64. | 2002/04/W2   10.32   2002q2 |
 65. | 2002/04/W3   10.32   2002q2 |
 66. | 2002/04/W4   10.55   2002q2 |
 67. | 2002/05/W1   10.55   2002q2 |
 68. | 2002/05/W2    10.7   2002q2 |
 69. | 2002/05/W3   10.71   2002q2 |
 70. | 2002/05/W4   10.81   2002q2 |
 71. | 2002/05/W5   10.93   2002q2 |
 72. | 2002/06/W1   10.95   2002q2 |
 73. | 2002/06/W2   11.23   2002q2 |
 74. | 2002/06/W3   11.23   2002q2 |
 75. | 2002/06/W4   11.42   2002q2 |
     |-----------------------------|
 76. | 2002/07/W1   11.45   2002q3 |
 77. | 2002/07/W2   11.45   2002q3 |
 78. | 2002/07/W3   11.45   2002q3 |
 79. | 2002/07/W4   11.49   2002q3 |
 80. | 2002/08/W1    11.5   2002q3 |
 81. | 2002/08/W2    11.5   2002q3 |
 82. | 2002/08/W3    11.5   2002q3 |
 83. | 2002/08/W4    11.5   2002q3 |
 84. | 2002/08/W5   11.65   2002q3 |
 85. | 2002/09/W1   11.75   2002q3 |
 86. | 2002/09/W2   12.24   2002q3 |
 87. | 2002/09/W3   12.34   2002q3 |
 88. | 2002/09/W4   12.44   2002q3 |
     |-----------------------------|
 89. | 2002/10/W1   12.59   2002q4 |
 90. | 2002/10/W2   12.59   2002q4 |
 91. | 2002/10/W3   12.59   2002q4 |
 92. | 2002/10/W4   12.59   2002q4 |
 93. | 2002/11/W1   12.73   2002q4 |
 94. | 2002/11/W2   12.75   2002q4 |
 95. | 2002/11/W3   12.75   2002q4 |
 96. | 2002/11/W4   12.75   2002q4 |
 97. | 2002/11/W5   12.75   2002q4 |
 98. | 2002/12/W1   12.75   2002q4 |
 99. | 2002/12/W2   12.75   2002q4 |
100. | 2002/12/W3   12.76   2002q4 |
     +-----------------------------+

Another way to do it is

gen qdate2 = yq(real(substr(date, 1, 4)), ceil(real(substr(date, 6, 2))/3))

After you have quarterly dates you probably need some kind of

collapse

.

[Gabriel Temesgen]

Thanks, Nick Cox
It work well.
One question here:
collapse RODR , by( qdate2 qdate )
Is this code the correct way to collapse?
Best

[Nick Cox]

Well, qdate and qdate2 are the same so you don't need both. Also, that collapse produces means, and so is correct if you want means. Perhaps geometric means would be more appropriate, but I am guessing.