Changing Daily Data into Monthly Data

[Max de Vries]

I have a problem with changing Daily Dates into Monthly Dates. When I go to browse --> tools --> variables manager --> format (create) and change daily into monthly, the dates change to the wrong dates.

The first picture is what is seen before the change from Daily to Monthly. The second picture is what is seen after the change. How can I make this into good formatted monthly dates? A link to a good formatted excel sheet would also help enormously, as i could insert it into my stata file.

[Clyde Schechter]

Please read the FAQ for good advice on how to pose questions so that they are clear, well-defined, and likely to draw healthy and timely responses. In particular pay attention to several important points:

1. If you don't show what code you used, nobody can tell you what was wrong with it.

2. Screenshots of data are useless. There is no way to import them into Stata to work with them. We have the -dataex- command available. Install it (-ssc install dataex-) and use it whenever you post example data.

Also note that it is the norm in this community to use our real first and last names, to encourage collegiality and professionalism. I doubt your real family name is Stata. If it is, accept my apology. If it's not, please click on CONTACT US in the lower right corner of the page and send a message to the Forum administrator requesting a name change. (Unfortunately, you cannot edit your username in your profile yourself.)

All of that said, what you have done is change the display format of the date variable without changing the underlying numerical representation from daily to monthly. Consequently what you see makes no sense.

What you need to do is first actually create a Stata internal monthly date from your daily date:

gen monthly_date = mofd(Date)
format monthly_date %tm

Finally, from a perspective of professionalism, you should not use Excel to do data analysis. It leaves no audit trail of what you have done. And in early versions of Excel its statistical functions were inaccurate and did incorrect things like treat blank cells as zeroes. Excel is fine for displays of data that are easy for humans eyes to parse. Excel is fine for shipping data sets among people who do not use the same statistical package. It might be OK for just "playing around" with data. But it should never be used for an analysis that you expect anyone to take seriously. That is what Stata or other statistical packages are for.

[Nick Cox]

Please review:

http://www.statalist.org/forums/help#realnames We ask for full real names here, not pseudonyms or joke names

http://www.statalist.org/forums/help#stata We ask for data examples that can be copied and pasted, not screenshots.

It seems that you have daily dates, numerically # of days after 1 January 1960, with values like 0, 31, 60. Changing the display format to %tm won't change those underlying values. You're just telling Stata: those values like 0, 31, 60 are really the number of months after January 1960. Look carefully at what you have to see that the monthly dates jump by 28, 29, 30, 31 months.

You need something quite different, namely

gen Mdate = mofd(Date)
format Mdate %tm

which all follows from a careful reading of e.g.

help datetime

It's not easy to use dates successfully in Stata without a full reading of the help on dates. Short-cuts just lead to wasted time.

EDIT: Clearly crossed with Clyde's almost identical post. I endorse his strictures on MS Excel.

[Max de Vries]

The name change is requested! And the code works perfectly, thank you both very much for that!

I agree that for data analysis stata is better than excel. However, the data I have received was in excel.