query on time-series function for computing average

Sanaullah Farooq

Join Date: Mar 2016
Posts: 107

query on time-series function for computing average

20 May 2019, 03:12

I am attempting to calculate average assets i.e. computed as average assets = (assets (t) + assets (t-1) ) / 2
I have used this code to generate average assets:

by fid: gen avg_assets = (assets + assets[_n-1]) / 2

The computed value of average assets looks like as follows. The problem is that ever when the previous year (t-1) value of asset is represented as empty cell represented as "." the STATA treats it as 0 and gives me the wrong estimation of average assets. In reality, if one of the assets value is unavailable as represented by ".", STATA must return average asset value as ".", instead of treating it as zero. Please help.

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str18 firmid int time str22 firmid_time float(fid assets_new avg_assets)
"0876711D LN Equity" 2003 "0876711D LN Equity2003" 1       .         .
"0876711D LN Equity" 2004 "0876711D LN Equity2004" 1       .         0
"0876711D LN Equity" 2005 "0876711D LN Equity2005" 1       .         0
"0876711D LN Equity" 2006 "0876711D LN Equity2006" 1       .         0
"0876711D LN Equity" 2007 "0876711D LN Equity2007" 1       .         0
"0876711D LN Equity" 2008 "0876711D LN Equity2008" 1   830.7    415.35
"0876711D LN Equity" 2009 "0876711D LN Equity2009" 1   714.2    772.45
"0876711D LN Equity" 2010 "0876711D LN Equity2010" 1   788.6     751.4
"0876711D LN Equity" 2011 "0876711D LN Equity2011" 1     727     757.8
"0876711D LN Equity" 2012 "0876711D LN Equity2012" 1   684.7    705.85
"0876711D LN Equity" 2013 "0876711D LN Equity2013" 1       .    342.35
"0876711D LN Equity" 2014 "0876711D LN Equity2014" 1       .         0
"0876711D LN Equity" 2015 "0876711D LN Equity2015" 1       .         0
"0876711D LN Equity" 2016 "0876711D LN Equity2016" 1       .         0
"0876711D LN Equity" 2017 "0876711D LN Equity2017" 1       .         0
"0876711D LN Equity" 2018 "0876711D LN Equity2018" 1       .         0
"1218069D LN Equity" 2003 "1218069D LN Equity2003" 2 182.889         .
"1218069D LN Equity" 2004 "1218069D LN Equity2004" 2  232.52  207.7045
"1218069D LN Equity" 2005 "1218069D LN Equity2005" 2 275.615  254.0675
"1218069D LN Equity" 2006 "1218069D LN Equity2006" 2 717.403   496.509
"1218069D LN Equity" 2007 "1218069D LN Equity2007" 2 790.272  753.8375
"1218069D LN Equity" 2008 "1218069D LN Equity2008" 2 903.164   846.718
"1218069D LN Equity" 2009 "1218069D LN Equity2009" 2 937.341  920.2525
"1218069D LN Equity" 2010 "1218069D LN Equity2010" 2 910.465   923.903
"1218069D LN Equity" 2011 "1218069D LN Equity2011" 2 823.901   867.183
"1218069D LN Equity" 2012 "1218069D LN Equity2012" 2       .  411.9505
"1218069D LN Equity" 2013 "1218069D LN Equity2013" 2       .         0
"1218069D LN Equity" 2014 "1218069D LN Equity2014" 2       .         0
"1218069D LN Equity" 2015 "1218069D LN Equity2015" 2       .         0
"1218069D LN Equity" 2016 "1218069D LN Equity2016" 2       .         0
"1218069D LN Equity" 2017 "1218069D LN Equity2017" 2       .         0
"1218069D LN Equity" 2018 "1218069D LN Equity2018" 2       .         0
"1334987D LN Equity" 2003 "1334987D LN Equity2003" 3 126.402         .
"1334987D LN Equity" 2004 "1334987D LN Equity2004" 3 191.404   158.903
"1334987D LN Equity" 2005 "1334987D LN Equity2005" 3  216.22   203.812
"1334987D LN Equity" 2006 "1334987D LN Equity2006" 3 279.354   247.787
"1334987D LN Equity" 2007 "1334987D LN Equity2007" 3 260.482   269.918
"1334987D LN Equity" 2008 "1334987D LN Equity2008" 3   212.8   236.641
"1334987D LN Equity" 2009 "1334987D LN Equity2009" 3   245.4     229.1
"1334987D LN Equity" 2010 "1334987D LN Equity2010" 3   223.2     234.3
"1334987D LN Equity" 2011 "1334987D LN Equity2011" 3       .     111.6
"1334987D LN Equity" 2012 "1334987D LN Equity2012" 3       .         0
"1334987D LN Equity" 2013 "1334987D LN Equity2013" 3       .         0
"1334987D LN Equity" 2014 "1334987D LN Equity2014" 3       .         0
"1334987D LN Equity" 2015 "1334987D LN Equity2015" 3       .         0
"1334987D LN Equity" 2016 "1334987D LN Equity2016" 3       .         0
"1334987D LN Equity" 2017 "1334987D LN Equity2017" 3       .         0
"1334987D LN Equity" 2018 "1334987D LN Equity2018" 3       .         0
"1561649D LN Equity" 2003 "1561649D LN Equity2003" 4 205.089         .
"1561649D LN Equity" 2004 "1561649D LN Equity2004" 4 212.072  208.5805
"1561649D LN Equity" 2005 "1561649D LN Equity2005" 4 203.671  207.8715
"1561649D LN Equity" 2006 "1561649D LN Equity2006" 4 247.038  225.3545
"1561649D LN Equity" 2007 "1561649D LN Equity2007" 4       .   123.519
"1561649D LN Equity" 2008 "1561649D LN Equity2008" 4 690.709  345.3545
"1561649D LN Equity" 2009 "1561649D LN Equity2009" 4  830.02  760.3645
"1561649D LN Equity" 2010 "1561649D LN Equity2010" 4  1565.9   1197.96
"1561649D LN Equity" 2011 "1561649D LN Equity2011" 4  1343.3    1454.6
"1561649D LN Equity" 2012 "1561649D LN Equity2012" 4  1488.1    1415.7
"1561649D LN Equity" 2013 "1561649D LN Equity2013" 4  1271.1    1379.6
"1561649D LN Equity" 2014 "1561649D LN Equity2014" 4  2122.2   1696.65
"1561649D LN Equity" 2015 "1561649D LN Equity2015" 4       .    1061.1
"1561649D LN Equity" 2016 "1561649D LN Equity2016" 4       .         0
"1561649D LN Equity" 2017 "1561649D LN Equity2017" 4       .         0
"1561649D LN Equity" 2018 "1561649D LN Equity2018" 4       .         0
"1638414D LN Equity" 2003 "1638414D LN Equity2003" 5 145.775         .
"1638414D LN Equity" 2004 "1638414D LN Equity2004" 5 145.237   145.506
"1638414D LN Equity" 2005 "1638414D LN Equity2005" 5  149.61  147.4235
"1638414D LN Equity" 2006 "1638414D LN Equity2006" 5 221.261  185.4355
"1638414D LN Equity" 2007 "1638414D LN Equity2007" 5 207.725   214.493
"1638414D LN Equity" 2008 "1638414D LN Equity2008" 5 177.811   192.768
"1638414D LN Equity" 2009 "1638414D LN Equity2009" 5 163.834  170.8225
"1638414D LN Equity" 2010 "1638414D LN Equity2010" 5 164.699  164.2665
"1638414D LN Equity" 2011 "1638414D LN Equity2011" 5 145.875   155.287
"1638414D LN Equity" 2012 "1638414D LN Equity2012" 5 193.526  169.7005
"1638414D LN Equity" 2013 "1638414D LN Equity2013" 5 187.314    190.42
"1638414D LN Equity" 2014 "1638414D LN Equity2014" 5 209.428   198.371
"1638414D LN Equity" 2015 "1638414D LN Equity2015" 5       .   104.714
"1638414D LN Equity" 2016 "1638414D LN Equity2016" 5       .         0
"1638414D LN Equity" 2017 "1638414D LN Equity2017" 5       .         0
"1638414D LN Equity" 2018 "1638414D LN Equity2018" 5       .         0
"1655637D LN Equity" 2003 "1655637D LN Equity2003" 6  1951.4         .
"1655637D LN Equity" 2004 "1655637D LN Equity2004" 6  2336.2    2143.8
"1655637D LN Equity" 2005 "1655637D LN Equity2005" 6  3607.2    2971.7
"1655637D LN Equity" 2006 "1655637D LN Equity2006" 6  3446.8      3527
"1655637D LN Equity" 2007 "1655637D LN Equity2007" 6  3579.5   3513.15
"1655637D LN Equity" 2008 "1655637D LN Equity2008" 6  4117.8   3848.65
"1655637D LN Equity" 2009 "1655637D LN Equity2009" 6    5673    4895.4
"1655637D LN Equity" 2010 "1655637D LN Equity2010" 6  6114.6    5893.8
"1655637D LN Equity" 2011 "1655637D LN Equity2011" 6  6502.1   6308.35
"1655637D LN Equity" 2012 "1655637D LN Equity2012" 6  6514.9    6508.5
"1655637D LN Equity" 2013 "1655637D LN Equity2013" 6  6573.1      6544
"1655637D LN Equity" 2014 "1655637D LN Equity2014" 6  6675.7    6624.4
"1655637D LN Equity" 2015 "1655637D LN Equity2015" 6  7008.2   6841.95
"1655637D LN Equity" 2016 "1655637D LN Equity2016" 6 10459.7   8733.95
"1655637D LN Equity" 2017 "1655637D LN Equity2017" 6       .   5229.85
"1655637D LN Equity" 2018 "1655637D LN Equity2018" 6       .         0
"3572335Q LN Equity" 2003 "3572335Q LN Equity2003" 7 1038.04         .
"3572335Q LN Equity" 2004 "3572335Q LN Equity2004" 7 1281.37 1159.7051
"3572335Q LN Equity" 2005 "3572335Q LN Equity2005" 7 1810.69   1546.03
"3572335Q LN Equity" 2006 "3572335Q LN Equity2006" 7    2574  2192.345
end

