Sort a database to reverse a time variable in order to draw a time graph with months as ticks (monthly data)

[Aziz Essouaied]

Hello Stata people;

I'm using the 13.1 version of Stata, I'm refering to this old post of M.@Nick Cox: https://www.statalist.org/forums/for...d-other-series in order to solve this problem.

I have this data at hand:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 month float price
"01/09/2025" 3697.45
"01/08/2025" 3373.96
"01/07/2025" 3285.78
"01/06/2025" 3266.41
"01/05/2025" 3289.55
"01/04/2025" 3288.78
"01/03/2025" 3123.08
"01/02/2025" 2858.05
"01/01/2025"  2798.2
"01/12/2024"  2624.6
"01/11/2024" 2651.22
"01/10/2024"  2746.7
"01/09/2024" 2634.76
"01/08/2024" 2503.38
"01/07/2024" 2447.36
"01/06/2024" 2326.72
"01/05/2024" 2327.28
"01/04/2024" 2285.99
"01/03/2024" 2231.56
"01/02/2024"  2044.2
"01/01/2024" 2039.15
"01/12/2023" 2062.92
"01/11/2023"  2044.9
"01/10/2023" 1984.01
"01/09/2023" 1848.49
"01/08/2023" 1939.83
"01/07/2023" 1964.34
"01/06/2023" 1919.57
"01/05/2023"  1965.2
"01/04/2023"  1990.6
"01/03/2023" 1969.55
"01/02/2023" 1826.74
"01/01/2023" 1928.12
"01/12/2022" 1824.32
"01/11/2022" 1768.61
"01/10/2022" 1632.38
"01/09/2022" 1660.88
"01/08/2022" 1708.58
"01/07/2022" 1766.43
"01/06/2022" 1817.45
"01/05/2022" 1847.26
"01/04/2022"  1911.7
"01/03/2022" 1953.04
"01/02/2022"  1900.7
"01/01/2022" 1796.12
"01/12/2021"  1828.6
"01/11/2021" 1775.92
"01/10/2021"  1783.9
"01/09/2021" 1756.66
"01/08/2021"  1815.8
"01/07/2021" 1814.12
"01/06/2021"  1771.6
"01/05/2021" 1904.74
"01/04/2021"  1767.7
"01/03/2021" 1715.24
"01/02/2021"  1728.8
"01/01/2021"  1849.7
"01/12/2020"  1895.1
"01/11/2020" 1779.86
"01/10/2020"  1879.9
"01/09/2020"  1893.9
"01/08/2020"  1970.5
"01/07/2020" 1971.68
"01/06/2020"  1770.7
"01/05/2020" 1725.65
"01/04/2020" 1716.75
"01/03/2020" 1604.65
"01/02/2020" 1626.35
"01/01/2020" 1580.85
"01/12/2019"    1523
"01/11/2019" 1456.35
"01/10/2019"  1506.4
"01/09/2019"  1487.6
"01/08/2019" 1526.55
"01/07/2019" 1430.55
"01/06/2019"  1413.2
"01/05/2019"    1296
"01/04/2019" 1285.15
"01/03/2019" 1291.15
"01/02/2019" 1325.45
"01/01/2019"  1322.5
"01/12/2018" 1281.65
"01/11/2018" 1220.45
"01/10/2018"  1217.7
"01/09/2018"  1183.5
"01/08/2018" 1206.85
"01/07/2018"  1219.2
"01/06/2018" 1250.55
"01/05/2018"  1303.5
"01/04/2018" 1316.25
"01/03/2018"  1323.9
"01/02/2018"  1320.3
"01/01/2018" 1343.35
"01/12/2017"  1296.5
"01/11/2017" 1282.15
"01/10/2017"  1270.5
"01/09/2017"  1284.8
"01/08/2017"  1322.2
"01/07/2017"  1273.4
"01/06/2017"  1242.3
end

