Reshape large panel data set with multiple variables and daily data

[Nils Edgren]

Hi members,

I have a large data set with information about 166 bonds, their characteristics, followed by daily yield data over a five year period (although most of these values are missing). The data is formatted as a wide format panel but it seems as if I need to reshape the data into a long format to use in Stata.

The data looks as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str13 RIC str59 IssuerName str6 IssuerTicker str8 Rating str6 Coupon byte date31122013 double date31122018
"NL180873945="  "ABN AMRO BANK NV"                                            "ABNAM"  "P-1"      "SR"     .     .05308791208791219
"ES164276376="  "ADIF ALTA VELOCIDAD"                                         "MNDEFA" "WD"       "SR"     .   -.010077997671711247
"00828EDF3="    "AFRICAN DEVELOPMENT BANK"                                    "AFDB"   "Aaa"      "SR"     .    -.02932627737226312
"015271AM1="    "ALEXANDRIA REAL ESTATE EQUITIES INC"                         "ARE"    "BBB+"     "SR"     .    -.07321824817518285
"037833BU3="    "APPLE INC"                                                   "AAPL"   "P-1"      "SR"     .     .03468363636363625
"037833CX6="    "APPLE INC"                                                   "AAPL"   "P-1"      "SR"     .    .007064516129032139
"045167DQ3="    "ASIAN DEVELOPMENT BANK"                                      "ADB"    "AAA"      "SR"     .   -.009346153846153893
"XS179621193="  "ASIAN DEVELOPMENT BANK"                                      "ADB"    "AAA"      "SR"     . -.00039999999999995595
"XS181439009="  "ASIAN DEVELOPMENT BANK"                                      "ADB"    "AAA"      "SR"     .   .0004339622641509333
"XS161817856="  "ASIAN DEVELOPMENT BANK"                                      "ADB"    "AAA"      "SR"     .                      .
"045167EB5="    "ASIAN DEVELOPMENT BANK"                                      "ADB"    "AAA"      "SR"     .   -.023430869971936552
"045167CY7="    "ASIAN DEVELOPMENT BANK"                                      "ADB"    "AAA"      "SR"     .    .026511597374179185
"045167DR1="    "ASIAN DEVELOPMENT BANK"                                      "ADB"    "AAA"      "SR"     .     .01513764258555117
"045167EC3="    "ASIAN DEVELOPMENT BANK"                                      "ADB"    "AAA"      "SR"     .    .015742857142857503
"SE0009779465=" "ATRIUM LJUNGBERG AB"                                         "ATRLJ"  "Baa2"     "SR"     .                      .
"05463CAD4="    "AXIS BANK LTD (DUBAI BRANCH)"                                "AXBKD"  "Baa2(cr)" "SR"     .     -.0135021013597032
"IN141034138="  "AXIS BANK LTD (DUBAI BRANCH)"                                "AXBKD"  "Baa2(cr)" "SR"     .   -.013702101359703178
"06051GGB9="    "BANK OF AMERICA CORP"                                        "BAC"    "Baa1"     "SR"     .    -.09311153846153886
"06051GHH5="    "BANK OF AMERICA CORP"                                        "BAC"    "Baa1"     "SR"     .    -.17016629834254138
"DEBHY0GP="     "BERLIN HYP AG"                                               "EWRSFY" "Aaa"      "MTG"    .     .02428253968253965
"DEBHY0GH="     "BERLIN HYP AG"                                               "EWRSFY" "Aaa"      "MTG"    .   -.010500000000000037
"DEBHY0GC="     "BERLIN HYP AG"                                               "EWRSFY" "Aaa"      "MTG"    .    .011591891891891859
"FR180833854="  "BNP PARIBAS SA"                                              "BNP"    "P-1"      "SRBN"   .     .24590909090909108
"FR0013067170=" "BPCE SA"                                                     "BPCSA"  "A"        "SR"     .   -.032315068493150745
"IT188639356="  "CASSA DEPOSITI E PRESTITI SPA"                               "CSDPR"  "WD"       "SR"     .     .06592613636363609
"SE0009161615=" "CASTELLUM AB"                                                "CASTZ"  "(P)Baa3"  "SR"     .                      .
"CN171117321="  "CHINA DEVELOPMENT BANK"                                      "CNDBK"  "AAA"      "SR"     .    -.17748837209302326
"CN171103959="  "CHINA DEVELOPMENT BANK"                                      "CNDBK"  "AAA"      "SR"     .     .06773565217391342
"AUCBA0322="    "COMMONWEALTH BANK OF AUSTRALIA"                              "CBA"    "P-1"      "SR"     .     .18568518518518484
"NL150243882="  "COOPERATIEVE RABOBANK UA"                                    "RABO"   "AA-"      "SR"     .    -.06003296703296705
"FR114083402="  "CREDIT AGRICOLE CORPORATE AND INVESTMENT BANK SA"            "CAGRAB" "BBB+"     "SR"     .      .1629516616314195
"FR114083330="  "CREDIT AGRICOLE CORPORATE AND INVESTMENT BANK SA"            "CAGRAB" "BBB+"     "SR"     .     -.2205882352941182
"FR136722948="  "CREDIT AGRICOLE CORPORATE AND INVESTMENT BANK SA"            "CAGRAB" "BBB+"     "SR"     .    -.03142187499999993
"JP150565553="  "DEVELOPMENT BANK OF JAPAN INC"                               "DBJPN"  "AAA"      "SR"     .   -.044623430962343225
"JP169765146="  "DEVELOPMENT BANK OF JAPAN INC"                               "DBJPN"  "AAA"      "SR"     .    -.04254054054054057
"25389JAL0="    "DIGITAL REALTY TRUST LP"                                     "DLRLT"  "Baa2"     "SR"     .    -.01493203539823007
"PT189362102="  "EDP FINANCE BV"                                              "EDPPF"  "A-3"      "SR"     .    -.04293548387096813
"NO1080686="    "EIDSIVA ENERGI AS"                                           "EIDEN"  "NULL"     "SR"     .                      .
"IT155014920="  "ENEL FINANCE INTERNATIONAL NV"                               "ENEIL"  "A-"       "SR"     .    -.12439784946236565
"FR0011911239=" "ENGIE SA"                                                    "ENGIE"  "BBB+"     "SR"     .                      .
"29874QCW2="    "EUROPEAN BANK FOR RECONSTRUCTION AND DEVELOPMENT"            "EBRD"   "Aaa"      "SR"     .     .08930545454545458
"29874QDG6="    "EUROPEAN BANK FOR RECONSTRUCTION AND DEVELOPMENT"            "EBRD"   "Aaa"      "SR"     .      .0916947368421055
"29878TCS1="    "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     .    .020111363636363677
"XS131433620="  "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     .    .020511363636363633
"XS134620218="  "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     .    -.14971345595353336
"XS155129301="  "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     . -.00035237173281710277
"XS128083499="  "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     .                 -.0305
"XS175742808="  "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     .   .0031534246575343206
"XS181185210="  "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     .                      0
"XS187883369="  "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     .    -.08431147540983608
"XS110771827="  "EUROPEAN INVESTMENT BANK"                                    "EIB"    "Aaa"      "SR"     .   -.038680327868852504
"30216BGU0="    "EXPORT DEVELOPMENT CANADA"                                   "EDC"    "AAA"      "SR"     .     .07675723270440216
"IN120986422="  "EXPORT IMPORT BANK OF INDIA"                                 "EXIMB"  "BBB+"     "SR"     .      .3240222222222222
"302154BZ1="    "EXPORT-IMPORT BANK OF KOREA"                                 "KEXIM"  "AA-"      "SR"     .     .10031914893617033
"KR165532295="  "EXPORT-IMPORT BANK OF KOREA"                                 "KEXIM"  "AA-"      "SR"     .     .16859999999999964
"KR179009986="  "EXPORT-IMPORT BANK OF KOREA"                                 "KEXIM"  "AA-"      "SR"     .     .13540188679245269
"IT173240031="  "FERROVIE DELLO STATO ITALIANE SPA"                           "FRSTO"  "WD"       "SR"     .      .5752306976744184
"CH38787903="   "GENEVA, CANTON OF"                                           "GNEVA"  "AA-"      "SR"     .                      .
"CH38787904="   "GENEVA, CANTON OF"                                           "GNEVA"  "AA-"      "SR"     .                      .
"373334KE0="    "GEORGIA POWER CO"                                            "SOGEP"  "VMIG 2"   "SR"     .     .01705000000000023
"SE125384781="  "GOTHENBURG, MUNICIPALITY OF"                                 "GTBRG"  "P-1"      "SR"     .     .02550000000000001
"SE143308286="  "GOTHENBURG, MUNICIPALITY OF"                                 "GTBRG"  "P-1"      "SR"     .  -.0036985645933014233
"SE162777831="  "GOTHENBURG, MUNICIPALITY OF"                                 "GTBRG"  "P-1"      "SR"     .     .02189999999999992
"HK171984696="  "HKCG FINANCE LTD"                                            "HKCNGF" "A+"       "SR"     .    -.01507903780068709
"KR137942992="  "HYUNDAI CAPITAL SERVICES INC"                                "HYMTRD" "BBB+"     "SR"     .     .42423520309477736
"44920UAG3="    "HYUNDAI CAPITAL SERVICES INC"                                "HYMTRD" "BBB+"     "SR"     .     .41953520309477765
"ES105705506="  "IBERDROLA INTERNATIONAL BV"                                  "IBERD"  "P-2"      "SR"     .    -.08899999999999997
"FR0013281755=" "ICADE SA"                                                    "ICAD"   "BBB+"     "SR"     .    -.12478980891719749
"CN183098527="  "INDUSTRIAL AND COMMERCIAL BANK OF CHINA LTD (LONDON BRANCH)" "ICBATF" "P-1"      "SR"     .     .09388888888888891
"NL132421773="  "ING BANK NV"                                                 "INGBK"  "F1+"      "SR"     .     .06471480144404333
"DE170272927="  "INNOGY FINANCE BV"                                           "IGYIG"  "BBB"      "SR"     .   -.053559585492227946
"XS151726810="  "INTERNATIONAL BANK FOR RECONSTRUCTION AND DEVELOPMENT"       "IBRD"   "Aaa"      "SR"     .     1.2643842105263159
"45905UX33="    "INTERNATIONAL BANK FOR RECONSTRUCTION AND DEVELOPMENT"       "IBRD"   "Aaa"      "SR"     .      .3536029411764696
"XS185633802="  "INTERNATIONAL BANK FOR RECONSTRUCTION AND DEVELOPMENT"       "IBRD"   "Aaa"      "SR"     .    .052601351351351355
"45905URL0="    "INTERNATIONAL BANK FOR RECONSTRUCTION AND DEVELOPMENT"       "IBRD"   "Aaa"      "SR"     .    .015238888888888802
"45905U6L3="    "INTERNATIONAL BANK FOR RECONSTRUCTION AND DEVELOPMENT"       "IBRD"   "Aaa"      "SR"     .    -.41844842105263114
"AUIBR1125="    "INTERNATIONAL BANK FOR RECONSTRUCTION AND DEVELOPMENT"       "IBRD"   "Aaa"      "SR"     .   .0029792531120329535
"SE0009921950=" "VOLVOFINANS BANK AB"                                         "VOLBK"  "P-1"      "SR"     .                      .
"95709TAN0="    "WESTAR ENERGY INC"                                           "EVRGW"  "NR"       "1STMTG" .     -.0958436213991769
"SE0009357338=" "VASTERAS, MUNICIPALITY OF"                                   "VAEST"  "AAA"      "SR"     .                      .
"SE0009190606=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0011425834=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0010832949=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0011062967=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0010948661=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0011062298=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0007666136=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0007666128=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0010297424=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0010832899=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0010442699=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "UN"     .                      .
"SE0006452553=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"SE0009607013=" "VASAKRONAN AB (PUBL)"                                        "VASKNV" "A3"       "SR"     .                      .
"FR121831970="  "UNIBAIL RODAMCO SE"                                          "UNBLR"  "A"        "SR"     .   -.007110236220472466
"FR103870852="  "UNIBAIL RODAMCO SE"                                          "UNBLR"  "A"        "SR"     .     .01753387096774206
"JP172063977="  "TOYOTA MOTOR CREDIT CORP"                                    "TOYOMF" "Aa3"      "SR"     .    -.04237297297297292
"89114QBT4="    "TORONTO-DOMINION BANK"                                       "TD"     "Aa3"      "SR"     .     -.0702387755102043
"NL124158109="  "TENNET HOLDING BV"                                           "TNETH"  "NR"       "SR"     .     -.0611532884515793
"NL143238466="  "TENNET HOLDING BV"                                           "TNETH"  "NR"       "SR"     .     .03902226825479027
"NL163289776="  "TENNET HOLDING BV"                                           "TNETH"  "NR"       "SR"     .    -.03450077679958563
end

