Why in panel data xtset Stata doesn't recognize the monthly frequencies of date?

[edo papa]

I'm actually working with panel data, but when i let specify the time variable Stata doesn't recognize the monthly frequencies. My database is based on the excel spreadsheet where the are for each column a variable ( such as market premium, ebitda an many more) and for each row an observation on that date for each variable( for example: on the row 01/01/2013 the are * value* ebitda, *value* mp etc). Why it doesn't work?

[Clyde Schechter]

Because 01/01/2013 is a daily date. You do not show actual example data from your data set, so I cannot tell if what you actually have is a string, "01/01/2013", which to Stata is not even a date at all, or whether you have an actual Stata daily date with a display format so that it looks like 01/01/2013 in Stata displays. On the assumption that what you have really is a Stata daily date variable, not a string, you can extract a monthly date from it with the -mofd()- function. Then Stata will work with that as a monthly variable.

If you need more detailed advice, please use the -dataex- command and post back with example data. If you are running version 18, 17, 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.

[edo papa]

I see. In order to specify the period I add another variable t that identify the number of observation for each i, but it dosent work as good as i thought. In fact when I use the the xtset command using Data as the time variable, the panel is strongly balanced for each i, but the time variable isn't recognize as monthly date. Despite this, when I use t as the time variable the variable i (it's the same of ID) is unbalanced.
dataex Data t Nome i rend mp

This is what dataex generated. The variables list is "Data, t, Nome, i , rend, mp"

