Formatting dates

[Sophie Goyet]

Dear All,
I am using Stata/SE 13.1 and having trouble when formatting a date.

I have dates extracted from excel, using the input excel function of stata

codebook date
type: numeric daily date (int)
rage [20451,20818]
or equivlently 01jan2016,30dec2016] units: days

list date in 1/5
1. 01jan2016
2. 01jan2016
3. 06jan2016
4. 13jan2016
5. 22jan2016

Now I want to format it in a monthly date, showing "Apr 2009".
To be absolutely sure I do not make any mistake while typing my command, I use the Variables manager to format my date:

format %tmMon_CCYY date

Now I want to see if it work:

codebook date

I obtain:
type: numeric monthly date
range: [20454, 20818]
or equivalently [3664m7, 3694m11] units: months

list date in 1/5
1. Jul 3664
2. Jul 3664
3. Dec 3664
4. Jul 3665

What happened? What did I do wrong?

I do experience the same trouble when trying to format my date in year.
Strangely, if I extract the day, the month and the year it works well:

gen datem=month(date)
list date datem in 1/5

1. 01jan2016 1
2. 01jan2016 1
3. 06jan2016 1
4. 13jan2016 1
5. 22jan2016 1

gen datey=year(date)
list date datey in 1/5


1. 01jan2016 2016
2. 01jan2016 2016
3. 06jan2016 2016
4. 13jan2016 2016
5. 22jan2016 2016


gen dateday=day(date)
list date dated in 1/5

1. 01jan2016 1
2. 01jan2016 1
3. 06jan2016 6
4. 13jan2016 13
5. 22jan2016 22

I have tried to recreate my date using the mdy()

gen datenew=mdy(datemonth,dateday,dateyear)
list date datenew in 1/5

1. 01jan2016 20454
2. 01jan2016 20454
3. 06jan2016 20459
4. 13jan2016 20466
5. 22jan2016 20475


Then I formatted it in daily and it worked
format datenew %td
list date datenew in 1/5

1. 01jan2016 01jan2016
2. 01jan2016 01jan2016
3. 06jan2016 06jan2016
4. 13jan2016 13jan2016
5. 22jan2016 22jan2016

But when trying to get the monthly format, it is going wrong again

format datenew %tm
list date datenew in 1/5

1. 01jan2016 3664m7
2. 01jan2016 3664m7
3. 06jan2016 3664m12
4. 13jan2016 3665m7
5. 22jan2016 3666m4


I have already spent 2 hours trying to understand this issue and fixing it, reading other posts from the statalist, at statblog, at the help files in stata, the PDF manual, and other online resources. I have been using stata for many years for data analysis in epidemiology and I have never been stuck like today...

Thanks for your help

Sophie

[Joseph Coveney]

Your variable contains the number of days since January 1st, 1960. You need the number of months since January 1960. You can get that using the Stata function mofd(). I show an example below using the codebook numerical range of values that you report first.

.ÿ
.ÿversionÿ14.2

.ÿ
.ÿclearÿ*

.ÿsetÿmoreÿoff

.ÿ
.ÿinputÿlongÿrng_dt

ÿÿÿÿÿÿÿÿÿÿÿrng_dt
ÿÿ1.ÿ20451
ÿÿ2.ÿ20818
ÿÿ3.ÿend

.ÿ
.ÿformatÿrng_dtÿ%tdCY-N-D

.ÿ
.ÿ*
.ÿ*ÿBeginÿhere
.ÿ*
.ÿgenerateÿintÿrng_moÿ=ÿmofd(rng_dt)

.ÿformatÿrng_moÿ%tmCY-N

.ÿ
.ÿlist,ÿnoobs

ÿÿ+----------------------+
ÿÿ|ÿÿÿÿÿrng_dtÿÿÿÿrng_moÿ|
ÿÿ|----------------------|
ÿÿ|ÿ2015-12-29ÿÿÿ2015-12ÿ|
ÿÿ|ÿ2016-12-30ÿÿÿ2016-12ÿ|
ÿÿ+----------------------+

.ÿ
.ÿexit

endÿofÿdo-file


.

[Sophie Goyet]

Joseph, you saved my day!
It works!
Thank you very much
Sophie

[Nick Cox]

Changing the display format does not change the kind of date you are using. This has been explained many times here, so I don't know what posts you are reading that appear to imply otherwise.

Here is the main point as concisely as I can explain it. But first you need to study these examples.

. di daily("01jan2016", "DMY")
20454

. di %tm daily("01jan2016", "DMY")
 3664m7

. di %tm mofd(daily("01jan2016", "DMY"))
 2016m1

. di %td 0
01jan1960

. di %tm 0
 1960m1

1. Apart from years held directly (e.g. 2016 makes sense as a calendar year) dates in Stata are numeric and just counted from an origin at the beginning of 1960. Days are how many days since 1 January 1960, months how many months from January 1960, and so forth.

2. They are given their human meaning by specifying a display format.

3. Recent daily dates thus have numeric values of the order of 20000.

4. If you change the display format, then tautologically but crucially, all that you change is how a number is displayed. So, if you instruct Stata that your recent dates are not daily but monthly, Stata takes you at your word and shows a monthly date several centuries into the future

5. In order to change a date from one kind to another, you need to do the calculation yourself (in this case, too complicated to appeal) or call up a function provided for the purpose, in this case mofd().

For more see e.g. http://www.stata-journal.com/sjpdf.h...iclenum=dm0067

The help on dates and times does explain this. You just need to skim and skip the parts that don't apply to your immediate problem.