As you can see, it's a data about the price of an ounce of gold from 1985 to 2025, defined monthly (the data ex stops at 2017).
I have two problems I would like to solve:
1) The time variable "month" starts the opposite way, from 2025 downward, I would like to change that and to inverse that variable in order for it to start from Januart 1985. Also, I would like to change its form in order to be able to apply the tsset comman on it.
2) I would like to draw the time graph showing the evolution of the gold price by year, yet since the data is monthly defined, I would like to have the months as ticks in the graph.

Any help with this please?

With many thanks!

[Clyde Schechter]

gen mdate = mofd(date(month, "DMY"))
format mdate %tm
tsset mdate

[Nick Cox]

This question is just a minor variation on that at #11 in the cited thread. You should be able to work out the slight difference in needed code.

There

gen mdate = monthly(substr(Date, 1, 7), "YM")

was a way to use YMD dates that were really monthly dates. Here you have DMY dates that are really monthly dates too.

Clyde Schechter's solution should work fine, reading in daily dates and transforming them to monthly dates, as should

gen mdate = monthly(substr(Date, 4, 7), "MY")

which is ignoring the day detail

01/

that is contained in places 1 to 3.

In other words, the procedure needed here is

1. Read friendly documentation until it's familiar. https://journals.sagepub.com/doi/pdf...6867X251341416 is one primer.

2. Look at the dates and work out what information is given in what order.

3. Work out what function calls you need.

Sorting and reversing are not at all the problem. Once you have a proper Stata date variable, Stata will be happy to give what you want.

[Aziz Essouaied]

Clyde Schechter Thanks for the help!

Nick Cox Thanks!
The problem is that I'm haping problems with the options of certains commands, I don't know which small changes so I go for in order to have the graph.
The best I could understand is the following:

local tickpos = ym(1985, 12) + 0.5
local ticks `tickpos'
forval y = 1985/2025 {
local labelpos = `tickpos' + 6
local labels `labels' `labelpos' "`y'"
local tickpos = ym(`y', 12) + 0.5
local ticks `ticks' `tickpos'
}