* Example generated by -dataex-. For more info, type help dataex
clear
input int(Data t) str24 Nome int i double(rend mp)
19359   1 "3M" 1   2.823920265780728  1.5420448414265073
19390   2 "3M" 1   9.380721593968776   7.013942746239691
19418   3 "3M" 1   2.176053564395425  .08108861523603023
19449   4 "3M" 1  1.8116989496000866   3.193749999999998
19479   5 "3M" 1 -1.0411736867013803  1.6380074307194672
19510   6 "3M" 1   5.796269727403159  3.6485699751008798
19540   7 "3M" 1 -1.1753006057318482 -2.3159510582010516
19571   8 "3M" 1   8.324947397310396  4.9896297218938175
19602   9 "3M" 1  -4.079047377755256  -5.137659844836808
19632  10 "3M" 1   5.317837647473152  2.4217447877099727
19663  11 "3M" 1   5.249958200969738  3.0366544740521415
19693  12 "3M" 1  1.4138204924543298   1.850413772963778
19724  13 "3M" 1   9.844924812030069   1.938353094123773
19755  14 "3M" 1 -11.657754010695182  -6.306863060242803
19783  15 "3M" 1  6.7070217917675565   5.691782520325204
19814  16 "3M" 1  3.2675289312457405  3.4176508033978172
19844  17 "3M" 1   3.134842159232404 -.15290582876611378
19875  18 "3M" 1  1.0723670193878212  2.0554898956251404
19905  19 "3M" 1  1.5739179314221536   2.631733984799136
19936  20 "3M" 1  -3.078306585500822 -2.1870855855855944
19967  21 "3M" 1   2.776389979301967  2.8861526032315994
19997  22 "3M" 1 -3.3472222222222174  -2.918183221881673
20028  23 "3M" 1  10.145135795372902  2.6456664876476825
20058  24 "3M" 1  3.1702544031311057  1.6088602599814252
20089  25 "3M" 1    3.89479008598887   .0451330927458965
20120  26 "3M" 1  .06085686465432955 -3.4855986979877893
20148  27 "3M" 1  3.6978469772533837    3.82244683806563
20179  28 "3M" 1  -4.680351906158352  -3.179602024181502
20209  29 "3M" 1 -2.9780950036918554  3.7822763907369836
20240  30 "3M" 1   .8244545915778685  -.7402583535435209
20270  31 "3M" 1  -2.094603094728887 -2.5409944625076135
20301  32 "3M" 1 -3.5528429168005147  .09415539368713538
20332  33 "3M" 1   -7.74047428723688   -9.65627406894962
20362  34 "3M" 1  1.6606498194945931   .6232568786303753
20393  35 "3M" 1  13.579545454545435  10.232939052745982
20423  36 "3M" 1 -1.8884442221110445  -.3607512588981209
20454  37 "3M" 1  -3.989802421924805  -2.623428704085833
20485  38 "3M" 1 -1.2679235262878363  -5.730331452750363
20514  39 "3M" 1   7.301822093726898  1.9052449457225376
20545  40 "3M" 1   4.975249075756625   4.422814675888654
20575  41 "3M" 1    .483495493344477   1.267939343038448
20606  42 "3M" 1   .2079125579184949  .35905845007247056
20636  43 "3M" 1   4.060703064793405   .4416481354302542
20667  44 "3M" 1  1.5665945083741597  3.0449121081745583
20698  45 "3M" 1   .9478938807560703 -.04795287265025454
20728  46 "3M" 1 -2.7447494166018434  -.5033392522540404
20759  47 "3M" 1  -6.164305301645334  -2.214853906733931
20789  48 "3M" 1  5.1019786910197835   3.805920121015093
20820  49 "3M" 1    3.44088513004692  2.2898890987236156
20851  50 "3M" 1 -1.9040152321218602    .839143941800638
20879  51 "3M" 1   8.386139179083193   5.164438477340377
20910  52 "3M" 1  .45296534288422263  -2.231348403461663
20940  53 "3M" 1   2.144505033557049   .6821678547492938
20971  54 "3M" 1   4.897079205379597   .9498157152924511
21001  55 "3M" 1  2.6816735992170386   .8959565217391264
21032  56 "3M" 1 -3.1692322356193134   1.530682352593652
21063  57 "3M" 1  .18702628211437908 -.32162886048988215
21093  58 "3M" 1   4.519551974847705   2.838119350898621
21124  59 "3M" 1   8.187629253619109  2.4855717612193002
21154  60 "3M" 1   4.765835433139282   2.207367402378463
21185  61 "3M" 1 -2.3968484345842844   .6000014314599211
21216  62 "3M" 1  5.3405276798232535   5.633331363466965
21244  63 "3M" 1  -6.695168185851413  -4.980503561969904
21275  64 "3M" 1  -8.091985821734243 -3.7819667036235267
21305  65 "3M" 1  -8.277678487442383  3.3825882454892002
21336  66 "3M" 1  2.3433494000615287   2.255716652581167
21366  67 "3M" 1   -2.03918031965529 -1.1334151721294572
21397  68 "3M" 1  5.8970949263502455  3.4956110648918504
21428  69 "3M" 1  1.8691137406423455    2.80297744360902
21458  70 "3M" 1   .6021240280675186   .9916138132295782
21489  71 "3M" 1   -9.41137659644658  -7.326690980561702
21519  72 "3M" 1   8.604723754031834  3.8909250021538773
21550  73 "3M" 1  -8.727725617934471  -9.926152024063676
21581  74 "3M" 1   4.523984465204159   6.471542917628933
21609  75 "3M" 1   4.182566780478014   3.602172850958563
21640  76 "3M" 1   2.144681671405845  1.6378037682454567
21670  77 "3M" 1 -12.206284797584225  1.5520254947840555
21701  78 "3M" 1 -13.989358843446015 -5.7922356924944545
21731  79 "3M" 1   9.041489627593101   8.351512249301853
21762  80 "3M" 1  -.6589880236089493   .4453136433818364
21793  81 "3M" 1  -6.714351638209514 -1.6003881716678234
21823  82 "3M" 1 -2.0652980460054438 -.32032273814582035
21854  83 "3M" 1   7.393610304331361  3.9431885730501848
21884  84 "3M" 1 -1.0700217531894838   2.034739821882944
21915  85 "3M" 1    4.84340643014202   4.141707349606507
21946  86 "3M" 1 -11.240222197029807    .733340166730629
21975  87 "3M" 1  -2.279839070183277  -4.710312997814901
22006  88 "3M" 1   -12.9917657822507 -19.319963739188292
22036  89 "3M" 1  11.611837163887644  14.626512059776934
22067  90 "3M" 1   4.697173620457617   7.888859335824597
22097  91 "3M" 1  -.0964134207481718  2.1567447220866103
22128  92 "3M" 1 -3.2297497265650197   4.697537271463928
22159  93 "3M" 1   9.567183033043026     6.8211225091894
22189  94 "3M" 1 -3.6468446601941857 -3.9621286347827214
22220  95 "3M" 1   2.613514704956235 -2.1366825140581716
22250  96 "3M" 1   4.652019148152702  10.094192416000432
22281  97 "3M" 1   2.504105090311976  2.1345469502007606
22312  98 "3M" 1 -.05149035986040587 -.19850811714053557
22340  99 "3M" 1    .978820835718379  3.8569901127874644
22371 100 "3M" 1   9.234170398503482   4.603353077356444
end
format %tdnn/dd/CCYY Data

