Converting MDY date to quarterly date (i.e. 2001q1)

[Helen Chang]

Hi,

My data contains a string variable of date in MDY format ( 09/26/02, 12/19/13,.....). I want to convert it into quarterly format, such as 2002q3, 2013q4.

I have tried to use the following codes but the quarterly dates turn out to be wrong ( for example, it shows 1960q2 for 09/26/02).
encode stringdatevar, gen (newdatevar)
gen qtr=qofd(newdatevar)
format qtr %td


Would you mind letting me know what have I done wrong and how to fix this problem?

Thanks a lot!

[Rich Goldstein]

if your date variable is in Stata date form (as the FAQ requests, please use -dataex- to show example data), then you can just use the "mofd" function; see

help datetime

and click on "sif-to-sif" conversion to get instructions

if your data are not in Stata date form, read more of the above help file; for specific code/instructions, please read and follow the FAQ

[Clyde Schechter]

You went wrong with -encode-. That command doesn't do anything remotely like what you need. What you want is:

gen daily_date = daily(my_string_date_variable, "MD20Y")
format daily_date %td
gen quarterly_date = qofd(daily_date)
format quarterly_date %tq

Confusion about what -encode- does is widespread. I'm not sure why, because I think the Stata documentation on it is very clear. Anyway, you should only use encode when you have a string variable whose values are inherently non-numeric, but you want to represent them with an arbitrary numeric coding so that you can use them as panel-variables or factor-variables or something like that. For example, if you have a variable, religion, which takes on values like Christian, Muslim, Jewish, Hindu, Buddhist, and maybe an Other category and you want to associate 1 = Christian, 2 = Muslim, 3 = Jewish, 4 = Hindu, 5 = Buddhist, and 6 = Other--that is what -encode- is for.

Here's what -encode- did with your date variable. It sorts them into alphabetic order (which is not chronological order), so of the dates you show 09/26/02 comes first. So it creates a numeric variable with the value 1 wherever the string is "09/26/02." Then comes 12/19/13. The numeric value now gets a value of 2 wherever the string is "12/19/13." Etcetera. It also creates a value label and attaches it to the numeric variable, so when you -browse- or -list- the variable it still looks like "09/26/02" etc. But as far as Stata is concerned, the number 1 means the date 02jan1960, and 2 means the date 03jan1960. So this -encode- has created a mess.

General rule: if your string variable looks like numbers, or looks like things that have a natural numerical interpretation (like dates), then stay as far away from -encode- as possible: it will wreak havoc with your data. When you have a string variable that is obviously non-numeric, just a list of names of things, but you need a numerical code for it for purposes of data analysis, that is where -encode- is useful.

Added: Crossed with #2. I understood you to say in #1 that your date is a string variable, so that the advice for date-to-date conversion functions would be premature.

[William Lisowski]

Let me expand a bit on Rich's advice.

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 that Rich referred you to 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 (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

[Helen Chang]

Thank you, Clyde, for providing the code and excellent explanation on -encode-. The code works great!

Stata User's Guide and -help datetime- help answer many of my questions and confusions, thanks to all the suggestions from Rich and William.