Change date format: ddmmmyyyy to Day Month Year columns

[Chris Parfitt]

Hi all,

This is my first post here. For my master thesis, I'm doing research regarding M&A deals and innovation (using patent data).

The data I got from SDC platinum has the dates in ddmmmyyyy format (for example, 30aug2011 or 15dec2000)

I put the describe function as an attachment.

I'd like to put it into three separate columns, Day Month Year. Any idea how to do this?

Thanks!
Chris

[Igor Paploski]

Hi Chris,

Please try:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 originaldate
"30aug2011"
"15dec2000"
end

gen date = date(originaldate, "DMY")
format date %td

gen day = day(date)
gen month = month(date)
gen year = year(date)

[Chris Parfitt]

Hi Igor,

Thanks for your quick reply! That worked for the two examples perfectly, but do you have any idea how to do it for a whole variable list (For example, DateAnnounced) I have around 1700 observations, would I have to manually list them all?

Kind regards,
Chris

[Igor Paploski]

Hi Chris,

No, as long as your observations are on the ddmmmyyyy format, it should work.

Dates are a bit tricky because it's quite common that they are recorded in different formats (17/03/2017; 17-03-2017; 03/17/2017; 03/17/17; 17mar2017) in the same database, which is a complete pain. I suggest that you read the manual for working with dates in Stata, a lot a good practices and tips can be found there.

Last but not least, if the examples you provided do not represent well your database, please consider sharing a snippet of your dataset using dataex. This saves you and whoever is replying some time figuring out problems that are not exactly what you are asking help for. You can find instructions for using dataex in topic 12 of the forum FAQ.

Best;

[Chris Parfitt]

Hi Igor,

When typing in the command you suggested, I only got an output for the two in quotations, and stata generated the rest of the missing variables. Any idea why it might do this and how to do it for all the observations under DateAnnounced?

I attached the dataex and a snippet of my browse data. I'm quite sure all of mine are formatted in ddmmmyyyy format.

[Igor Paploski]

Hi Chris,

Note that you have 2 date variables (DateAnnounced and DateEffective). I don't know which one you are interested in separating day, month and year, so the code below does it for both date variables.

gen day_announced = day(DateAnnounced)
gen month_announced = month(DateAnnounced)
gen year_announced = year(DateAnnounced)

gen day_effective = day(DateEffective)
gen month_effective = month(DateEffective)
gen year_effective = year(DateEffective)

Additionally, your date variables are not on the ddmmmyyyy format, they are integer numbers that Stata reads as dates and displays as ddmmmyyyy. It is one of the easiest scenarios to work with dates.

Last but not least, for future reference, please copy the code that dataex returns in the Stata main window (this way others can copy the code and recreate your dataset so they can test solutions). Sharing a print screen of anything in this kind of scenario is almost always useless (no one is gonna take the time to type the output of dataex displayed on a print screen to re-create a database to solve a problem that's not theirs to begin with). Also, note that you can restrict the reach of dataex using a command such as:

dataex DateAnnounced DateEffective in 1/25

This will produce a code (that should be copied from Stata main window to here as text) that allow others to recreate only the variables DateAnnounced and DateEffective and only for the first 25 observations.

Best;

[Chris Parfitt]

Igor,

That worked perfectly for me! Thank you so much, much appreciated.

Have a great day,
Chris