How to keep the end of each quarterly observation from monthly dataset

[Jae Li]

Hi Statalists,

I have a monthly dataset and would like to convert to the quarterly basis and only keep the end of each quarterly observation. For example,

 date    
31dec1985
31jan1986
28feb1986
31mar1986
30apr1986
30may1986
30jun1986
31jul1986
29aug1986
30sep1986

I only want to keep the rows in the date on 31dec1985, 31mar1986, 30jun1986 and 30sep1986, are there any professionals who know how to achieve that? Many thanks indeed in advance!!!

[Nick Cox]

Yours isn't a monthly dataset! It is a dataset based on daily dates that is mostly gaps and which people can see is monthly data coded by a convenient daily date towards the end of the month. Stata can't recognise that as you wish until you use mofd() to convert to monthly dates.

Also, the data example isn't friendly. Please do read and act on FAQ Advice #12 as every new message prompt asks every poster to before posting.

Assuming that is fixed:

A necessary and sufficient condition for a month to be last in its quarter -- evident to amateurs as well as professionals -- is that the month number is divisible by 3. Hence

clear
set obs 12 
gen mdate = ym(2017, _n) 
format mdate %tm

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

list 

     +------------------+
     |   mdate    qdate |
     |------------------|
  1. |  2017m1   2017q1 |
  2. |  2017m2   2017q1 |
  3. |  2017m3   2017q1 |
  4. |  2017m4   2017q2 |
  5. |  2017m5   2017q2 |
     |------------------|
  6. |  2017m6   2017q2 |
  7. |  2017m7   2017q3 |
  8. |  2017m8   2017q3 |
  9. |  2017m9   2017q3 |
 10. | 2017m10   2017q4 |
     |------------------|
 11. | 2017m11   2017q4 |
 12. | 2017m12   2017q4 |
     +------------------+

keep if mod(month(dofm(mdate)), 3) == 0 

list 

     +------------------+
     |   mdate    qdate |
     |------------------|
  1. |  2017m3   2017q1 |
  2. |  2017m6   2017q2 |
  3. |  2017m9   2017q3 |
  4. | 2017m12   2017q4 |
     +------------------+

[Jae Li]

@Nick Cox Hi Professor Nick, thank you for your reply! Here is my data by using -dataex-:

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input long date float(qdate yq)
 9496 3165 103
 9586 3195 104
 9677 3225 105
 9769 3256 106
 9861 3287 107
 9951 3317 108
10042 3347 109
 9496 3165 103
 9586 3195 104
 9677 3225 105
 9769 3256 106
 9861 3287 107
 9951 3317 108
10042 3347 109
10134 3378 110
10226 3408 111
10317 3439 112
10408 3469 113
10500 3500 114
10591 3530 115
10682 3560 116
10773 3591 117
10864 3621 118
11869 3956 129
11961 3987 130
12053 4017 131
12143 4047 132
12234 4078 133
12326 4108 134
12418 4139 135
13604 4534 148
13695 4565 149
13787 4595 150
13879 4626 151
13969 4656 152
end
format %d date
format %tq qdate
format %tq yq

------------------ copy up to and including the previous line ------------------


My apologies for the data example confusion (Please ignore qdate as I got it wrong). The above is the actual data loaded for your review. I downloaded this dataset from a monthly stock file on CRSP database. I am still bit confused, why do you think they are based on daily dates with most gaps?

Many thanks indeed for your help!!

[William Lisowski]

To Stata, your data consist of daily dates with gaps. They are identified with a year, a month, and a day, and are stored as the number of days since January 1, 1960. Monthly dates would be identified with only a year and a month,, and would be stored as the number of months since January 1960.

Stata's "datetime" 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.

[Jae Li]

@William Lisowski Thank you for your explanations! I get your point now and will look into datetime more.