How can I change the digits into year and date (%9s --> %td)

[Mengqi Liu]

Dear All,

Thanks for helping me here.
I have trouble to convert the date (format %9s) into %td.
The story is like this. I downloaded the daily exchange rate as csv file and imported into stata.
I have no idea why the columns are merged into one column.
By using
split v1, p(";") I was able to divide the column into four parts. (please refer the first picture)

Please help me out here, how can I convert the digits into readable dates in stata.

I was using
format datadate %td

and it just warned me that " a string format is required for string variables."


Sincerely,
Mengqi

[Clyde Schechter]

Please read the Forum FAQ for excellent advice about how to effectively show the information needed to resolve your problem. Pictures, especially screenshots of data, are particularly discouraged because they are often unreadable, and even when readable, cannot be imported into Stata if somebody needs to try out some code. In this case, your pictures, at least on my computer, are completely unreadable (they are blank).

Please post back using the -dataex- command to show what you have. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

If you do that, I am confident that a timely and helpful response will be forthcoming.

[Mengqi Liu]

Please read the Forum FAQ for excellent advice about how to effectively show the information needed to resolve your problem. Pictures, especially screenshots of data, are particularly discouraged because they are often unreadable, and even when readable, cannot be imported into Stata if somebody needs to try out some code. In this case, your pictures, at least on my computer, are completely unreadable (they are blank).

Please post back using the -dataex- command to show what you have. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

If you do that, I am confident that a timely and helpful response will be forthcoming.

Hello Mr. Schechter,
sorry for the inconvenient. I followed your instruction and get the following results.

Do you mean this?
Thanks for your help.
Sincerely,
Mengqi

