Lag not working for monthly/quarterly data

[Philipp Zacharias-Langhans]

I am struggling with this issue and can't understand, why it's not working.
I have quarterly cross-sectional data (isid gvkey date is alright) and want to generate lagged values (the lag is quarterly, or 3 months respectively)
I did the following preparation:

gen date=date(datadate,"YMD")
format date %tm
tsset gvkey date, monthly
sort gvkey date
gen test=L3.ibq

but it only generates missing values for test. Alternatively with delta(3) and test=L.ibq it doesn't work either...

Could someone please help me identifying where the mistake in my process is?

Many thanks in advance!!

Philipp Z-L

* Example generated by -dataex-. To install: ssc install dataex
clear
input long gvkey str8 datadate float(date ibq test)
1166 "19980630" 14060     6.37 .
1166 "19990630" 14425   12.201 .
1166 "20010630" 15156      9.3 .
1166 "20040331" 16161    14.88 .
1166 "20040630" 16252    4.359 .
1166 "20040930" 16344    3.692 .
1166 "20041231" 16436    1.395 .
1166 "20050331" 16526   -7.248 .
1166 "20050630" 16617     .518 .
1166 "20050930" 16709   -6.296 .
1166 "20051231" 16801  -27.191 .
1166 "20060331" 16891   10.033 .
1166 "20060630" 16982   18.718 .
1166 "20060930" 17074   13.055 .
1166 "20061231" 17166   12.012 .
1166 "20070331" 17256   11.157 .
1166 "20070630" 17347   14.903 .
1166 "20070930" 17439   15.827 .
1166 "20071231" 17531    19.09 .
1166 "20080331" 17622   12.639 .
1166 "20080630" 17713  -34.706 .
1166 "20080930" 17805    2.421 .
1166 "20081231" 17897   -6.245 .
1166 "20090331" 17987  -23.276 .
1166 "20090630" 18078  -56.549 .
1166 "20090930" 18170  -15.817 .
1166 "20091231" 18262  -11.741 .
1166 "20100331" 18352    4.172 .
1166 "20100630" 18443   51.153 .
1166 "20100930" 18535   34.286 .
1166 "20101231" 18627   24.656 .
1166 "20110331" 18717   40.074 .
1166 "20110630" 18808    54.67 .
1166 "20110930" 18900   80.971 .
1166 "20111231" 18992   15.445 .
1166 "20120331" 19083     6.26 .
1166 "20120630" 19174   23.654 .
1166 "20120930" 19266    4.908 .
1166 "20121231" 19358  -21.733 .
1166 "20130331" 19448 1410.146 .
1166 "20130630" 19539  -23.403 .
1166 "20130930" 19631    -.875 .
1166 "20131231" 19723 -333.975 .
1166 "20140331" 19813   27.137 .
1166 "20140630" 19904   34.598 .
1166 "20140930" 19996   54.564 .
1166 "20141231" 20088   21.009 .
1166 "20150331" 20178   59.988 .
1166 "20150630" 20269   39.862 .
1166 "20150930" 20361   35.707 .
1166 "20151231" 20453   21.719 .
1166 "20160331" 20544    5.358 .
1166 "20160630" 20635   35.576 .
1166 "20160930" 20727   33.058 .
1166 "20161231" 20819   61.479 .
1166 "20170331" 20909   35.863 .
1166 "20170630" 21000  132.122 .
1166 "20170930" 21092   42.211 .
1166 "20171231" 21184  242.206 .
1166 "20180331" 21274   14.954 .
1166 "20180630" 21365   59.408 .
1166 "20180930" 21457   39.096 .
1166 "20181231" 21549   43.676 .
1932 "20010630" 15156      213 .
1932 "20040331" 16161      215 .
1932 "20040630" 16252      255 .
1932 "20040930" 16344      421 .
1932 "20041231" 16436      207 .
1932 "20050331" 16526      428 .
1932 "20050630" 16617      510 .
1932 "20050930" 16709      443 .
1932 "20051231" 16801      390 .
1932 "20060331" 16891      452 .
1932 "20060630" 16982      549 .
1932 "20060930" 17074      446 .
1932 "20061231" 17166      449 .
1932 "20070331" 17256      495 .
1932 "20070630" 17347      584 .
1932 "20070930" 17439      600 .
1932 "20071231" 17531      451 .
1932 "20080331" 17622      599 .
1932 "20080630" 17713      650 .
1932 "20080930" 17805      657 .
1932 "20081231" 17897      551 .
1932 "20090331" 17987      725 .
1932 "20090630" 18078      725 .
1932 "20090930" 18170    631.5 .
1932 "20091231" 18262    631.5 .
1932 "20100331" 18352    762.5 .
1932 "20100630" 18443    762.5 .
1932 "20100930" 18535      677 .
1932 "20101231" 18627      677 .
1932 "20110331" 18717      935 .
1932 "20110630" 18808      935 .
1932 "20110930" 18900    612.5 .
1932 "20111231" 18992    612.5 .
1932 "20120331" 19083      954 .
1932 "20120630" 19174    964.5 .
1932 "20120930" 19266    944.5 .
1932 "20121231" 19358      956 .
end
format %tm date

