Convert monthy data to quarterly data

[Ana Vasconcelos]

Hello,

I have a panel data with monthy data (from 1984m1 - 2016m5) that I want to convert to quarterly data. Moreover, I want to use only the last value of each quarter.
I use the following code to convert the data:

gen date1=monthly(date,"YM")

format date1 %tm

gen year_q=qofd(date1)

format year_q %tq


However, when I reach this last step...stata converts the data since the begining of 1960. For example, it says that my monthy value 1984m1 is equal to 1960q4.

My two questions are:

1 - How do I match the monthy data in the corresponding quartely period? Therefore: 1984m1=1984q1; 1984m2=1984q1; 1984m3=1984q1; 1984m4=1984q2; ...)
2 - What is the code that I have to use to select only the last value of each quarter (march, june, september, december)?

Thank you very much!

[Alberto Dorantes]

Hi Ana.
I assume that you have a variable date that is monthly, and you have another variable such as price for each month. If that is the case, then you can try the following code to convert your monthly dataset to a quarterly dataset keeping only the price for the last month of each quarter:

gen day=dofm(date)
format day %td
gen quarter=qofd(day)
format quarter %tq

collapse (last) price, by(quarter)


I hope this help.
Regards... Alberto

[Nick Cox]

Changing the display format never changes the type of date you are referring too. Stata isn't changing anything here at all: what you did yourself is change to a different display format, In effect, you are saying to Stata: You know that monthly date? It's really a quarterly date. That's like saying: You know that weight in pounds? It's really a weight in kg! But there is a conversion factor you need to apply.

Also, if date1 is a monthly date then applying qofd() is saying: You know that monthly date? It's really a daily date and I want you to convert that to a quarterly date.

Here is some technique that will solve the problem.

clear 
set obs 2 
gen mdate = ym(1984, 1) in 1 
replace mdate = ym(2016, 5) in 2 
format mdate %tm
list 

     +--------+
     |  mdate |
     |--------|
  1. | 1984m1 |
  2. | 2016m5 |
     +--------+

gen qdate = qofd(dofm(mdate))
format qdate %tq 

list 
     +-----------------+
     |  mdate    qdate |
     |-----------------|
  1. | 1984m1   1984q1 |
  2. | 2016m5   2016q2 |
     +-----------------+

.

In short, the way to convert monthly dates to quarterly dates is to apply qofd(dofm()) (think that this pair of functions applied one after the other telescopes to qofm(), except that there is no such function). Following that, you then assign a suitable quarterly date format. But changing the format itself won't work, as you discovered.

This all is documented in

 
help dates

See also http://www.stata-journal.com/sjpdf.h...iclenum=dm0067