The first column is the RIC code, which would be the individual identifier for each row (bond). Following that, I have 4 of the variables shown as an example of the 12 variables that the dataset actually includes. Lastly, I have included two of the dates shown as an example (as mentioned previously, I have daily data over a five year period for each of the bonds). I have formatted the date in the top row as text in excel, with the format "date"dd/mm/yyyy

The outcome I'm trying to achieve is to have the RIC code, the yield, and the 12 variables as columns with the daily yield (i.e. the observation) in rows, and then the value for each variable on each row (which is the same for every date as it simply is a characteristic of the bond itself, so it doesn't vary every day).

i.e. like this:

RIC                   Date             Yield                  IssuerName     Ticker        Rating    Coupon
"NL180873945="    2013/12/31       0.00003141....      "ABN AMRO BANK NV"      "ABNAM"     "P-1"      "SR"
...... same RIC (i.e. same bond) with yield for all 5 years
......
... ..Next RIC/bond starting from first date

This would generate a panel with a very large number of rows, but if I understand correctly this shouldn't be a problem. How do I best go about doing this? I've tried using reshape but wasn't successful in doing so.

Many thanks,
Nils

[Nick Cox]

Thanks for the data example. This seems to work for your example. We can't comment on what you got wrong with your reshape code without seeing the code or what happened. (See comments in FAQ Advice #12 on "didn't work" reports.)