* Example generated by -dataex-. To install: ssc install dataex
clear
input str15 exrt strL datadate str3 curccd
"7.94703839"     "8067" "AED"
"3.89647593"     "8067" "ANG"
"21701.38888889" "8067" "ARA"
"2.16475078"     "8067" "BSD"
"0.9623431"      "8067" "CYP"
"1"              "8067" "GBP"
"128.64400217"   "8067" "GRD"
"1400.10920852"  "8067" "IDR"
"36.24891842"    "8067" "ILS"
"174.07773615"   "8067" "IRR"
"0.61481288"     "8067" "KWD"
"1.54540397"     "8067" "KYD"
"10.32188915"    "8067" "LBP"
"57.61729441"    "8067" "MXP"
"17.91322725"    "8067" "PHP"
"22.50941231"    "8067" "PKR"
"7.3929315"      "8067" "SAR"
"25.2195933"     "8067" "UYU"
"635.92195966"   "8067" "XOF"
"2.3999808"      "8067" "ZAL"
"0.00029104"     "8067" "BRE"
"432.7523001"    "8067" "JPY"
"1.69800128"     "8067" "AUD"
"30.57861985"    "8067" "ATS"
"74.29162551"    "8067" "BEF"
"1.8605"         "8067" "BMD"
"2.2355768"      "8067" "CAD"
"3.4884375"      "8067" "CHF"
"72.30107656"    "8067" "CLP"
"109.5110766"    "8067" "COP"
"4.372175"       "8067" "DEM"
"14.32119875"    "8067" "DKK"
"1.53919165"     "8067" "EGP"
"184.9392815"    "8067" "ESP"
"8.27010855"     "8067" "FIM"
"11.1131386"     "8067" "FRF"
"10.9006695"     "8067" "HKD"
"1.2454187"      "8067" "IEP"
"17.097995"      "8067" "INR"
"2334.053065"    "8067" "ITL"
"1318.257275"    "8067" "KRW"
"37.76815"       "8067" "LKR"
"82.60564186"    "8067" "LUF"
"4.245661"       "8067" "MYR"
"4.7874386"      "8067" "NLG"
"11.06160275"    "8067" "NOK"
"2.325625"       "8067" "NZD"
"1.29472195"     "8067" "PGK"
"125.8823603"    "8067" "PTE"
"10.6476415"     "8067" "SEK"
"3.8828635"      "8067" "SGD"
"42.88452501"    "8067" "THB"
"1.8605"         "8067" "USD"
"9.29251284"     "8067" "VEB"
"1.819569"       "8067" "ZAR"
"7.94703839"     "8068" "AED"
"3.89647593"     "8068" "ANG"
"21701.38888889" "8068" "ARA"
"2.16475078"     "8068" "BSD"
"0.9623431"      "8068" "CYP"
"1"              "8068" "GBP"
"128.64400217"   "8068" "GRD"
"1400.10920852"  "8068" "IDR"
"36.24891842"    "8068" "ILS"
"174.07773615"   "8068" "IRR"
"0.61481288"     "8068" "KWD"
"1.54540397"     "8068" "KYD"
"10.32188915"    "8068" "LBP"
"57.61729441"    "8068" "MXP"
"17.91322725"    "8068" "PHP"
"22.50941231"    "8068" "PKR"
"7.3929315"      "8068" "SAR"
"25.2195933"     "8068" "UYU"
"635.92195966"   "8068" "XOF"
"2.3999808"      "8068" "ZAL"
"0.00029104"     "8068" "BRE"
"434.4281247"    "8068" "JPY"
"1.70137937"     "8068" "AUD"
"30.70852748"    "8068" "ATS"
"74.49999996"    "8068" "BEF"
"1.8625"         "8068" "BMD"
"2.24747875"     "8068" "CAD"
"3.5070875"      "8068" "CHF"
"72.79096996"    "8068" "CLP"
"111.2003762"    "8068" "COP"
"4.38153125"     "8068" "DEM"
"14.28071874"    "8068" "DKK"
"1.5492275"      "8068" "EGP"
"185.2088624"    "8068" "ESP"
"8.3205325"      "8068" "FIM"
"11.10888124"    "8068" "FRF"
"10.91424999"    "8068" "HKD"
"1.241915"       "8068" "IEP"
"17.09774999"    "8068" "INR"
"2339.337249"    "8068" "ITL"
"1320.233124"    "8068" "KRW"
"37.80874998"    "8068" "LKR"
"82.95537745"    "8068" "LUF"
"4.2763"         "8068" "MYR"
"4.7974275"      "8068" "NLG"
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 1833751 observations
Use the count() option to list more

[Wouter Wakker]

You can only use date formats like %td with numeric variables, but your date variable is a string so you have to destring it first.

destring datadate, gen(date)
format date %td

See also help datetime for info on time and date conversions.

[Clyde Schechter]

I'll just add that you will probably want to -destring- the exrt variable as well: you won't be able to calculate anything with it as a string.

I'm not certain what to make of the datadate variable. If you follow the advice in #4, you end up with 01feb1982 and 02feb1982. Are those the actual dates corresponding to the events your data describes? I fear that the "8067" and "8068" represent some other kind of encoding of date information, but I cannot discern what it might be.

[Mengqi Liu]

You can only use date formats like %td with numeric variables, but your date variable is a string so you have to destring it first.

destring datadate, gen(date)
format date %td

See also help datetime for info on time and date conversions.

Hello Mr. Wakker,
thanks for your help! It worked perfectly!
Have a nice day.
Sincerely,
Mengqi

[Mengqi Liu]

I'll just add that you will probably want to -destring- the exrt variable as well: you won't be able to calculate anything with it as a string.

I'm not certain what to make of the datadate variable. If you follow the advice in #4, you end up with 01feb1982 and 02feb1982. Are those the actual dates corresponding to the events your data describes? I fear that the "8067" and "8068" represent some other kind of encoding of date information, but I cannot discern what it might be.

Hello Mr. Schechter,
yes, those dates correspond to my historical exchange rate, and are exactly want I needed.
I followed the instruction of No. 4. And it worked.
Thanks for your help as well
Have a nice day.
Sincerely,
Mengqi