From yearly to quarterly data

[Alexandra Contreras]

Hello to all!
I am new to this Forum, please apologies if I am not using it right.
I have a question, does STATA allow for converting yearly data to quarterly data?
Most of my database is per year but the most important variables in my analysis are the result of auctions which can happen 2-4 times a year, so converting all the dataset to a quarterly timeline sounded good to me.
However I don't consider worthy the time of manually converting all the variables to quarterly because they are a lot.

Another option could be adding more than one event (in one variable) to one year observation.

I will copy a part of my data base :

YEAR 2009		X1 40,130	X2 308,740	X3
2010		40,520	370,260
2011		40,900	448,170
2012		41,733	581,540
2013		42,203	613,200
2014	2014.1	42,670	566,850	229
2014	2014.2	42,670	566,850	34
2014	2014.3	42,670	566,850	67
2014	2014.4	42,670	566,850	234
2015		42,670	566,850	90
2016		43,130	633,960
2017		43,590	532,700	14

Please your help!

Alexandra

[Justin Niakamal]

This thread should tell you what you need. If not, here's an example of how I would go about taking a yearly variable and making it quarterly. As an example, I've compared the quartered unemployment rate (from yearly to quarterly) against the quarterly (seasonally adjusted) unemployment rate.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float quarter double actual
120 5.53167750606359
121 5.55368383532152
122 5.99257380339251
123 6.51753884341743
124 7.11796664922873
125 7.92801679615688
126 8.25485569136677
127 8.67277199478431
128 9.20021634242936
129 9.76269957404261
130 10.1830358427499
131 10.4102265690718
132 10.2773943455484
133 10.0154104224425
134  9.5826549660854
135 9.95134031636706
136 9.92773625845649
137 9.63138769049475
138 9.07682910607205
139 8.34761641490052
140 7.75129340482802
141 7.89493979154778
142 8.14962626311586
143 8.33093225230938
144 8.57404456396557
145 8.44885890148089
146 7.79678877918148
147 7.71998395583676
148 7.45451087356006
149 7.04061798144419
150 6.61707431893162
151 6.35037393095157
152 6.46695367494215
153 6.54562174148723
154 6.68989927208012
155 6.60924489412037
156 6.09804630208552
157 5.71371244886627
158 5.82743908009751
159 5.80563485822728
160  5.6316545874983
161 5.48587561694067
162 5.38917048043787
163 4.97819839097589
164 5.09156128290642
165 5.27502122307945
166 5.82461741515249
167 6.49075279183011
168 6.82047507469195
169  6.9398943545054
170 6.63033184074372
171 6.58606844140168
172 6.71885363257488
173 7.04650720031624
174 7.18072187921208
175 6.92469384555496
176 6.69736580496448
177 6.71537464060312
178  6.3579525796945
179 6.20077360275311
180 5.81731610526503
181  5.4142186104994
182  4.9986633230299
183 5.21578197654047
184 4.96909409801882
185 4.79155918565776
186 4.83431872227202
187 4.52904243433792
188 4.82484607810868
189  4.8777152421154
190 5.21288258118221
191 5.56200941208299
192 6.09049348703451
193 6.91519447863969
194 8.06853215439807
195 9.29101875437506
196 10.7127515613771
197 11.6192127748699
198 11.8210484043695
199 12.2518055958176
200 12.2944696787286
201  12.453210991768
202 12.5037121144232
203 12.7543352430669
204 12.1853108422148
205 12.3096776322852
206 12.2548626587353
207 11.9575092117242
208 11.4614064148757
209 11.0811333397946
210 10.6843475043398
211 10.4211826768583
212  10.229906345801
213 10.0389107712647
214 9.54772493555333
215 9.21486683031624
216  8.7801908492634
217 8.37615558109597
218 8.10181568422713
219 7.77056111482696
220  7.3882813708474
221 7.02454387602501
222 6.25570998143439
223 5.87671255195986
224 5.46389088847185
225 5.24194110284552
226  5.2213800543667
227 5.03340233411886
228 4.93221669344379
229 4.73377452398139
230 4.66991176428847
231 4.42388556255185
end
format %tq!Qq-YY quarter

tempfile temp
save `temp'



* Example generated by -dataex-. To install: ssc install dataex
clear
input int year float value
1990  5.9
1991    8
1992  9.9
1993  9.9
1994  9.3
1995    8
1996  8.1
1997  6.9
1998  6.6
1999  5.9
2000  5.4
2001  5.7
2002  6.7
2003    7
2004  6.5
2005  5.4
2006  4.8
2007  5.1
2008  7.6
2009 11.6
2010 12.5
2011 12.2
2012 10.9
2013  9.8
2014  8.3
2015  6.6
2016  5.3
2017  4.7
end


gen quarter =  .

levelsof(year) , local(years)
foreach v in `years' {
    replace  quarter = tq(`v'-2)  if year == `v' &  quarter == .
}
format quarter %tq!Qq-YY
qui ssc install insob
insob 3 _N, after
replace quarter = quarter[_n-1] + 1 if quarter == .


tsset quarter, quarterly
tsfill
mipolate value quarter, gen(unrate) pchip

merge 1:1 quarter using `temp'
keep if _merge == 3
drop _merge

twoway line unrate actual quarter, scheme(s1color) legend(region(lcolor(white))) xtitle("")

[Alexandra Contreras]

Thanks Justin, I can certainly try what you suggest. The link you provided me is very useful.