Converting monthly values to quarterly values

[Jule Beck]

Dear statalist-members,

I am working with an asylum data-set from EUROSTAT (http://appsso.eurostat.ec.europa.eu/...ppctzm&lang=en).
The data is only available with monthly values. However, I need to aggregate it to quarterly values.
The time-ID was in the following form: 2008M01 (string variable).
Therefore, I used the following command to destring the time-ID:

gen int year = real(substr(time,1,4))
gen int month = real(substr(time,6,2))

gen int monthly = ym(year,month)
format monthly %tmMCY

Until this point everything worked out perfectly fine.

Then, my idea was to use the date and time functions and "qofd" in particular to create year-quarters and to collapse the data afterwards:
gen yq = qofd(monthly)
format yq %tq

collapse (sum) fasyl_appl, by(country yq)

Unfortunately, when I tried to generate "yq" the year quarters were not correct and I have no clue why this is the case.


************************************************** ************************************************** *************************************

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 time str48 country long fasyl_appl int(year month monthly) float yq
"2008M01" "Belgium"        1060 2008 1 576 6
"2008M01" "Bulgaria"          . 2008 1 576 6
"2008M01" "Czech Republic"  155 2008 1 576 6
"2008M01" "Denmark"         235 2008 1 576 6
"2008M01" "Germany"        2395 2008 1 576 6
end
format %tmMCY monthly

************************************************** ************************************************** **************************************

Your help and advice is greatly appreciated!
Jule Beck,
University of Konstanz

[Nick Cox]

Monthly to quarterly needs qofd(dofm()), which is not what you tried.

[Jule Beck]

Thank you so much!
It finally worked out with
gen yq = qofd(dofm(monthly))
format yq %tq

[Nick Cox]

My program -convdate- in the -numdate- package on SSC is another way to do it.

[William Lisowski]

Welcome to Statalist, Jule.

Nick solved your problem and didn't complicate the solution by focusing on side issues.

I just want to point out, for the benefit of others who may read this, that one can go from the time string variable to the monthly date variable in a single step with the monthly() function, which has the ability to sort out a variety of representations of monthly dates, as long as you tell it whether it's the year or the month that comes first. See the example below. The output of help datetime translation as well as the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF are the definitive sources of further information on this. Nick's numdate simplifies this process as well as converting date types.

. set obs 1
number of observations (_N) was 0, now 1

. generate time = "2008M01"

. generate monthly = monthly(time,"YM")

. format monthly %tmMCY

. list

     +-----------------------+
     |    time       monthly |
     |-----------------------|
  1. | 2008M01   January2008 |
     +-----------------------+