Variable that counts how many times another variable repeats in the last 5 years

[Viktor Christian]

Hi all,

I am writing my Bachelor thesis which is about how total payout policy (dividends + share repurchases) is affected during a crisis period in which I will be comparing the Financial Crisis to the COVID crisis. Sample includes all firms in the economy excluding some based on their SIC codes (utilities & financial companies).

I am trying to create a variable that measures how many times a company has experienced negative income in the previous 5 fiscal years. My data is quarterly so I would like to know how many times, at that each observation, a company has experienced negative income. The data looks like:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 datacqtr str70 Company double NI NI_Count
"2005Q1" "CECO ENVIRONMENTAL CORP" -.632 1
"2005Q2" "CECO ENVIRONMENTAL CORP" .235 1
"2005Q3" "CECO ENVIRONMENTAL CORP" .355 1
"2005Q4" "CECO ENVIRONMENTAL CORP" -.393 2
"2006Q1" "CECO ENVIRONMENTAL CORP" -.736 3
"2006Q2" "CECO ENVIRONMENTAL CORP" 1.562 3
"2006Q3" "CECO ENVIRONMENTAL CORP" 1.067 3
"2006Q4" "CECO ENVIRONMENTAL CORP" 1.201 3
"2007Q1" "CECO ENVIRONMENTAL CORP" 1.144 3
"2007Q2" "CECO ENVIRONMENTAL CORP" 1.147 3
"2007Q3" "CECO ENVIRONMENTAL CORP" 2.196 3
"2007Q4" "CECO ENVIRONMENTAL CORP" 1.818 3
"2008Q1" "CECO ENVIRONMENTAL CORP" -.575 4
"2008Q2" "CECO ENVIRONMENTAL CORP" 1.029 4
"2008Q3" "CECO ENVIRONMENTAL CORP" 1.196 4
"2008Q4" "CECO ENVIRONMENTAL CORP" 3.36 4
"2009Q1" "CECO ENVIRONMENTAL CORP" .369 4
"2009Q2" "CECO ENVIRONMENTAL CORP" -.639 5
"2009Q3" "CECO ENVIRONMENTAL CORP" -.678 6
"2009Q4" "CECO ENVIRONMENTAL CORP" -14.084 7
"2010Q1" "CECO ENVIRONMENTAL CORP" 1.029 6
"2010Q2" "CECO ENVIRONMENTAL CORP" 1.196 6
"2010Q3" "CECO ENVIRONMENTAL CORP" 3.36 7
"2010Q4" "CECO ENVIRONMENTAL CORP" .369 6
"2011Q1" "CECO ENVIRONMENTAL CORP" -.639 5
"2011Q2" "CECO ENVIRONMENTAL CORP" -.678 6
"2011Q3" "CECO ENVIRONMENTAL CORP" -14.084 7
end

The NI_Count variable is what I am looking for. Thank you in advance for any help!


Best,
Viktor Christian

[Clyde Schechter]

I cannot reproduce your results, and checking by hand they do not seem correct. For instance, in 2010Q3, your NI_Count variable suddenly increases from 6 in 2010Q2 to 7. But NI itself is positive in 2010Q3, so NI_Count should just stay the same. Then in 2010Q4, you have it drop back to 6 (presumably because the negative value in 2005Q4 is now out of the five year range). But because it should have been 6 in 2010Q3, it should now actually go back to 5.

Anyway, I think the following code is a correct implementation of your request, though as explained, it disagrees with your NI_Cout variable in two places.

gen int qdate = quarterly(datacqtr, "YQ")
assert missing(qdate) == missing(datacqtr)
format qdate %tq
drop datacqtr

isid Company qdate, sort

gen byte negative = NI < 0
rangestat (sum) wanted = negative, by(Company) interval(qdate -19 0)

The first paragraph creates a Stata internal format numeric date variable to be used i lieu of the string variable datacqtr (which is not suitable for calculations.)
The -rangestat- command is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

[Viktor Christian]

Clyde Schechter Hi Clyde, thank you so so much for the help. Indeed I noticed I had hand written the variable I wished to create wrongly, apologies for any confusion that created.

The code which you provided is exactly what I was looking for, thank you once again.

Best regards,
Viktor