Tags: None

Nick Cox

Join Date: Mar 2014
Posts: 35642

20 May 2019, 05:03

I can't reproduce this alleged behaviour.

Code:

. bysort fid (time): gen check = (assets + assets[_n-1]) / 2 
(45 missing values generated)

. list fid firmid_time assets_new-check , sepby(fid)

     +---------------------------------------------------------------+
     | fid              firmid_time   assets~w   avg_as~s      check |
     |---------------------------------------------------------------|
  1. |   1   0876711D LN Equity2003          .          .          . |
  2. |   1   0876711D LN Equity2004          .          0          . |
  3. |   1   0876711D LN Equity2005          .          0          . |
  4. |   1   0876711D LN Equity2006          .          0          . |
  5. |   1   0876711D LN Equity2007          .          0          . |
  6. |   1   0876711D LN Equity2008      830.7     415.35          . |
  7. |   1   0876711D LN Equity2009      714.2     772.45     772.45 |
  8. |   1   0876711D LN Equity2010      788.6      751.4      751.4 |
  9. |   1   0876711D LN Equity2011        727      757.8      757.8 |
 10. |   1   0876711D LN Equity2012      684.7     705.85     705.85 |
 11. |   1   0876711D LN Equity2013          .     342.35          . |
 12. |   1   0876711D LN Equity2014          .          0          . |
 13. |   1   0876711D LN Equity2015          .          0          . |
 14. |   1   0876711D LN Equity2016          .          0          . |
 15. |   1   0876711D LN Equity2017          .          0          . |
 16. |   1   0876711D LN Equity2018          .          0          . |
     |---------------------------------------------------------------|
 17. |   2   1218069D LN Equity2003    182.889          .          . |
 18. |   2   1218069D LN Equity2004     232.52   207.7045   207.7045 |
 19. |   2   1218069D LN Equity2005    275.615   254.0675   254.0675 |
 20. |   2   1218069D LN Equity2006    717.403    496.509    496.509 |
 21. |   2   1218069D LN Equity2007    790.272   753.8375   753.8375 |
 22. |   2   1218069D LN Equity2008    903.164    846.718    846.718 |
 23. |   2   1218069D LN Equity2009    937.341   920.2525   920.2525 |
 24. |   2   1218069D LN Equity2010    910.465    923.903    923.903 |
 25. |   2   1218069D LN Equity2011    823.901    867.183    867.183 |
 26. |   2   1218069D LN Equity2012          .   411.9505          . |
 27. |   2   1218069D LN Equity2013          .          0          . |
 28. |   2   1218069D LN Equity2014          .          0          . |
 29. |   2   1218069D LN Equity2015          .          0          . |
 30. |   2   1218069D LN Equity2016          .          0          . |
 31. |   2   1218069D LN Equity2017          .          0          . |
 32. |   2   1218069D LN Equity2018          .          0          . |
     |---------------------------------------------------------------|
 33. |   3   1334987D LN Equity2003    126.402          .          . |
 34. |   3   1334987D LN Equity2004    191.404    158.903    158.903 |
 35. |   3   1334987D LN Equity2005     216.22    203.812    203.812 |
 36. |   3   1334987D LN Equity2006    279.354    247.787    247.787 |
 37. |   3   1334987D LN Equity2007    260.482    269.918    269.918 |
 38. |   3   1334987D LN Equity2008      212.8    236.641    236.641 |
 39. |   3   1334987D LN Equity2009      245.4      229.1      229.1 |
 40. |   3   1334987D LN Equity2010      223.2      234.3      234.3 |
 41. |   3   1334987D LN Equity2011          .      111.6          . |
 42. |   3   1334987D LN Equity2012          .          0          . |
 43. |   3   1334987D LN Equity2013          .          0          . |
 44. |   3   1334987D LN Equity2014          .          0          . |
 45. |   3   1334987D LN Equity2015          .          0          . |
 46. |   3   1334987D LN Equity2016          .          0          . |
 47. |   3   1334987D LN Equity2017          .          0          . |
 48. |   3   1334987D LN Equity2018          .          0          . |
     |---------------------------------------------------------------|
 49. |   4   1561649D LN Equity2003    205.089          .          . |
 50. |   4   1561649D LN Equity2004    212.072   208.5805   208.5805 |
 51. |   4   1561649D LN Equity2005    203.671   207.8715   207.8715 |
 52. |   4   1561649D LN Equity2006    247.038   225.3545   225.3545 |
 53. |   4   1561649D LN Equity2007          .    123.519          . |
 54. |   4   1561649D LN Equity2008    690.709   345.3545          . |
 55. |   4   1561649D LN Equity2009     830.02   760.3645   760.3645 |
 56. |   4   1561649D LN Equity2010     1565.9    1197.96    1197.96 |
 57. |   4   1561649D LN Equity2011     1343.3     1454.6     1454.6 |
 58. |   4   1561649D LN Equity2012     1488.1     1415.7     1415.7 |
 59. |   4   1561649D LN Equity2013     1271.1     1379.6     1379.6 |
 60. |   4   1561649D LN Equity2014     2122.2    1696.65    1696.65 |
 61. |   4   1561649D LN Equity2015          .     1061.1          . |
 62. |   4   1561649D LN Equity2016          .          0          . |
 63. |   4   1561649D LN Equity2017          .          0          . |
 64. |   4   1561649D LN Equity2018          .          0          . |
     |---------------------------------------------------------------|
 65. |   5   1638414D LN Equity2003    145.775          .          . |
 66. |   5   1638414D LN Equity2004    145.237    145.506    145.506 |
 67. |   5   1638414D LN Equity2005     149.61   147.4235   147.4235 |
 68. |   5   1638414D LN Equity2006    221.261   185.4355   185.4355 |
 69. |   5   1638414D LN Equity2007    207.725    214.493    214.493 |
 70. |   5   1638414D LN Equity2008    177.811    192.768    192.768 |
 71. |   5   1638414D LN Equity2009    163.834   170.8225   170.8225 |
 72. |   5   1638414D LN Equity2010    164.699   164.2665   164.2665 |
 73. |   5   1638414D LN Equity2011    145.875    155.287    155.287 |
 74. |   5   1638414D LN Equity2012    193.526   169.7005   169.7005 |
 75. |   5   1638414D LN Equity2013    187.314     190.42     190.42 |
 76. |   5   1638414D LN Equity2014    209.428    198.371    198.371 |
 77. |   5   1638414D LN Equity2015          .    104.714          . |
 78. |   5   1638414D LN Equity2016          .          0          . |
 79. |   5   1638414D LN Equity2017          .          0          . |
 80. |   5   1638414D LN Equity2018          .          0          . |
     |---------------------------------------------------------------|
 81. |   6   1655637D LN Equity2003     1951.4          .          . |
 82. |   6   1655637D LN Equity2004     2336.2     2143.8     2143.8 |
 83. |   6   1655637D LN Equity2005     3607.2     2971.7     2971.7 |
 84. |   6   1655637D LN Equity2006     3446.8       3527       3527 |
 85. |   6   1655637D LN Equity2007     3579.5    3513.15    3513.15 |
 86. |   6   1655637D LN Equity2008     4117.8    3848.65    3848.65 |
 87. |   6   1655637D LN Equity2009       5673     4895.4     4895.4 |
 88. |   6   1655637D LN Equity2010     6114.6     5893.8     5893.8 |
 89. |   6   1655637D LN Equity2011     6502.1    6308.35    6308.35 |
 90. |   6   1655637D LN Equity2012     6514.9     6508.5     6508.5 |
 91. |   6   1655637D LN Equity2013     6573.1       6544       6544 |
 92. |   6   1655637D LN Equity2014     6675.7     6624.4     6624.4 |
 93. |   6   1655637D LN Equity2015     7008.2    6841.95    6841.95 |
 94. |   6   1655637D LN Equity2016    10459.7    8733.95    8733.95 |
 95. |   6   1655637D LN Equity2017          .    5229.85          . |
 96. |   6   1655637D LN Equity2018          .          0          . |
     |---------------------------------------------------------------|
 97. |   7   3572335Q LN Equity2003    1038.04          .          . |
 98. |   7   3572335Q LN Equity2004    1281.37   1159.705   1159.705 |
 99. |   7   3572335Q LN Equity2005    1810.69    1546.03    1546.03 |
100. |   7   3572335Q LN Equity2006       2574   2192.345   2192.345 |
     +---------------------------------------------------------------+

By the way, better in general to go

Code:

tsset fid time 
gen wanted = (assets_new + L1.assets_new)/2

Announcement

query on time-series function for computing average

Comment