Converting calendar dates to different quarters

[Dennis Laat]

Dear all,

I've a question on how to convert calendar dates to different (i.e. fiscal) quarters in Stata 16.1.

I'm using two databases that have different ways to classify years. To ensure consistency and comparability, I want to convert for one of the databases' calendar dates to fiscal quarters. The fiscal year runs from June 1^st in year 1 through May 31^st in year t+1. Therefore, the first fiscal quarter should start from June 1^st through August 31^st, the second fiscal quarter should start September 1^st through November 31^st ,and etc.

So far, I used the following codes, though with no luck:

gen fiscalquarter=quarter(calendardate)
replace fiscalquarter=fiscalquarter-1 if month(calendardate)>=6 & month(calendardate)<=8

Does anyone know how to convert it?

Any help is much appreciated!

Kind regards,

Dennis

[Sergiy Radyakin]

Easy:

clear all
input str20 date
"2020-07-03"
"2020-10-29"
"2021-02-28"
"2021-05-09"
end

generate ddate=date(date,"YMD")
format ddate %td
generate calq=real(substr("334441112223",month(ddate),1))
list

Results in:

     +-------------------------------+
     |       date       ddate   calq |
     |-------------------------------|
  1. | 2020-07-03   03jul2020      1 |
  2. | 2020-10-29   29oct2020      2 |
  3. | 2021-02-28   28feb2021      3 |
  4. | 2021-05-09   09may2021      4 |
     +-------------------------------+

[Nick Cox]

Consider also

gen wanted = quarter(dofm(monthly(substr(date, 1, 7), "YM") - 5))

list

As in elementary algebra, work from the inside outwards

* day of month is irrelevant, so ignore it
substr(date, 1, 7)

* extract monthly date
monthly(substr(date, 1, 7), "YM")

* subtract 5 to compensate for starting the year 5 months later than conventional calendar
monthly(substr(date, 1, 7), "YM") - 5

* extract quarter -- we need two steps, monthly date to daily date, and then quarter  
quarter(dofm(monthly(substr(date, 1, 7), "YM") - 5))

See also Section 4 in https://www.stata-journal.com/articl...article=st0394

[Dennis Laat]

Thanks, it works! Much appreciated Sergiy and Nick!