extracting 4 quarter dates from annual dates when some dates are missing

[Mike Kraft]

Dear all
I have a dataset that looks like:

FirmID rdq fqenddt earnings
12994 8/31/1987 6/30/1987
12994 11/25/1987 9/30/1987
12994 3/11/1988 12/31/1987
12994 5/25/1988 3/31/1988
12994 8/29/1989 6/30/1989
12994 11/3/1989 9/30/1989
12994 2/20/1990 12/31/1989
12994 5/10/1990 3/31/1990
12994 8/28/1990 6/30/1990
12994 12/10/1990 9/30/1990
12994 2/5/1991 12/31/1990
12994 5/3/1991 3/31/1991
12994 8/27/1991 6/30/1991
12994 11/6/1991 9/30/1991
12994 2/12/1992 12/31/1991
11907 4/28/1993 2/28/1993
11907 6/9/1993 5/31/1993
11907 9/21/1993 8/31/1993
11907 12/13/1993 11/30/1993



I focus on fqenddt (fiscal quarter end date) to construct my data as the main date variable. I want to convert this string variable to a date format and then create a quarter variable that contains the quarter number (1,2,3,or 4). One of the complications is that fiscal quarter is not always 31 March, 30June, 30 September and 31 December for all firms (for example firmID 11907 above), and also my data may have only fewer than 4 quarters available for few firms(for example firmID 12994 above)


I want to have something similar to:

FirmID rdq fqenddt year quarter earnings
1987 1
1987 2
1987 3
1987 4
1988 1
1988 2
and so on




What I have done is:
gen newdate = subinstr(fqenddt, "/", "-", .)
generate fiscalQdate = date(newstrvar, "MDY")
format %td fiscalQdate

This gave me:

fiscalQdate
30jun1987
30sep1987
31dec1987
31mar1988
30jun1989
30sep1989
31dec1989
31mar1990

My main problem is how to generate the quarter variable when few firms do not have all quarters?



Can anyone help please ?

Thanks a lot

[Rich Goldstein]

if you look at "help datetime" you will see a list of sub-headings - click on "SIF-to-SIF conversion" to see how to move from a date to quarterly using "qofd(datevar)"; so, e.g., try:

gen quarter=qofd(fiscalQdate)

by the way, there was no reason to change the "/"'s

[Nick Cox]

Note that (a) production of a numeric date variable and (b) assignment of an appropriate date display format almost always go together but are logically distinct. Thus the process is not one of converting to a date format, but one of two separate steps (a) and (b).

Using dataex (SSC) to produce data examples as requested in the FAQ would be helpful to cut down on the engineering required to yield code that can be run, as here.

You can get quarterly date variables directly by combining production of daily date variables and mapping to equivalent quarters. I use daily() rather than date() because the name of the latter is often misinterpreted, even though it does the same thing.

Getting the quarter itself 1 to 4 is a matter of using quarter() rather than qofd(). This is all documented at help dates

Sorry, but I don't understand the rest of your question. If there are gaps in the data, how would you expect them to be managed?

clear 
input FirmID str10(rdq fqenddt) 
12994 "8/31/1987" "6/30/1987"
12994 "11/25/1987" "9/30/1987"
12994 "3/11/1988" "12/31/1987"
12994 "5/25/1988" "3/31/1988"
12994 "8/29/1989" "6/30/1989"
12994 "11/3/1989" "9/30/1989"
12994 "2/20/1990" "12/31/1989"
12994 "5/10/1990" "3/31/1990"
12994 "8/28/1990" "6/30/1990"
12994 "12/10/1990" "9/30/1990"
12994 "2/5/1991" "12/31/1990"
12994 "5/3/1991" "3/31/1991"
12994 "8/27/1991" "6/30/1991"
12994 "11/6/1991" "9/30/1991"
12994 "2/12/1992" "12/31/1991"
11907 "4/28/1993" "2/28/1993"
11907 "6/9/1993" "5/31/1993"
11907 "9/21/1993" "8/31/1993"
11907 "12/13/1993" "11/30/1993"
end 

gen rqdate = qofd(daily(rdq, "MDY")) 
gen fqdate = qofd(daily(fq, "MDY")) 
format *date %tq 
list, sepby(FirmID) 

     +----------------------------------------------------+
     | FirmID          rdq      fqenddt   rqdate   fqdate |
     |----------------------------------------------------|
  1. |  12994    8/31/1987    6/30/1987   1987q3   1987q2 |
  2. |  12994   11/25/1987    9/30/1987   1987q4   1987q3 |
  3. |  12994    3/11/1988   12/31/1987   1988q1   1987q4 |
  4. |  12994    5/25/1988    3/31/1988   1988q2   1988q1 |
  5. |  12994    8/29/1989    6/30/1989   1989q3   1989q2 |
  6. |  12994    11/3/1989    9/30/1989   1989q4   1989q3 |
  7. |  12994    2/20/1990   12/31/1989   1990q1   1989q4 |
  8. |  12994    5/10/1990    3/31/1990   1990q2   1990q1 |
  9. |  12994    8/28/1990    6/30/1990   1990q3   1990q2 |
 10. |  12994   12/10/1990    9/30/1990   1990q4   1990q3 |
 11. |  12994     2/5/1991   12/31/1990   1991q1   1990q4 |
 12. |  12994     5/3/1991    3/31/1991   1991q2   1991q1 |
 13. |  12994    8/27/1991    6/30/1991   1991q3   1991q2 |
 14. |  12994    11/6/1991    9/30/1991   1991q4   1991q3 |
 15. |  12994    2/12/1992   12/31/1991   1992q1   1991q4 |
     |----------------------------------------------------|
 16. |  11907    4/28/1993    2/28/1993   1993q2   1993q1 |
 17. |  11907     6/9/1993    5/31/1993   1993q2   1993q2 |
 18. |  11907    9/21/1993    8/31/1993   1993q3   1993q3 |
 19. |  11907   12/13/1993   11/30/1993   1993q4   1993q4 |
     +----------------------------------------------------+

[Mike Kraft]

Thanks to you both.
Both codes gave the same output. Thanks

To follow up on your reply Nick, I thought I might need to reflect all quarters even if they are missing because I will need to to calculate the seasonal changes with regard to some variables. For example I need to generate a new variable to reflect the seasonal changes in earnings as the difference between earnings (quarter 1 year 2) minus earnings (quarter 1 year 1) and so on.
Hence I can compare the changes per the same quarter !

If I generate a lagged variables as gen lag_earnings=L.earnings, this will lead to lag_earnings for quarter 1 year 2=earnings (quarter 4 year 1) which I do not want, as I want this to be for quarter 1 year 1 here in order to calculate the seasonal difference as the difference between earnings and lag_earnings for the same quarter but previous year!

Any help?

[Clyde Schechter]

I think if you -xtset- your data with your quarterly dates and specify the -quarterly- option in the -xtset- command, then S4.var will give you a four-quarter seasonal difference in var.

See -help xtset- (specifically -tsoptions- within that) and -help tsvarlist-.

Note that you do not have to have synthetic observations filling in the missing quarters for this to work correctly. That is one of the nicest features about the time series operators in Stata.

Your major problem will be that if you are missing a lot of quarters, then many instances of the seasonal difference will be undefined and will show up as missing values.