Beginning and end of fiscal year dates

[Emilie Gronsund]

Hi,

I want to generate two new date variables, one that is identical to another and another one which is 11 months earlier.

I have a variable called datadate which basically corresponds to the end of the fiscal year date.
Now, I want to generate a variable called endfyr which is equal to datadate.... Moreover, I want to generate a variable called begfyr which is the beginning of fiscal year, 11 months prior to endfyr.
My dates in stata are listed as date.month.year: datadate=31dec2004. I have a code that is used for SAS programming, so it doesn't directly translate to stata, but want something equivalent.

gvkey	fyear	datadate
1004	1990	31may1991
1004	1991	31may1992
1013	1994	31oct1994
1013	1995	31oct1995

* create begin and end dates for fiscal year;
endfyr= datadate; format endfyr date9.;
begfyr= intnx('month',endfyr,-11,'beg'); format begfyr date9.;
*intnx(interval, from, n, 'alignment'); Could someone help me with the codes for stata?

Best,
Emilie

[Joe Canner]

Emilie,

It is not entirely clear what you want to do and I'm not very familiar with the SAS intnx() function, but here's my best guess: I am assuming that you want end of fiscal year dates like 31may1991 to generate beginning of fiscal year dates like 01jun1990 (11 months prior at the beginning of the month). There are a number of ways to do this. Here is one:

gen endfyr=datadate
gen begfyr=endfyr-11*30.4       // subtract 11 months (1 month=30.4 days on average)
replace begfyr=mdy(month(begfyr),1,year(begfyr))    // reset to first of the month

Another possibility, which I thinks gets the same answer but with a different definition is this:

gen endfyr=datadate
gen begfyr=mdy(month(endfyr)+1,1,year(endfyr)-1)    // Add 1 to month, subtract 1 from year, 1st of the month

You can probably imagine other solutions (e.g., adding one day to the end of the fiscal year and then subtracting one year).

NOTE: There may also be additional code required, depending on how datadate is stored. If is a string ("31may1991") then you will have to convert it to a Stata date variable using the date() function. If it is already a Stata date variable (numeric with format %td) then no further work is required.

Regards,
Joe

[Roberto Ferrer]

Another possibility, which I thinks gets the same answer but with a different definition is this:

gen endfyr=datadate
gen begfyr=mdy(month(endfyr)+1,1,year(endfyr)-1) // Add 1 to month, subtract 1 from year, 1st of the month

In this case, a provision needs to be made for the case in which the fiscal year ends in december. Unlike other months, december (month 12) should be mapped to january (month 1) and the year stays constant (not -1).

[Joe Canner]

Good point. Perhaps to avoid exceptions a better way would be:

gen begfyr=mdy(month(endfyr-364),1,year(endfyr-364))

This works for leap years as well since it ends up in the right month and we always use the first day.