Please help: How to get interval from one date variable without fixed starting points

[Sabrina Gong]

Hello, this question sounds stupid but I have already spent the whole weekend and still couldn't figure it out.

My data set: Unbalanced panel data with firm-monthly obs. For each firm, initially it has the fiscal year period with corresponding accounting data and monthly return. Additionally, for each firm each fiscal year, I get -24 to +36 monthly return. So even if the accounting info is missing, the firm still has -24 and/or +36 returns. Each firm has different fiscal year end and beginning month.

My question is: I have a variable "date", which is the date of the obs of monthly return. For example, now I have "date" started from Apr 1988 to May 1993. From Apr 1990 to Mar 1991, I also have fiscal year date "datadate" with accounting info. Other than that, all accounting info are missing. Now I want to know the interval between Apr 1988 to Apr 1990(fiscal year starts) , ideally which could be marked as -24,-23,-22..... and I want to know the interval between Mar 1991(fiscal year ends) to May 1993, ideally which could be 1, 2, 3.....

Notice each firm has different fiscal year end and starts, and the period before/after a fiscal year may not be exactly between -24 to 36 months because of missing data.

Could anyone please tell me how to do that? I almost freak out and I tried different ways to sort the variable but different fiscal year end and missing data in pre-fiscal year (not necessary -24 months) are causing the problem. I really appreciate anyone could help me and share me you ideas.

Thank you so much

[Clyde Schechter]

I couldn't understand your explanation of what you have and what you want. Why don't you show us a sample of the data along with hand-calculated results for what you want to end up with?

[Sabrina Gong]

I couldn't understand your explanation of what you have and what you want. Why don't you show us a sample of the data along with hand-calculated results for what you want to end up with?

Sorry if I didn't explain well.
Data example

Date                  Fiscal year     Monthly return                    Net income
Apr 1988                                      -0.05                          .
May 1988                                      0.07                          .
June 1988                                     0.09                          .
;;;; 
Apr 1990                 0                  0.08                         100
May 1990                                   0.12                          100
Jun 1990                                      0.01                        100
;;;;
Mar 1991                                      0.2                          100
Feb 1991                                      0.11                        100
May 1991               0                    -0.12                       100
June 1991                                     0.04                        .
;;;;;   
May 1993                                      0.05               .

The starting and end of a fiscal-year is marked as 0. Now i want to get the "distance" between April 1988 and Apr 1990, which should be 24 months( value=-24 because it's before Apr 1990), also "distance" between May 1988 to Apr 1990, which should be 23 months (value=-23), so on so forth....

If it's not clear, pls let me know. Thank you.

[Clyde Schechter]

I'm still a bit confused, but let me see if this helps. It seems that the monthly return and net income are just along for the ride and have nothing to do with the calculation desired. So, the relevant variables are Date and Fiscal year. Then you also "mark" something with 0. In your sample data, it's an extra zero stuck in after fiscal year. I'm going to assume that in your actual data this is a variable, which I'll call fy_mark. You don't say what value it takes on in all the other observations. I'll assume it's just a missing value. So fy_mark = 0 at the beginning or end of a fiscal year, and is . otherwise. I'm also going to assume that there is only one such fiscal year marked. (If that's not true, a different, more complicated solution will be needed.)

Now, also, your variable Date appears to be just a string variable that encodes the three digit abbreviation of the month. The year itself is in the fiscal year variable. That's not very workable. So I'm going to leave it to you to play with the date functions and come up with a true monthly date variable, which I'll call current_month, calculated from Date and Fiscal year So I'm going to start from a data set that looks like this:

current_month fy_mark
1988m4            .
1998m5            .
1998m6            .
1998m7            .
etc.
1990m3            .
1990m4            0
1990m5            .
etc.

Comment: current_month is a Stata monthly variable, formatted %tm. (See -help datetime- and -help date functions- if need be.)

So now you can do this:

egen marked_month = max(current_month*!missing(fy_mark))
format marked_month %tm
gen interval = current_month - marked_month

You seem to be interested in having this only for the first and last months. If so, you can just replace interval by . elsewhere:

replace interval = . if _n != 1 & _n != _N

Finally, you don't say this, but I'm guessing that in fact you have data like this for a bunch of firms. In that case, all you have to do is put -by firm_id (current_month), sort:-
in front of the -egen- command (and in front of the -replace interval- command if you're using it.)

Hope this helps.