local labelpos = ym(1985, 5)
local labels `labels' `labelpos' " 2025"
line price mdate lc(red blue) yla(, ang(h)) ytitle(%, orient(horiz)) xla(`labels', noticks) xticks(`ticks', tlength(*3)) xtitle("") legend(ring(0) pos(11) col(1)) xsc(r(. `=ym(2025, 6)')) xli(`ticks', lc(gs12))

Obviousy, the code needs just some small change, since that, when I don't use the options in my lasy command "line", I kind of get the graph I'm looking for, it's just that the options of the time axis need some adjustments.

I would really appreciate it if you could help. Thanks!

[Nick Cox]

Not that much is obvious without seeing your graph and your spelling out what adjustments are needed in your view.

Here are some key details.

The graph call is illegal without a comma before the options.

The detail for May 2025 isn't needed.

You don't have enough space to show every year as 4 digits (in my opinion). I show every odd year with two digits.

This should work in Stata 13.1 but I can't test that.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 month float price
"01/09/2025" 3697.45
"01/08/2025" 3373.96
"01/07/2025" 3285.78
"01/06/2025" 3266.41
"01/05/2025" 3289.55
"01/04/2025" 3288.78
"01/03/2025" 3123.08
"01/02/2025" 2858.05
"01/01/2025"  2798.2
"01/12/2024"  2624.6
"01/11/2024" 2651.22
"01/10/2024"  2746.7
"01/09/2024" 2634.76
"01/08/2024" 2503.38
"01/07/2024" 2447.36
"01/06/2024" 2326.72
"01/05/2024" 2327.28
"01/04/2024" 2285.99
"01/03/2024" 2231.56
"01/02/2024"  2044.2
"01/01/2024" 2039.15
"01/12/2023" 2062.92
"01/11/2023"  2044.9
"01/10/2023" 1984.01
"01/09/2023" 1848.49
"01/08/2023" 1939.83
"01/07/2023" 1964.34
"01/06/2023" 1919.57
"01/05/2023"  1965.2
"01/04/2023"  1990.6
"01/03/2023" 1969.55
"01/02/2023" 1826.74
"01/01/2023" 1928.12
"01/12/2022" 1824.32
"01/11/2022" 1768.61
"01/10/2022" 1632.38
"01/09/2022" 1660.88
"01/08/2022" 1708.58
"01/07/2022" 1766.43
"01/06/2022" 1817.45
"01/05/2022" 1847.26
"01/04/2022"  1911.7
"01/03/2022" 1953.04
"01/02/2022"  1900.7
"01/01/2022" 1796.12
"01/12/2021"  1828.6
"01/11/2021" 1775.92
"01/10/2021"  1783.9
"01/09/2021" 1756.66
"01/08/2021"  1815.8
"01/07/2021" 1814.12
"01/06/2021"  1771.6
"01/05/2021" 1904.74
"01/04/2021"  1767.7
"01/03/2021" 1715.24
"01/02/2021"  1728.8
"01/01/2021"  1849.7
"01/12/2020"  1895.1
"01/11/2020" 1779.86
"01/10/2020"  1879.9
"01/09/2020"  1893.9
"01/08/2020"  1970.5
"01/07/2020" 1971.68
"01/06/2020"  1770.7
"01/05/2020" 1725.65
"01/04/2020" 1716.75
"01/03/2020" 1604.65
"01/02/2020" 1626.35
"01/01/2020" 1580.85
"01/12/2019"    1523
"01/11/2019" 1456.35
"01/10/2019"  1506.4
"01/09/2019"  1487.6
"01/08/2019" 1526.55
"01/07/2019" 1430.55
"01/06/2019"  1413.2
"01/05/2019"    1296
"01/04/2019" 1285.15
"01/03/2019" 1291.15
"01/02/2019" 1325.45
"01/01/2019"  1322.5
"01/12/2018" 1281.65
"01/11/2018" 1220.45
"01/10/2018"  1217.7
"01/09/2018"  1183.5
"01/08/2018" 1206.85
"01/07/2018"  1219.2
"01/06/2018" 1250.55
"01/05/2018"  1303.5
"01/04/2018" 1316.25
"01/03/2018"  1323.9
"01/02/2018"  1320.3
"01/01/2018" 1343.35
"01/12/2017"  1296.5
"01/11/2017" 1282.15
"01/10/2017"  1270.5
"01/09/2017"  1284.8
"01/08/2017"  1322.2
"01/07/2017"  1273.4
"01/06/2017"  1242.3
end

gen mdate = monthly(substr(month, 4, 7), "MY")

local tickpos = ym(1984, 12) + 0.5
local ticks `tickpos'
forval y = 1985/2025 {
local labelpos = ym(`y', 6) + 0.5
local mid : di %02.0f mod(`y', 100)
if mod(`y', 2) == 1 local labels `labels' `labelpos' "`mid'"
local tickpos = ym(`y', 12) + 0.5
local ticks `ticks' `tickpos'
}

line price mdate, lc(blue) yla(, ang(h)) ytitle(Price, orient(horiz)) xla(`labels', noticks) ///  
xtitle("") xline(`ticks', lp(solid) lc(gs12) lw(vthin)) legend(ring(0) pos(11) col(1))

[Aziz Essouaied]

Nick Cox Thanks for the help! The code work.

Sorry for this question, but it came on my mind just now when having a closer look to my data. The data is about the evolution of gold price that I'm studying, and you can see, it's a long time horizon, so, comparisons between periods would kind of lose relevance because the underlying trend is itself a time-indexed parameter. So, should one examine deviations from the trend for a better more relevant interpretation? I just thought of this right now... I hope this question is interesting...

[Nick Cox]

I don't have a good answer to #6, for all sorts of reasons. I am not an economist. I don't know what you understand by trend and how would you would calculate it precisely.

Others may want to weigh in more helpfully.

[Aziz Essouaied]

Nick Cox Thanks for your response By what I've said in #6, I do mean the deviation from the trend (or sometimes it is even called "detrended value), it's calculated by subtracting the estimated value from the observed in order to.highlight the short-term, cyclical, or random fluctuations around the long-term underlying movement.
I mean, perhaps I need to study the concept even more, but I guess it could be calculated by either moving averages or by linear or polynomial regression.