[Clyde Schechter]

You have confused Stata by telling it to -tsset gvkey date, monthly- when the variable date is not, in fact, a monthly date variable--it is a daily date. If you look at the output Stata gives you from that -tsset- command you will see an earlier warning of the trouble to come:

. tsset gvkey date, monthly
       panel variable:  gvkey (unbalanced)
        time variable:  date, 3131m9 to 3755m10, but with gaps
                delta:  1 month

[emphasis added] you will see that -tsset- thinks your dates are in the distant future and span several centuries. The problem is that you need a bona fide monthly date.

gen mdate = mofd(date)
format mdate %tm
tsset gvkey mdate

and then things will run properly.

If you are going to work with this kind of data, you need a thorough understanding of how Stata represents and works with dates and times. Invest some time in reading the datetime chapter of the [D] volume of the PDF documentation that comes with your Stata. It's a heavy lift, and you won't remember everything. But it will bring across the concepts so that you will understand why your variable date was not appropriate for what you are doing, why mdate is, and how to get from date to mdate. Also review the manual section on the -tsset- command to understand that options like -monthly-, -quarterly-, etc. do not convert your date variable to the appropriate type; they simply tell -tsset- to interpret the date variable as if it were that type.

Added: You also shot yourself in the foot by -format date %tm-. If you run your -dataex- and then just browse the data, you will see, even without running -tsset-, that Stata thinks your date variable refers to time in a future age. Formating -date- with %tm does not modify the actual content or meaning of the variable: it just tells Stata to display it as if it were a monthly variable. But monthly variables with values that large are in the distant future. Daily variables (which are what you have) with numbers that large are contemporary.

[Nick Cox]

format %tm

does not magically change your daily dates to monthly dates. If you look at your data you will see that command just messed them up, as for example

. di %tm 14060
 3131m9

Thus 14060 declared as a monthly date is September in the year 3131 for which I am confident that you have no data.

You have monthly dates that happen to be daily dates for the end of each month, but Stata can't see anything but daily dates spaced roughly 30 apart, hence nothing is reported for lags 3. And delta(3) won't help because your distance between dates is not 3 either.

You need

gen mdate=mofd(daily(datadate,"YMD"))

format mdate %tm

tsset gvkey mdate, monthly

gen test=L3.ibq

There I used daily() rather than date() to underline that what either function does is create daily dates. After that I pushed your daily dates through mofd() to get monthly dates. Then you should get what you want.

The fallacy that changing the format changes the kind of date is common, despite the documentation in

help datetime

and elsewhere. For exegesis see https://journals.sagepub.com/doi/pdf...867X1201200415

[Philipp Zacharias-Langhans]

Dear Clyde,

thank you very much for your help, I kind of expected it to be related to the daily date issue, but as you noticed, my understanding of Stata's handling of dates was a bit lacking, so thank you as well for the recommendation!

Needless to say that your adaption has made it work as wanted.

Edit: Thank you as well Nick, it's great to see the support in this forum, as I mentioned above, my understanding of the date "format" was clearly lacking