Destring a time variable in order to apply the "tsset" command

[Aziz Essouaied]

Hello Stata people;

I have this data at hand currently:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date float(eurozone usa)
"31/01/2010"  .8 1.6
"28/02/2010"  .8 1.3
"31/03/2010" 1.1 1.1
"30/04/2010"  .9  .9
"31/05/2010"  .9  .9
"30/06/2010"   1  .9
"31/07/2010"   1  .9
"31/08/2010"   1  .9
"30/09/2010" 1.2  .8
"31/10/2010" 1.1  .6
"30/11/2010" 1.1  .8
"31/12/2010" 1.1  .8
"31/01/2011" 1.1   1
"28/02/2011"   1 1.1
"31/03/2011" 1.3 1.2
"30/04/2011" 1.6 1.3
"31/05/2011" 1.5 1.5
"30/06/2011" 1.5 1.6
"31/07/2011" 1.2 1.8
"31/08/2011" 1.2   2
"30/09/2011" 1.6   2
"31/10/2011" 1.6 2.1
"30/11/2011" 1.6 2.2
"31/12/2011" 1.6 2.2
"31/01/2012" 1.5 2.3
"29/02/2012" 1.5 2.2
"31/03/2012" 1.6 2.3
"30/04/2012" 1.6 2.3
"31/05/2012" 1.6 2.3
"30/06/2012" 1.6 2.2
"31/07/2012" 1.7 2.1
"31/08/2012" 1.5 1.9
"30/09/2012" 1.5   2
"31/10/2012" 1.5   2
"30/11/2012" 1.4 1.9
"31/12/2012" 1.5 1.9
"31/01/2013" 1.3 1.9
"28/02/2013" 1.3   2
"31/03/2013" 1.5 1.9
"30/04/2013"   1 1.7
"31/05/2013" 1.2 1.7
"30/06/2013" 1.2 1.6
"31/07/2013" 1.1 1.7
"31/08/2013" 1.1 1.8
"30/09/2013"   1 1.7
"31/10/2013"  .8 1.7
"30/11/2013"   1 1.7
"31/12/2013"  .7 1.7
"31/01/2014"  .8 1.6
"28/02/2014"   1 1.6
"31/03/2014"  .7 1.7
"30/04/2014"   1 1.8
"31/05/2014"  .7   2
"30/06/2014"  .8 1.9
"31/07/2014"  .8 1.9
"31/08/2014"  .9 1.7
"30/09/2014"  .8 1.7
"31/10/2014"  .7 1.8
"30/11/2014"  .7 1.7
"31/12/2014"  .7 1.6
"31/01/2015"  .6 1.6
"28/02/2015"  .7 1.7
"31/03/2015"  .6 1.8
"30/04/2015"  .9 1.8
"31/05/2015" 1.3 1.7
"30/06/2015" 1.2 1.8
"31/07/2015" 1.4 1.8
"31/08/2015" 1.4 1.8
"30/09/2015" 1.3 1.9
"31/10/2015" 1.5 1.9
"30/11/2015"  .9   2
"31/12/2015"  .9 2.1
"31/01/2016"   1 2.2
"29/02/2016"  .9 2.3
"31/03/2016"   1 2.2
"30/04/2016"  .7 2.1
"31/05/2016"  .8 2.2
"30/06/2016"  .8 2.2
"31/07/2016"  .9 2.2
"31/08/2016"  .8 2.3
"30/09/2016"  .8 2.2
"31/10/2016"  .7 2.1
"30/11/2016"  .8 2.1
"31/12/2016"  .9 2.2
"31/01/2017"  .9 2.3
"28/02/2017"  .8 2.2
"31/03/2017"  .7   2
"30/04/2017" 1.3 1.9
"31/05/2017"  .9 1.7
"30/06/2017" 1.2 1.7
"31/07/2017" 1.2 1.7
"31/08/2017" 1.2 1.7
"30/09/2017" 1.2 1.7
"31/10/2017"  .9 1.8
"30/11/2017"  .9 1.7
"31/12/2017"  .9 1.8
"31/01/2018"   1 1.8
"28/02/2018"   1 1.8
"31/03/2018" 1.1 2.1
"30/04/2018"  .7 2.1
end

What I want to do is transform the "date" variable in order to be able to apply the "tsset" command and draw a time grapgh of the other tow variables "Euro zone" and "USA".

With many thanks!

[Chen Samulsion]

