Extract quarter from date

[Nikolaos Kanellopoulos]

Dear all,
Good morning.

From a dataset containing monthly dates I want to extract the relevant quarter. Most probably because I am doing something wrong I do not get the results I want. Please see a fragment of my data and the code I am using below.

Kind regards,
Nikos

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(year month)
2009  1
2009  2
2009  3
2009  4
2009  5
2009  6
2009  7
2009  8
2009  9
2009 10
2009 11
2009 12
2010  1
2010  2
2010  3
2010  4
2010  5
2010  6
2010  7
2010  8
2010  9
2010 10
2010 11
2010 12
end

gen time = ym(year , month)
format time %tm
tsset time
gen quarter = quarter(time)
recode month (1/3=1)(4/6=2)(7/9=3)(10/12=4), gen(quarter2)
ta quarter
l time quarter*, sep(3)

[William Lisowski]

The short answer is that the quarter() function accepts as its argument a SIF daily date, and you have instead applied it to a SIF monthly date.

If the meaning of that answer is not immediately apparent, it is because Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

Here is some code based on yours to demonstrate the use of quarter() in your situation.

gen time = ym(year , month)
format time %tm
gen quarter = quarter(dofm(time))
recode month (1/3=1)(4/6=2)(7/9=3)(10/12=4), gen(quarter2)
tab quarter quarter2

. tab quarter quarter2

           |               RECODE of month
   quarter |         1          2          3          4 |     Total
-----------+--------------------------------------------+----------
         1 |         6          0          0          0 |         6 
         2 |         0          6          0          0 |         6 
         3 |         0          0          6          0 |         6 
         4 |         0          0          0          6 |         6 
-----------+--------------------------------------------+----------
     Total |         6          6          6          6 |        24

[Nikolaos Kanellopoulos]

Dear William,

thanks a lot for the answer.
It's true that repetition is the mother of all learning and apparently it's high-time for a revision.

Regards,
Nikos

[William Lisowski]

I will admit that my first attempt at solving your problem failed badly, and revisiting the documentation told me I'd confused quarter() with qofd(). I should have learned by now that the times when I'm certain of the answer are the times I most need to visit the documentation.

[Clive Blundell]

I have a quarterly variable qtr that is of the form YYYYQQ and is in format %tq. I want to extract the year from this. What doesn't work is

gen year_bit=year(qtr)

Any ideas?
Clive

[Clyde Schechter]

gen year_bit = year(dofq(qtr))

The -year()- function requires a daily date argument. So to get year from a quarterly date you have to go through -dofq()- which calculates the daily date corresponding to the first date in the quarter, and then pull the year from that.

[Nick Cox]

The definition of yq(1960, 1) as 0 allows another approach which may be found congenial or instructive. A simple example beats lengthy explanation:

clear 
set obs 12 
gen qdate = -5 + _n
clonevar same_thing = qdate 
format qdate %tq 

gen year = 1960 + floor(qdate/4)

list , sep(4)

     +--------------------------+
     |  qdate   same_t~g   year |
     |--------------------------|
  1. | 1959q1         -4   1959 |
  2. | 1959q2         -3   1959 |
  3. | 1959q3         -2   1959 |
  4. | 1959q4         -1   1959 |
     |--------------------------|
  5. | 1960q1          0   1960 |
  6. | 1960q2          1   1960 |
  7. | 1960q3          2   1960 |
  8. | 1960q4          3   1960 |
     |--------------------------|
  9. | 1961q1          4   1961 |
 10. | 1961q2          5   1961 |
 11. | 1961q3          6   1961 |
 12. | 1961q4          7   1961 |
     +--------------------------+

Similarly given Stata half-yearly, monthly, weekly dates and a desire to convert to years, the divisor changes to 2, 12, 52 respectively.

For daily dates, year() is already available and for clock date-times, you're better off (I suggest) using year(dofc()) than grappling with leap years and so forth.

[Hemanshu Kumar]

And there's also

gen year_bit = real(substr(strofreal(qtr, "%tq"), 1, 4))

[Nick Cox]

Let me riff a little on three solutions so far.

year(dofq()) is a Stataish solution. It might seem puzzling -- to very experienced users too -- why there isn't say yofq() or something like dateconvert(, "Y", "Q"), but there you go.

1960 + floor(qdate/4) is a poor solution in this sense: the origins of Stata dates (0 is a date or date-time as early as possible in 1960) are documented but arbitrary. So anyone looking at the code who didn't know that might well be puzzled. Why 1960?

real(substr(strofreal(, "%tq"), 1, 4)) has its own logic: if I can get a string date out of that quarterly date with year first, then I just need to pull out the first 4 characters and then get those mapped to a number.

[Clive Blundell]

Many thanks guys, for these great solutions. So useful.