convert dates to monthly date

[kevin smithy]

Hi,

I need help converting my date column into monthly dates so that stata can read. Stata reads the date variable as if they are daily dates, not monthly. When I tried to convert them to monthly data, stata replaced the date columns with random monthly dates, not in the order specified in the file.
Can someone please point me in the right direction?

[Clyde Schechter]

All Forum members are asked to familiarize themselves with the FAQ so that they can get the most out of their Statalist activities. Please read them, with attention to #12. There you will learn, among other things, that attachments are strongly discouraged. Also, the way to present example data is not to show an Excel file, but to use the -dataex- command with your Stata data file. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

As for troubleshooting your attempt to convert the dates, you do not show the code you used, so it is anybody's guess what might have gone wrong.

So please post back with -dataex- generated example data and you will likely get a timely and helpful response on how to get the monthly dates you want. And if you also want an explanation of why your own attempts failed, post that code as well.

[William Lisowski]

Stata's "date and time" 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 and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

As Clyde suggested, without knowing what you did, it's difficult to tell you what you did wrong.

The reading recommended above suggests that

generate datem = mofd(date)
format %tm datem

will create Stata monthly dates from the daily dates that Excel provided.

Stata reads the Excel date variable as if it contains daily dates, because that's how Excel stores dates - then Excel formats has the dates to suppress the month. I mention this because it is important to understand the difference between how a value is stored and how it is visually presented to the user. And misunderstanding this is a possible explanation of what you did wrong.

[Joro Kolev]

Like this:

. clear

. import excel "C:\Users\gikolev\Downloads\EMPI and dates.xlsx", sheet("Sheet1") firstrow allstrin
> g
(2 vars, 109 obs)

. gen montlydate = monthly(date,"20MY")
(109 missing values generated)


. drop montlydate

. gen montlydate = monthly(date,"M20Y")

. format montlydate %tm

[kevin smithy]

thanks for the help