Create datapoint for last date of the month

[Alexander BECK]

Hi all,

I am currently trying to isolate the last datapoint in a month.
My dataset looks as follows

date - variable (not stata format) YYYYMMDD
year - variable YYYY
month - variable MM
stock_XYZ - I want the last date from

My dataset is trading data which means the last day of the month is not always the last day available.

My approach was to first isolate the last day of the month and then go from there and get the last datapoint.

The code I used was:

egen date_last = max(date), by(year month)

gen stock_XYZ_last = stock_XYZ if date== date_last

While the approach works it does not give me the right answer.

For example, I get for the first month of my dataset which is not the date 19960131 but rather the date 19960132 which is not possible and does not exist in my dataset.
Generally I get in most cases either one above or one below but not always the right date.
Anyone has a workaround or an idea why this is happening?

Thanks in advance.

Best,
Alex

[Nick Cox]

There is no data example here, contrary to FAQ Advice #12, but guesses are possible.

I suspect your problems lie in use of a non-standard method of holding daily dates. That's never a good idea, unfortunately.

Here's the issue on my guess. By default you are trying to hold daily dates as integers in a float variable. There aren't enough bits in a float to do that without rounding.

 
. clear

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

. gen float mydate = 19960131

. format mydate %8.0f

. l

     +----------+
     |   mydate |
     |----------|
  1. | 19960132 |
     +----------+

.

It's hard to say what is best, but starting again may be the answer, depending on how your dates arrive.

[Alexander BECK]

Thanks Nick for your answer. Indeed it was an issue with my date variable.

I solved it now by transforming it first to a string and then to a date variable (was familiar with this was, I am sure there is also a way to directly go from number to date) and applied the same code.

That worked!

Thank you