sum of the coming 5 years of a variable

[Floris bedkom]

Dear Statalist,

I am trying to generate a variable, that is equal to the sum of the coming 5 years of goodwill impaired (GwlImpaired), for every cusip, in every year, so the collapse command does not yield the desired outcome. My dataset looks as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
fyear cusip     gdwl   GwlImpaired
2001 "125141101"   8.135      0
2002 "125141101"   9.527      0
2003 "125141101"   9.527      0
2004 "125141101"   9.527      0
2005 "125141101"   9.527      0
2006 "125141101"   9.527      0
2007 "125141101"  14.761      0
2008 "125141101"  31.116      0
2009 "125141101"  14.591  17.11
2010 "125141101"  14.713      0
2011 "125141101"  14.661      0
2012 "125141101"  19.548      0
2013 "125141101"  132.22      0
2014 "125141101" 167.547      0
2015 "125141101" 220.163   3.34
2016 "125141101" 170.153 57.923
2017 "125141101" 166.951  7.168
2018 "125141101" 152.156      0
2019 "125141101"  152.02      0
2020 "125141101"  161.82      0
2001 "552691107"       .      0
2002 "552691107"       0     45
2003 "552691107"       0      0
2004 "552691107"       0      0
2001 "00817Y108"  6583.8      0
2001 "00817Y108"  6583.8      0
2002 "00817Y108"  3618.4      0
2002 "00817Y108"  3618.4      0
2003 "00817Y108"  3679.5      0
2003 "00817Y108"  3679.5      0
2004 "00817Y108"  3687.8      0
2004 "00817Y108"  3687.8      0
2005 "00817Y108"  4523.2      0
2005 "00817Y108"  4523.2      0
2006 "00817Y108"  4603.6      0
2006 "00817Y108"  4603.6      0
2007 "00817Y108"    5059      0
2007 "00817Y108"    5059      0
2008 "00817Y108"  5085.6      0
2008 "00817Y108"  5085.6      0
2009 "00817Y108"  5146.2      0
2009 "00817Y108"  5146.2      0
2010 "00817Y108"  5146.4      0
2010 "00817Y108"  5146.4      0
2011 "00817Y108"  6203.9      0
2011 "00817Y108"  6203.9      0
2012 "00817Y108"  6214.4      0
2012 "00817Y108"  6214.4      0
2013 "00817Y108" 10227.5      0
2013 "00817Y108" 10227.5      0
2014 "00817Y108" 10613.2      0
2014 "00817Y108" 10613.2      0
2015 "00817Y108" 10636.8      0
2015 "00817Y108" 10636.8      0
2016 "00817Y108"   10637      0
2016 "00817Y108"   10637      0
2017 "00817Y108"   10571      0
2017 "00817Y108"   10571      0
2001 "57686G105"       0      0
2002 "57686G105"       0      0
2003 "57686G105"       .      0
2004 "57686G105"       .      0
2005 "57686G105"       .      0
2006 "57686G105"       9      0
2007 "57686G105"      12      0
2008 "57686G105"      26      0
2009 "57686G105"      27      0
2010 "57686G105"      27      0
2011 "57686G105"      27      0
2012 "57686G105"      27      0
2013 "57686G105"    27.4      0
2014 "57686G105"    27.4      0
2015 "57686G105"   241.6      0
2016 "57686G105"   323.7      0
2017 "57686G105"   323.7      0
2018 "57686G105"   327.8      0
2019 "57686G105"   327.8      0
2020 "57686G105"   327.8      0
2001 "438516106"    5441      0
2002 "438516106"    5698      0
2003 "438516106"    5789      0
2004 "438516106"    6013      0
2005 "438516106"    7660      0
2006 "438516106"    8403      0
2007 "438516106"    9175      0
2008 "438516106"   10185      0
2009 "438516106"   10494      0
2010 "438516106"   11597      0
2011 "438516106"   11858      0
2012 "438516106"   12425      0
2013 "438516106"   13046      0
2014 "438516106"   12788      0
2015 "438516106"   15895      0
2016 "438516106"   17707      0
2017 "438516106"   18277      0
2018 "438516106"   15546      0
2019 "438516106"   15563      0
2020 "438516106"   16058      0
2001 "806530101"       .      0
2001 "83088M102"       .      0
end

Thank you very much for your reply!

Floris Bedkom

[Nick Cox]

Although your example purports to be dataex output, you have mangled it somehow. This corrected example should help, noting that rangestat must be installed with ssc install rangestat.

