Transform quarterly data to monthly data using interpolate

[Thomas Lee]

Hi all,
I have some trouble to transform quarterly data to monthly data. I try to use denton and linear inter but i cannot get the right syntax. My data set is in the link. Thank you so much for your help.
https://fred.stlouisfed.org/series/AUSPROINDQISMEI

clear
* Example generated by -dataex-. To install: ssc install dataex
clear
input int date double ipau
5295 44.3183305704798
5295 44.3183305704798
5295 44.3183305704798
5387 43.7289969099266
5387 43.7289969099266
5387 43.7289969099266
5479 40.6233538247024
5479 40.6233538247024
5479 40.6233538247024
5569 40.6601026983177
5569 40.6601026983177
5569 40.6601026983177
5660 41.3978173281077
5660 41.3978173281077
5660 41.3978173281077
5752 42.7380614208846
5752 42.7380614208846
5752 42.7380614208846
5844 41.4203397412246
5844 41.4203397412246
5844 41.4203397412246
5935 42.3993128383035
5935 42.3993128383035
5935 42.3993128383035
6026 43.2691882448379
6026 43.2691882448379
6026 43.2691882448379
6118 43.2730574365887
6118 43.2730574365887
6118 43.2730574365887
6210 43.5387306727853
6210 43.5387306727853
6210 43.5387306727853
6300 43.8991230747233
6300 43.8991230747233
6300 43.8991230747233
6391 43.6608214133575
6391 43.6608214133575
6391 43.6608214133575
6483 43.1138630056904
6483 43.1138630056904
6483 43.1138630056904
6575 43.2501126226256
6575 43.2501126226256
6575 43.2501126226256
6665 44.2779659456214
6665 44.2779659456214
6665 44.2779659456214
6756 44.4656301935504
6756 44.4656301935504
6756 44.4656301935504
6848 44.8880479357376
6848 44.8880479357376
6848 44.8880479357376
6940 45.7661855028228
6940 45.7661855028228
6940 45.7661855028228
7030 46.2921928285207
7030 46.2921928285207
7030 46.2921928285207
7121 47.2898867311011
7121 47.2898867311011
7121 47.2898867311011
7213 47.0522609110891
7213 47.0522609110891
7213 47.0522609110891
7305 47.3988999414764
7305 47.3988999414764
7305 47.3988999414764
7396 46.4036221717359
7396 46.4036221717359
7396 46.4036221717359
7487 47.2087857686396
7487 47.2087857686396
7487 47.2087857686396
7579 48.2705156394969
7579 48.2705156394969
7579 48.2705156394969
7671 48.7051830062226
7671 48.7051830062226
7671 48.7051830062226
7761 48.2996444018477
7761 48.2996444018477
7761 48.2996444018477
7852  48.559201273792
7852  48.559201273792
7852  48.559201273792
7944 49.4717560618217
7944 49.4717560618217
7944 49.4717560618217
8036 49.0866278663329
8036 49.0866278663329
8036 49.0866278663329
8126 49.9375797149597
8126 49.9375797149597
8126 49.9375797149597
8217 48.6674541626442
8217 48.6674541626442
8217 48.6674541626442
8309 46.0683710681609
end
format %td date
expand 3
bysort date : gen qdate = yq(date, _n)
by date: replace ipau= . if _n > 1
gen logw = log(ipau)
ipolate ipau time1 gen(linear) epolate

[Clyde Schechter]

So, there are a few problems with your code. There is no need to -expand 3- because the data already has 3 copies of the data for each quarter. You generate a variable logw that you never use. No harm in that, I guess, but a waste of time. Your -ipolate- command has two problem: it refers to a variable time1 that does not exist, and it lacks the necessary comma separating its varlist from its options. I imagine that the variable time1 that exists in your mind is a date that counts up the months within the quarters. Putting all of this together, it seems you want:

bysort date : gen qdate = yq(date, _n)
by date: replace ipau= . if _n > 1
by date: gen time1 = mofd(date) + _n - 1
format time1 %tm
gen logw = log(ipau)
ipolate ipau time1, gen(linear) epolate

I will also just remark that linear interpolation is not always a good way to fill in missing observations in time series. I leave it to you and your knowledge of the subject matter to determine its suitability in this particular application.

[Justin Niakamal]

This thread is a helpful reference for this topic. Here's some example code as well for your data series.

local v = "AUSPROINDQISMEI"
!curl -L https://fred.stlouisfed.org/series/`v'/downloaddata/`v'.csv > "`v'.csv"
insheet using "`v'.csv", comma clear
erase "`v'.csv"

rename date date1

gen date = qofd(date(date1, "YMD"))
gen mdate = mofd(dofq(date)) + 1
tsset mdate
format mdate %tm
tsfill
sort mdate

gen lnprod = log(value)

mipolate lnprod mdate, gen(lnprod2) pchip
gen iprod = exp(lnprod2)


set scheme s1color

twoway line iprod mdate, ms(+) || scatter value mdate, msize(tiny) ///
legend(order(1 "guessed" 2 "known") region(lcolor(white)))  xtitle("") yla(, ang(h)) ytitle("2015 = 100", angle(v)) xlab(#4 ,labsize(small))