Keeping end of month date from daily data

[Sandra Rhouma]

Dear readers,

I have daily data from the 01/01/2007 to 01/08/2014 for 4 different variables. I would like to keep only the end of month data (31/01/2007, 28/02/2007, 31/03/2007 etc up to 31/07/2014) for those variables. Is there an easy way to do this on Stata? Is it also possible with weekly data?

Thank you in advance for your answer

Regards,

Sandra

[Clyde Schechter]

So assuming your daily date is in a variable called date, and that is a genuine Stata date, not a string that is human-readable as a date, you can do this:

gen long first_of_next_month = dofm(mofd(date)+1)
keep if date + 1 == first_of_next_month

[Sandra Rhouma]

Thank you very much, it worked.

[Allah Ditta Nawaz]

Clyde Schechter I guess it misses some values when there is weekends at the month end and incase of leap year.
Can you confirm, please?

[Nick Cox]

I don't follow what is being asked in #4 Clyde Schechter's solution is based on the identity

last day of this month is one day before the first day of the next month

which sounds trivial, but is the key to programming this! It is true over varying lengths of month, including the cases of leap years.

If, per contra, you want something different, such as the last Friday in each month, then see now the more extended discussion

Cox, N. J. 2019.. Speaking Stata: The last day of the month. The Stata Journal 19(3): 719–728. https://doi.org/10.1177/1536867X19874247

Fortuitously, but fortunately, this becomes publicly readable on the release of Stata Journal 22(3), which should be very soon.

[Nick Cox]

If your data are only for some days, e.g. not weekends or holidays, then a flag for the last day of each month present in your dataset is -- given a daily date ddate --

gen mdate = mofd(ddate)
bysort mdate (ddate) : gen islast = _n == _N

[Nick Cox]

As promised in #5 a link like https://www.stata-journal.com/articl...article=dm0100 now leads directly to a pdf, regardless of whether you or your workplace have a subscription to the Stata Journal.