Using date() function.

gen date2=date(date,"DMY")
format date2 %td
tsset date2
tsline eurozone usa

[Nick Cox]

Transform is a good term here but destringing in the sense of the destring command is not at all what you need. See for example https://journals.sagepub.com/doi/pdf...867X1801800413 for much relevant discussion.

There are two steps implied by your data example. First, what you have are daily dates easily converted from string form. But second, you clearly have monthly data indexed by daily dates and should tsset in terms of a monthly date variable. You can combine those two steps by telescoping two function calls.

Again, https://journals.sagepub.com/doi/pdf...6867X241297950 addresses your case -- although a brief summary is that setting up your dataset as monthly data solves the massive problem that would ensure from declaring a time variable that is a daily date.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 date float(eurozone usa)
"31/01/2010"  .8 1.6
"28/02/2010"  .8 1.3
"31/03/2010" 1.1 1.1
"30/04/2010"  .9  .9
"31/05/2010"  .9  .9
"30/06/2010"   1  .9
"31/07/2010"   1  .9
"31/08/2010"   1  .9
"30/09/2010" 1.2  .8
"31/10/2010" 1.1  .6
"30/11/2010" 1.1  .8
"31/12/2010" 1.1  .8
"31/01/2011" 1.1   1
"28/02/2011"   1 1.1
"31/03/2011" 1.3 1.2
"30/04/2011" 1.6 1.3
"31/05/2011" 1.5 1.5
"30/06/2011" 1.5 1.6
"31/07/2011" 1.2 1.8
"31/08/2011" 1.2   2
"30/09/2011" 1.6   2
"31/10/2011" 1.6 2.1
"30/11/2011" 1.6 2.2
"31/12/2011" 1.6 2.2
"31/01/2012" 1.5 2.3
"29/02/2012" 1.5 2.2
"31/03/2012" 1.6 2.3
"30/04/2012" 1.6 2.3
"31/05/2012" 1.6 2.3
"30/06/2012" 1.6 2.2
"31/07/2012" 1.7 2.1
"31/08/2012" 1.5 1.9
"30/09/2012" 1.5   2
"31/10/2012" 1.5   2
"30/11/2012" 1.4 1.9
"31/12/2012" 1.5 1.9
"31/01/2013" 1.3 1.9
"28/02/2013" 1.3   2
"31/03/2013" 1.5 1.9
"30/04/2013"   1 1.7
"31/05/2013" 1.2 1.7
"30/06/2013" 1.2 1.6
"31/07/2013" 1.1 1.7
"31/08/2013" 1.1 1.8
"30/09/2013"   1 1.7
"31/10/2013"  .8 1.7
"30/11/2013"   1 1.7
"31/12/2013"  .7 1.7
"31/01/2014"  .8 1.6
"28/02/2014"   1 1.6
"31/03/2014"  .7 1.7
"30/04/2014"   1 1.8
"31/05/2014"  .7   2
"30/06/2014"  .8 1.9
"31/07/2014"  .8 1.9
"31/08/2014"  .9 1.7
"30/09/2014"  .8 1.7
"31/10/2014"  .7 1.8
"30/11/2014"  .7 1.7
"31/12/2014"  .7 1.6
"31/01/2015"  .6 1.6
"28/02/2015"  .7 1.7
"31/03/2015"  .6 1.8
"30/04/2015"  .9 1.8
"31/05/2015" 1.3 1.7
"30/06/2015" 1.2 1.8
"31/07/2015" 1.4 1.8
"31/08/2015" 1.4 1.8
"30/09/2015" 1.3 1.9
"31/10/2015" 1.5 1.9
"30/11/2015"  .9   2
"31/12/2015"  .9 2.1
"31/01/2016"   1 2.2
"29/02/2016"  .9 2.3
"31/03/2016"   1 2.2
"30/04/2016"  .7 2.1
"31/05/2016"  .8 2.2
"30/06/2016"  .8 2.2
"31/07/2016"  .9 2.2
"31/08/2016"  .8 2.3
"30/09/2016"  .8 2.2
"31/10/2016"  .7 2.1
"30/11/2016"  .8 2.1
"31/12/2016"  .9 2.2
"31/01/2017"  .9 2.3
"28/02/2017"  .8 2.2
"31/03/2017"  .7   2
"30/04/2017" 1.3 1.9
"31/05/2017"  .9 1.7
"30/06/2017" 1.2 1.7
"31/07/2017" 1.2 1.7
"31/08/2017" 1.2 1.7
"30/09/2017" 1.2 1.7
"31/10/2017"  .9 1.8
"30/11/2017"  .9 1.7
"31/12/2017"  .9 1.8
"31/01/2018"   1 1.8
"28/02/2018"   1 1.8
"31/03/2018" 1.1 2.1
"30/04/2018"  .7 2.1
end

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