reshape long date, i(RIC) j(time) 
rename date yield 
gen date = daily(string(time, "%8.0f"), "DMY") 
format date %td 
gen year = year(date)

[Nils Edgren]

Thanks for the data example. This seems to work for your example. We can't comment on what you got wrong with your reshape code without seeing the code or what happened. (See comments in FAQ Advice #12 on "didn't work" reports.)

reshape long date, i(RIC) j(time)
rename date yield
gen date = daily(string(time, "%8.0f"), "DMY")
format date %td
gen year = year(date)

Hi Nick,

Many thanks for your quick reply. Apologies on not including a further description of my own attempts with the reshape code.

I tried using your code. When doing so Stata starts printing a lot of notes in the review window. For example, it looks like this:

. reshape long date, i(RIC) j(time)
(note: j = 1012014 1012015 1012016 1012017 1012018 1022014 1022015 1022016 1022017 1022018 1032014 1032015 1032016 1032017 1032018 104
> 2014 1042015 1042016 1042017 1042018 1052014 1052015 1052016 1052017 1052018 1062014 1062015 1062016 1062017 1062018 1072014 1072015
>  1072016 1072017 1072018 1082014 1082015 1082016 1082017 1082018 1092014 1092015 1092016 1092017 1092018 1102014 1102015 1102016 110
> 2017 1102018 1112014 1112015 1112016 1112017 1112018 1122014 1122015 1122016 1122017 1122018 2012014 2012015 2012016 2012017 2012018
>  2022014 2022015 2022016 2022017 2022018 2032014 2032015 2032016 2032017 2032018 2042014 2042015 2042016 2042017 2042018 2052014 205
> 2015 2052016 2052017 2052018 2062014 2062015 2062016 2062017 2062018 2072014 2072015 2072016 2072017 2072018 2082014 2082015 2082016
>  2082017 2082018 2092014 2092015 2092016 2092017 2092018 2102014 2102015 2102016 2102017 2102018 2112014 2112015 2112016 2112017 211
> 2018 2122014 2122015 2122016 2122017 2122018 3012014 3012015 3012016 3012017 3012018 3022014 3022015 3022016 3022017 3022018 3032014
>  3032015 3032016 3032017 3032018 3042014 3042015 3042016 3042017 3042018 3052014 3052015 3052016 3052017 3052018 3062014 3062015 306
> 2016 3062017 3062018 3072014 3072015 3072016 3072017 3072018 3082014 3082015 3082016 3082017 3082018 3092014 3092015 3092016 3092017
>  3092018 3102014 3102015 3102016 3102017 3102018 3112014 3112015 3112016 3112017 3112018 3122014 3122015 3122016 3122017 3122018 401
> 2014 4012015 4012016 4012017 4012018 4022014 4022015 4022016 4022017 4022018 4032014 4032015 4032016 4032017 4032018 4042014 4042015
>  4042016 4042017 4042018 4052014 4052015 4052016 4052017 4052018 4062014 4062015 4062016 4062017 4062018 4072014 4072015 4072016 407
> 2017 4072018 4082014 4082015 4082016 4082017 4082018 4092014 4092015 4092016 4092017 4092018 4102014 4102015 4102016 4102017 4102018
>  4112014 4112015 4112016 4112017 4112018 4122014 4122015 4122016 4122017 4122018 5012014 5012015 5012016 5012017 5012018 5022014 502
> 2015 5022016 5022017 5022018 5032014 5032015 5032016 5032017 5032018 5042014 5042015 5042016 5042017 5042018 5052014 5052015 5052016
>  5052017 5052018 5062014 5062015 5062016 5062017 5062018 5072014 5072015 5072016 5072017 5072018 5082014 5082015 5082016 5082017 508
> 2018 5092014 5092015 5092016 5092017 5092018 5102014 5102015 5102016 5102017 5102018 5112014 5112015 5112016 5112017 5112018 5122014
>  5122015 5122016 5122017 5122018 6012014 6012015 6012016 6012017 6012018 6022014 6022015 6022016 6022017 6022018 6032014 6032015 603
> 2016 6032017 6032018 6042014 6042015 6042016 6042017 6042018 6052014 6052015 6052016 6052017 6052018 6062014 6062015 6062016 6062017
>  6062018 6072014 6072015 6072016 6072017 6072018 6082014 6082015 6082016 6082017 6082018 6092014 6092015 6092016 6092017 6092018 610
> 2014 6102015 6102016 6102017 6102018 6112014 6112015 6112016 6112017 6112018 6122014 6122015 6122016 6122017 6122018 7012014 7012015
>  7012016 7012017 7012018 7022014 7022015 7022016 7022017 7022018 7032014 7032015 7032016 7032017 7032018 7042014 7042015 7042016 704
> 2017 7042018 7052014 7052015 7052016 7052017 7052018 7062014 7062015 7062016 7062017 7062018 7072014 7072015 7072016 7072017 7072018
>  7082014 7082015 7082016 7082017 7082018 7092014 7092015 7092016 7092017 7092018 7102014 7102015 7102016 7102017 7102018 7112014 711
> 2015 7112016 7112017 7112018 7122014 7122015 7122016 7122017 7122018 8012014 8012015 8012016 8012017 8012018 8022014 8022015 8022016
>  8022017 8022018 8032014 8032015 8032016 8032017 8032018 8042014 8042015 8042016 8042017 8042018 8052014 8052015 8052016 8052017 805
> 2018 8062014 8062015 8062016 8062017 8062018 8072014 8072015 8072016 8072017 8072018 8082014 8082015 8082016 8082017 8082018 8092014
>  8092015 8092016 8092017 8092018 8102014 8102015 8102016 8102017 8102018 8112014 8112015 8112016 8112017 8112018 8122014 8122015 812
> 2016 8122017 8122018 9012014 9012015 9012016 9012017 9012018 9022014 9022015 9022016 9022017 9022018 9032014 9032015 9032016 9032017
>  9032018 9042014 9042015 9042016 9042017 9042018 9052014 9052015 9052016 9052017 9052018 9062014 9062015 9062016 9062017 9062018 907
> 2014 9072015 9072016 9072017 9072018 9082014 9082015 9082016 9082017 9082018 9092014 9092015 9092016 9092017 9092018 9102014 9102015
>  9102016 9102017 9102018 9112014 9112015 9112016 9112017 9112018 9122014 9122015 9122016 9122017 9122018 10012014 10012015 10012016
> 10012017 10012018 10022014 10022015 10022016 10022017 10022018 10032014 10032015 10032016 10032017 10032018 10042014 10042015 100420
> 16 10042017 10042018 10052014 10052015 10052016 10052017 10052018 10062014 10062015 10062016 10062017 10062018 10072014 10072015 100
> 72016 10072017 10072018 10082014 10082015 10082016 10082017 10082018 10092014 10092015 10092016 10092017 10092018 10102014 10102015
> 10102016 10102017 10102018 10112014 10112015 10112016 10112017 10112018 10122014 10122015 10122016 10122017 10122018 11012014 110120
> 15 11012016 11012017 11012018 11022014 11022015 11022016 11022017 11022018 11032014 11032015 11032016 11032017 11032018 11042014 110
> 42015 11042016 11042017 11042018 11052014 11052015 11052016 11052017 11052018 11062014 11062015 11062016 11062017 11062018 11072014
> 11072015 11072016 11072017 11072018 11082014 11082015 11082016 11082017 11082018 11092014 11092015 11092016 11092017 11092018 111020
> 14 11102015 11102016 11102017 11102018 11112014 11112015 11112016 11112017 11112018 11122014 11122015 11122016 11122017 11122018 120
> 12014 12012015 12012016 12012017 12012018 12022014 12022015 12022016 12022017 12022018 12032014 12032015 12032016 12032017 12032018
> 12042014 12042015 12042016 12042017 12042018 12052014 12052015 12052016 12052017 12052018 12062014 12062015 12062016 12062017 120620
> 18 12072014 12072015 12072016 12072017 12072018 12082014 12082015 12082016 12082017 12082018 12092014 12092015 12092016 12092017 120
> 92018 12102014 12102015 12102016 12102017 12102018 12112014 12112015 12112016 12112017 12112018 12122014 12122015 12122016 12122017
> 12122018 13012014 13012015 13012016 13012017 13012018 13022014 13022015 13022016 13022017 13022018 13032014 13032015 13032016 130320
> 17 13032018 13042014 13042015 13042016 13042017 13042018 13052014 13052015 13052016 13052017 13052018 13062014 13062015 13062016 130
> 62017 13062018 13072014 13072015 13072016 13072017 13072018 13082014 13082015 13082016 13082017 13082018 13092014 13092015 13092016
> 13092017 13092018 13102014 13102015 13102016 13102017 13102018 13112014 13112015 13112016 13112017 13112018 13122014 13122015 131220
> 16 13122017 13122018 14012014 14012015 14012016 14012017 14012018 14022014 14022015 14022016 14022017 14022018 14032014 14032015 140
> 32016 14032017 14032018 14042014 14042015 14042016 14042017 14042018 14052014 14052015 14052016 14052017 14052018 14062014 14062015
> 14062016 14062017 14062018 14072014 14072015 14072016 14072017 14072018 14082014 14082015 14082016 14082017 14082018 14092014 140920
> 15 14092016 14092017 14092018 14102014 14102015 14102016 14102017 14102018 14112014 14112015 14112016 14112017 14112018 14122014 141
> 22015 14122016 14122017 14122018 15012014 15012015 15012016 15012017 15012018 15022014 15022015 15022016 15022017 15022018 15032014
> 15032015 15032016 15032017 15032018 15042014 15042015 15042016 15042017 15042018 15052014 15052015 15052016 15052017 15052018 150620
> 14 15062015 15062016 15062017 15062018 15072014 15072015 15072016 15072017 15072018 15082014 15082015 15082016 15082017 15082018 150
> 92014 15092015 15092016 15092017 15092018 15102014 15102015 15102016 15102017 15102018 15112014 15112015 15112016 15112017 15112018
> 15122014 15122015 15122016 15122017 15122018 16012014 16012015 16012016 16012017 16012018 16022014 16022015 16022016 16022017 160220
> 18 16032014 16032015 16032016 16032017 16032018 16042014 16042015 16042016 16042017 16042018 16052014 16052015 16052016 16052017 160
> 52018 16062014 16062015 16062016 16062017 16062018 16072014 16072015 16072016 16072017 16072018 16082014 16082015 16082016 16082017
> 16082018 16092014 16092015 16092016 16092017 16092018 16102014 16102015 16102016 16102017 16102018 16112014 16112015 16112016 161120
> 17 16112018 16122014 16122015 16122016 16122017 16122018 17012014 17012016 17012018 17022014 17022016 17022018 17032014 17032016 170
> 32018 17042014 17042016 17042018 17052014 17052016 17052018 17062014 17062016 17062018 17072014 17072016 17072018 17082014 17082016
> 17082018 17092014 17092016 17092018 17102014 17102016 17102018 17112014 17112016 17112018 17122014 17122016 17122018 18012014 180120
> 16 18012018 18022014 18022016 18022018 18032014 18032016 18032018 18042014 18042016 18042018 18052014 18052016 18052018 18062014 180
> 62016 18062018 18072014 18072016 18072018 18082014 18082016 18082018 18092014 18092016 18092018 18102014 18102016 18102018 18112014
> 18112016 18112018 18122014 18122016 18122018 19012014 19012016 19012018 19022014 19022016 19022018 19032014 19032016 19032018 190420
> 14 19042016 19042018 19052014 19052016 19052018 19062014 19062016 19062018 19072014 19072016 19072018 19082014 19082016 19082018 190
> 92014 19092016 19092018 19102014 19102016 19102018 19112014 19112016 19112018 19122014 19122016 19122018 20012014 20012016 20012018
> 20022014 20022016 20022018 20032014 20032016 20032018 20042014 20042016 20042018 20052014 20052016 20052018 20062014 20062016 200620
> 18 20072014 20072016 20072018 20082014 20082016 20082018 20092014 20092016 20092018 20102014 20102016 20102018 20112014 20112016 201
> 12018 20122014 20122016 20122018 21012014 21012016 21012018 21022014 21022016 21022018 21032014 21032016 21032018 21042014 21042016
> 21042018 21052014 21052016 21052018 21062014 21062016 21062018 21072014 21072016 21072018 21082014 21082016 21082018 21092014 210920
> 16 21092018 21102014 21102016 21102018 21112014 21112016 21112018 21122014 21122016 21122018 22012014 22012016 22012018 22022014 220
> 22016 22022018 22032014 22032016 22032018 22042014 22042016 22042018 22052014 22052016 22052018 22062014 22062016 22062018 22072014
> 22072016 22072018 22082014 22082016 22082018 22092014 22092016 22092018 22102014 22102016 22102018 22112014 22112016 22112018 221220
> 14 22122016 22122018 23012014 23012016 23012018 23022014 23022016 23022018 23032014 23032016 23032018 23042014 23042016 23042018 230
> 52014 23052016 23052018 23062014 23062016 23062018 23072014 23072016 23072018 23082014 23082016 23082018 23092014 23092016 23092018
> 23102014 23102016 23102018 23112014 23112016 23112018 23122014 23122016 23122018 24012014 24012016 24012018 24022014 24022016 240220
> 18 24032014 24032016 24032018 24042014 24042016 24042018 24052014 24052016 24052018 24062014 24062016 24062018 24072014 24072016 240
> 72018 24082014 24082016 24082018 24092014 24092016 24092018 24102014 24102016 24102018 24112014 24112016 24112018 24122014 24122016
> 24122018 25012014 25012016 25012018 25022014 25022016 25022018 25032014 25032016 25032018 25042014 25042016 25042018 25052014 250520
> 16 25052018 25062014 25062016 25062018 25072014 25072016 25072018 25082014 25082016 25082018 25092014 25092016 25092018 25102014 251
> 02016 25102018 25112014 25112016 25112018 25122014 25122016 25122018 26012014 26012016 26012018 26022014 26022016 26022018 26032014
> 26032016 26032018 26042014 26042016 26042018 26052014 26052016 26052018 26062014 26062016 26062018 26072014 26072016 26072018 260820
> 14 26082016 26082018 26092014 26092016 26092018 26102014 26102016 26102018 26112014 26112016 26112018 26122014 26122016 26122018 270
> 12014 27012016 27012018 27022014 27022016 27022018 27032014 27032016 27032018 27042014 27042016 27042018 27052014 27052016 27052018
> 27062014 27062016 27062018 27072014 27072016 27072018 27082014 27082016 27082018 27092014 27092016 27092018 27102014 27102016 271020
> 18 27112014 27112016 27112018 27122014 27122016 27122018 28012014 28012016 28012018 28022014 28022016 28022018 28032014 28032016 280
> 32018 28042014 28042016 28042018 28052014 28052016 28052018 28062014 28062016 28062018 28072014 28072016 28072018 28082014 28082016
> 28082018 28092014 28092016 28092018 28102014 28102016 28102018 28112014 28112016 28112018 28122014 28122016 28122018 29012014 290120
> 16 29012018 29022016 29032014 29032016 29032018 29042014 29042016 29042018 29052014 29052016 29052018 29062014 29062016 29062018 290
> 72014 29072016 29072018 29082014 29082016 29082018 29092014 29092016 29092018 29102014 29102016 29102018 29112014 29112016 29112018
> 29122014 29122016 29122018 30012014 30012016 30012018 30032014 30032016 30032018 30042014 30042016 30042018 30052014 30052016 300520
> 18 30062014 30062016 30062018 30072014 30072016 30072018 30082014 30082016 30082018 30092014 30092016 30092018 30102014 30102016 301
> 02018 30112014 30112016 30112018 30122014 30122016 30122018 31012014 31012016 31012018 31032014 31032016 31032018 31052014 31052016
> 31052018 31072014 31072016 31072018 31082014 31082016 31082018 31102014 31102016 31102018 31122012 31122014 31122016 31122018)
(note: date1012014 not found)
(note: date1012015 not found)
(note: date1012016 not found)
(note: date1012017 not found)
(note: date1012018 not found)
(note: date1022014 not found)
(note: date1022015 not found)
(note: date1022016 not found)
(note: date1022017 not found)
(note: date1022018 not found)
(note: date1032014 not found)
(note: date1032015 not found)
(note: date1032016 not found)
(note: date1032017 not found)
...

Stata then continues to paste notes with new dates until I hit break. Should I simply wait until this finishes or am I doing something wrong? Looking at the notes it seems as if Stata is removing the leading zero before the day in the date format (dd/mm/yyyy). FYI I waited for about five minutes before hitting break.

[Nick Cox]

Good point. This didn't arise with your example but e.g. 01012019 won't be treated as you wish.

reshape long date, i(RIC) j(time) string  
rename date yield  
gen date = daily(time, "DMY")  
format date %td  
gen year = year(date)

is my suggested code to cope.