[Carlo Lazzaro]

Edo:
you may want to try:

. xtset i Data, monthly

[Hemanshu Kumar]

edo papa you have only changed the format of the date variable -- which is to say what Stata represents it as, to be human-readable. You need to actually change the daily date to a monthly date. Try this:

format %td Data
gen long Data_month = mofd(Data)

xtset i Data_month, monthly

[Nick Cox]

Clyde Schechter answered the question in #2 but the answer was incompletely understood in #3.

You have what to you are (1) monthly data that happen to be (2) indexed by a daily date. Stata doesn't know (1) unless you tell it but it does know (2) because your original xtset specified a daily date variable. The reason xtset knows this is that you have specified a daily date format for the date variable -- which beyond the business of controlling how dates are displayed is the simplest way of telling Stata that your time variable is indeed a daily date variable.

All Stata can then do is try to implement your instructions -- which imply that your data are mostly holes because only about 1 in 30 of the observations possible are in the dataset; the others are absent from the data (NB, not missing in Stata's sense, just absent). There is no inbuilt researcher inside Stata to say, these are clearly monthly data and so you can't or shouldn't be wanting what you're asking for.

As Clyde explained, you need a monthly date variable like this

. * Example generated by -dataex-. For more info, type help dataex
. clear

. input int(Data t)

         Data         t
  1. 19359 1
  2. 19390 2
  3. 19418 3
  4. 19449 4
  5. 19479 5
  6. end

.
.
. gen mdate = mofd(Data)

. format mdate %tm

. format t %tm

.
. list

     +-------------------------+
     |  Data        t    mdate |
     |-------------------------|
  1. | 19359   1960m2   2013m1 |
  2. | 19390   1960m3   2013m2 |
  3. | 19418   1960m4   2013m3 |
  4. | 19449   1960m5   2013m4 |
  5. | 19479   1960m6   2013m5 |
     +-------------------------+

Now you can xtset using a panel identifier and your new monthly date variable, here called mdate.

Your home-grown solution is way inferior to Clyde's solution, as the code above using your variable t demonstrated.

Stata will, contrary to your statement, recognize t as a monthly date, but it needs you to tell it that it is a monthly date, either by specifying a monthly date format, as was done above. or otherwise through xtset. But then if you do that, the answer is wrong for any research purposes as for Stata a monthly date of 0 is January 1960.

In #3 you say that the data are balanced and then they are unbalanced. I can't comment on this from your data example, but one statement is false and the other is true.

PS @Carlo Lazzaro's suggestion in #4 unfortunately won't help as if only because the data are still mostly holes from Stata's point of view. . Also 19359 for example is to Stata 3573m4 (i.e. April in the year 3573), which is no use to you or any reader of your work.

[Nick Cox]

#6 is in effect a longer version of #5. which was not visible to me when I started writing. By accident rather than design, but fortunately if fortuitously, #5 and #6 should serve as the executive summary and the longer explanation of the same story. Use mofd(), in two words.

[edo papa]

Thank you guys! It works!