list in 1/12, sep(12)

     +---------------------------------------+
     |       date   eurozone   usa     mdate |
     |---------------------------------------|
  1. | 31/01/2010         .8   1.6    2010m1 |
  2. | 28/02/2010         .8   1.3    2010m2 |
  3. | 31/03/2010        1.1   1.1    2010m3 |
  4. | 30/04/2010         .9    .9    2010m4 |
  5. | 31/05/2010         .9    .9    2010m5 |
  6. | 30/06/2010          1    .9    2010m6 |
  7. | 31/07/2010          1    .9    2010m7 |
  8. | 31/08/2010          1    .9    2010m8 |
  9. | 30/09/2010        1.2    .8    2010m9 |
 10. | 31/10/2010        1.1    .6   2010m10 |
 11. | 30/11/2010        1.1    .8   2010m11 |
 12. | 31/12/2010        1.1    .8   2010m12 |
     +---------------------------------------+

[Nick Cox]

Sorry, but I didn't see #2 while drafting #3 and I have to say that it is bad advice, as tsset in terms of a daily date variable can only lead here into a mess.

[Chen Samulsion]

Dear Nick Cox thank you for the correction. But I wonder what's the difference between daily date and monthly date when they were took as x-var in tsline plot? The "mess" you refered to is exactly what?

gen mdate = mofd(daily(date, "DMY"))
format mdate %tm 
tsset mdate
tsline eurozone usa, name(g1)
gen date2=date(date,"DMY")
format date2 %td
tsset date2
tsline eurozone usa, name(g2)
graph combine g1 g2, row(2)

[Nick Cox]

You can get a half-decent graph. I agree on that. However, your example makes my point for me too. The graph that is possible after tsset using a daily date has date labels that are less suitable than those following a tsset using a monthly date.

The mess is what happens when you try almost anything else that depends on tsset. Your data appears to be Stata to be mostly gaps and calculations using operators and most data management or modelling just will not work as desired.

Again, this is discussed in considerable detail in the second link in #3 so I wasn't tempted or anxious to add examples or explanation at length.

[Chen Samulsion]

Thank you very much Nick. I will read the article you cited in #3.

[Nick Cox]

Please note my revisions to #6.

[Aziz Essouaied]

Nick Cox Chen Samulsion Thanks for both of your clarifications, they helped me get the desired results.