* Example generated by -dataex-. To install: ssc install dataex
clear
input fyear str9 cusip     gdwl   GwlImpaired
2001 "125141101"   8.135      0
2002 "125141101"   9.527      0
2003 "125141101"   9.527      0
2004 "125141101"   9.527      0
2005 "125141101"   9.527      0
2006 "125141101"   9.527      0
2007 "125141101"  14.761      0
2008 "125141101"  31.116      0
2009 "125141101"  14.591  17.11
2010 "125141101"  14.713      0
2011 "125141101"  14.661      0
2012 "125141101"  19.548      0
2013 "125141101"  132.22      0
2014 "125141101" 167.547      0
2015 "125141101" 220.163   3.34
2016 "125141101" 170.153 57.923
2017 "125141101" 166.951  7.168
2018 "125141101" 152.156      0
2019 "125141101"  152.02      0
2020 "125141101"  161.82      0
2001 "552691107"       .      0
2002 "552691107"       0     45
2003 "552691107"       0      0
2004 "552691107"       0      0
2001 "00817Y108"  6583.8      0
2001 "00817Y108"  6583.8      0
2002 "00817Y108"  3618.4      0
2002 "00817Y108"  3618.4      0
2003 "00817Y108"  3679.5      0
2003 "00817Y108"  3679.5      0
2004 "00817Y108"  3687.8      0
2004 "00817Y108"  3687.8      0
2005 "00817Y108"  4523.2      0
2005 "00817Y108"  4523.2      0
2006 "00817Y108"  4603.6      0
2006 "00817Y108"  4603.6      0
2007 "00817Y108"    5059      0
2007 "00817Y108"    5059      0
2008 "00817Y108"  5085.6      0
2008 "00817Y108"  5085.6      0
2009 "00817Y108"  5146.2      0
2009 "00817Y108"  5146.2      0
2010 "00817Y108"  5146.4      0
2010 "00817Y108"  5146.4      0
2011 "00817Y108"  6203.9      0
2011 "00817Y108"  6203.9      0
2012 "00817Y108"  6214.4      0
2012 "00817Y108"  6214.4      0
2013 "00817Y108" 10227.5      0
2013 "00817Y108" 10227.5      0
2014 "00817Y108" 10613.2      0
2014 "00817Y108" 10613.2      0
2015 "00817Y108" 10636.8      0
2015 "00817Y108" 10636.8      0
2016 "00817Y108"   10637      0
2016 "00817Y108"   10637      0
2017 "00817Y108"   10571      0
2017 "00817Y108"   10571      0
2001 "57686G105"       0      0
2002 "57686G105"       0      0
2003 "57686G105"       .      0
2004 "57686G105"       .      0
2005 "57686G105"       .      0
2006 "57686G105"       9      0
2007 "57686G105"      12      0
2008 "57686G105"      26      0
2009 "57686G105"      27      0
2010 "57686G105"      27      0
2011 "57686G105"      27      0
2012 "57686G105"      27      0
2013 "57686G105"    27.4      0
2014 "57686G105"    27.4      0
2015 "57686G105"   241.6      0
2016 "57686G105"   323.7      0
2017 "57686G105"   323.7      0
2018 "57686G105"   327.8      0
2019 "57686G105"   327.8      0
2020 "57686G105"   327.8      0
2001 "438516106"    5441      0
2002 "438516106"    5698      0
2003 "438516106"    5789      0
2004 "438516106"    6013      0
2005 "438516106"    7660      0
2006 "438516106"    8403      0
2007 "438516106"    9175      0
2008 "438516106"   10185      0
2009 "438516106"   10494      0
2010 "438516106"   11597      0
2011 "438516106"   11858      0
2012 "438516106"   12425      0
2013 "438516106"   13046      0
2014 "438516106"   12788      0
2015 "438516106"   15895      0
2016 "438516106"   17707      0
2017 "438516106"   18277      0
2018 "438516106"   15546      0
2019 "438516106"   15563      0
2020 "438516106"   16058      0
2001 "806530101"       .      0
2001 "83088M102"       .      0
end

rangestat (sum) wanted=GwlImpaired, int(fyear 1 5) by(cusip)

[Andrew Musau]

Do not modify the output from dataex when copying and pasting to the forum. In your example, you have deleted the input statement and the variable types. You have a number of duplicate observations in your dataset. I drop these and use rangestat from SSC below.

encode cusip, gen(cid)
duplicates drop *, force
*ssc install rangestat
rangestat (sum) GwlImpaired, interval(fyear 1 5) by(cid)

Directly using Stata's time series operators

xtset cid fyear
gen wanted1= F1.GwlImpaired+ F2.GwlImpaired+ F3.GwlImpaired+ F4.GwlImpaired+ F5.GwlImpaired

[Nick Cox]

Good for Andrew Musau in spotting the duplicates. Note that rangestat is fine with string identifiers for panels.