Tho, I want to take this opportunity to ask about the same thing, but only if the "date" variable was defined by the month and the year, like so:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 date float(eurozone usa)
"janv-10" .25  .11
"févr-10" .25  .13
"mars-10" .25  .16
"avr-10"  .25   .2
"mai-10"  .25   .2
"juin-10" .25  .18
"juil-10" .25  .18
"août-10" .25  .19
"sept-10" .25  .19
"oct-10"  .25  .19
"nov-10"  .25  .19
"déc-10"  .25  .18
"janv-11" .25  .17
"févr-11" .25  .16
"mars-11" .25  .14
"avr-11"   .5   .1
"mai-11"   .5  .09
"juin-11"  .5  .09
"juil-11" .75  .07
"août-11" .75   .1
"sept-11" .75  .08
"oct-11"  .75  .07
"nov-11"   .5  .08
"déc-11"  .25  .07
"janv-12" .25  .08
"févr-12" .25   .1
"mars-12" .25  .13
"avr-12"  .25  .14
"mai-12"  .25  .16
"juin-12" .25  .16
"juil-12"   0  .16
"août-12"   0  .13
"sept-12"   0  .14
"oct-12"    0  .16
"nov-12"    0  .16
"déc-12"    0  .16
"janv-13"   0  .14
"févr-13"   0  .15
"mars-13"   0  .14
"avr-13"    0  .15
"mai-13"    0  .11
"juin-13"   0  .09
"juil-13"   0  .09
"août-13"   0  .08
"sept-13"   0  .08
"oct-13"    0  .09
"nov-13"    0  .08
"déc-13"    0  .09
"janv-14"   0  .07
"févr-14"   0  .07
"mars-14"   0  .08
"avr-14"    0  .09
"mai-14"    0  .09
"juin-14" -.1   .1
"juil-14" -.1  .09
"août-14" -.1  .09
"sept-14" -.2  .09
"oct-14"  -.2  .09
"nov-14"  -.2  .09
"déc-14"  -.2  .12
"janv-15" -.2  .11
"févr-15" -.2  .11
"mars-15" -.2  .11
"avr-15"  -.2  .12
"mai-15"  -.2  .12
"juin-15" -.2  .13
"juil-15" -.2  .13
"août-15" -.2  .14
"sept-15" -.2  .14
"oct-15"  -.2  .12
"nov-15"  -.2  .12
"déc-15"  -.3  .24
"janv-16" -.3  .34
"févr-16" -.3  .38
"mars-16" -.3  .36
"avr-16"  -.4  .37
"mai-16"  -.4  .37
"juin-16" -.4  .38
"juil-16" -.4  .39
"août-16" -.4   .4
"sept-16" -.4   .4
"oct-16"  -.4   .4
"nov-16"  -.4  .41
"déc-16"  -.4  .54
"janv-17" -.4  .65
"févr-17" -.4  .66
"mars-17" -.4  .79
"avr-17"  -.4   .9
"mai-17"  -.4  .91
"juin-17" -.4 1.04
"juil-17" -.4 1.15
"août-17" -.4 1.16
"sept-17" -.4 1.15
"oct-17"  -.4 1.15
"nov-17"  -.4 1.16
"déc-17"  -.4  1.3
"janv-18" -.4 1.41
"févr-18" -.4 1.42
"mars-18" -.4 1.51
"avr-18"  -.4 1.69
end

Is it possible to transform that variable in a way to do the same thing and draw a monthly time-graph for the other two variables?

Thanks for both of you!

[Hemanshu Kumar]

Perhaps you can generate a numeric month variable like so:

local months_fr janv févr mars avr mai juin juil août sept oct nov déc
local months_en jan feb mar apr may jun jul aug sep oct nov dec

gen date_en = date

forval i = 1/12 {
    local mm_fr: word `i' of `months_fr'
    local mm_en: word `i' of `months_en'
    replace date_en = usubinstr(date_en, "`mm_fr'", "`mm_en'", 1)
}

gen mdate = monthly(date_en,"M20Y")
format %tm mdate

[Aziz Essouaied]

Hemanshu Kumar Thanks for the answer.

Yet, when I use that code, I get an error message about the usubinstr function telling me it's unknown (the error is r(133)), even when I've used the ssc install command, Stata didn't find that command, perhaps it is because I'm using an old version of Stata (I'm using the 13.1 version).

Thanks!

[Hemanshu Kumar]

Ah. When using older versions of Stata, please specify that in your post, as suggested in the FAQ #11.

Unicode support was introduced in Stata 14. Perhaps someone else can tell you how to manage this in Stata 13.1. You might want to try with the subinstr() function instead to see if that works? Since Stata appears to able to read your strings, perhaps they are not stored as Unicode to begin with.

[Nick Cox]

FAQ Advice #11 -- which I think has been flagged to you before.

11. What should I say about the version of Stata I use?

The current version of Stata is 18. Please specify if you are using an earlier version; otherwise, the answer to your question may refer to commands or features unavailable to you. Moreover, as bug fixes and new features are issued frequently by StataCorp, make sure that you update your Stata before posting a query, as your problem may already have been solved.

As a detail, functions can only be defined within Stata code before compilation. Functions are not definable by user code and so will never be extras on SSC. By functions, I mean Stata functions strict sense, not commands, not egen functions, not Mata functions.

Most crucially, you already asked this question and it's been answered, with only a small detail of difference that you don't have stops (periods) in these dates.

https://www.statalist.org/forums/for...-type-variable

You should be able to adapt the code there to your present problem.

[Clyde Schechter]

I have to say that if you are using the pre-Unicode version 13.1, I don't understand how your Stata is even able to display things like "déc" or "août."

Be that as it may, here is a different solution to your problem. It relies on the data already being both consecutive and in chronological order. This is, at least, the case in your example data. If it is not true in the full data set then this approach will not work--in fact, it will give incorrect results. The code also specifically assumes that, as in your example data, the very first observation has the date as "jan-10."

gen mdate = tm(2009m12) + _n
format mdate %tm