Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    How to construct some summary statistics for panel data

    hi all,

    I have this panel data which reports the customer-supplier linked firm with variables date, stic(ticker of supplier), ctic(ticker of customer), and siccd_sup which reports the industry category for the supplier and finally siccd which reports the industry of the customer.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input long sdate str8(stic ctic) double(siccd_sup siccd)
15736 "A"    "HPQ"  3825 3571
15764 "A"    "HPQ"  3825 3571
15795 "A"    "HPQ"  3825 3571
15825 "A"    "HPQ"  3825 3571
15855 "A"    "HPQ"  3825 3571
15886 "A"    "HPQ"  3825 3571
15917 "A"    "HPQ"  3825 3571
15946 "A"    "HPQ"  3825 3571
15978 "A"    "HPQ"  3825 3571
16009 "A"    "HPQ"  3825 3571
16037 "A"    "HPQ"  3825 3571
16070 "A"    "HPQ"  3825 3571
16100 "A"    "HPQ"  3825 3571
16128 "A"    "HPQ"  3825 3571
16161 "A"    "HPQ"  3825 3571
16191 "A"    "HPQ"  3825 3571
16219 "A"    "HPQ"  3825 3571
16252 "A"    "HPQ"  3825 3571
16282 "A"    "HPQ"  3825 3571
16314 "A"    "HPQ"  3825 3571
16344 "A"    "HPQ"  3825 3571
16373 "A"    "HPQ"  3825 3571
16405 "A"    "HPQ"  3825 3571
16436 "A"    "HPQ"  3825 3571
19023 "AAC"  "AET"  9999 6324
19052 "AAC"  "AET"  9999 6324
19082 "AAC"  "AET"  9999 6324
19113 "AAC"  "AET"  9999 6324
19144 "AAC"  "AET"  9999 6324
19173 "AAC"  "AET"  9999 6324
19205 "AAC"  "AET"  9999 6324
19236 "AAC"  "AET"  9999 6324
19264 "AAC"  "AET"  9999 6324
19297 "AAC"  "AET"  9999 6324
19996 "AAC"  "AET"     . 6324
20027 "AAC"  "AET"  6799 6324
20055 "AAC"  "AET"  6799 6324
20088 "AAC"  "AET"  6799 6324
20118 "AAC"  "AET"  6799 6324
20146 "AAC"  "AET"  6799 6324
20178 "AAC"  "AET"  6799 6324
20208 "AAC"  "AET"  6799 6324
20237 "AAC"  "AET"  6799 6324
20269 "AAC"  "AET"  6799 6324
20300 "AAC"  "AET"  6799 6324
20331 "AAC"  "AET"  6799 6324
20361 "AAC"  "AET"  6799 6324
20391 "AAC"  "AET"  6799 6324
20422 "AAC"  "AET"  6799 6324
20453 "AAC"  "AET"  6799 6324
20482 "AAC"  "AET"  6799 6324
20513 "AAC"  "AET"  6799 6324
20544 "AAC"  "AET"  6799 6324
20573 "AAC"  "AET"  6799 6324
20605 "AAC"  "AET"  6799 6324
20635 "AAC"  "AET"  6799 6324
20664 "AAC"  "AET"  6799 6324
20697 "AAC"  "AET"  6799 6324
20727 "AAC"  "AET"  6799 6324
20758 "AAC"  "AET"  6799 6324
20788 "AAC"  "AET"  6799 6324
20818 "AAC"  "AET"  6799 6324
20088 "AAC"  "ANTM" 6799 6324
20118 "AAC"  "ANTM" 6799 6324
20146 "AAC"  "ANTM" 6799 6324
20178 "AAC"  "ANTM" 6799 6324
20208 "AAC"  "ANTM" 6799 6324
20237 "AAC"  "ANTM" 6799 6324
20269 "AAC"  "ANTM" 6799 6324
20300 "AAC"  "ANTM" 6799 6324
20331 "AAC"  "ANTM" 6799 6324
20361 "AAC"  "ANTM" 6799 6324
20391 "AAC"  "ANTM" 6799 6324
20422 "AAC"  "ANTM" 6799 6324
20453 "AAC"  "ANTM" 6799 6324
20482 "AAC"  "ANTM" 6799 6324
20513 "AAC"  "ANTM" 6799 6324
20544 "AAC"  "ANTM" 6799 6324
20573 "AAC"  "ANTM" 6799 6324
20605 "AAC"  "ANTM" 6799 6324
20635 "AAC"  "ANTM" 6799 6324
20664 "AAC"  "ANTM" 6799 6324
20697 "AAC"  "ANTM" 6799 6324
20727 "AAC"  "ANTM" 6799 6324
20758 "AAC"  "ANTM" 6799 6324
20788 "AAC"  "ANTM" 6799 6324
20818 "AAC"  "ANTM" 6799 6324
19754 "AAMC" "RESI" 6162 6162
19782 "AAMC" "RESI" 6162 6162
19813 "AAMC" "RESI" 6162 6162
19843 "AAMC" "RESI" 6162 6162
19873 "AAMC" "RESI" 6162 6162
19904 "AAMC" "RESI" 6162 6162
19935 "AAMC" "RESI" 6162 6162
19964 "AAMC" "RESI" 6162 6162
19996 "AAMC" "RESI" 6162 6162
20027 "AAMC" "RESI" 6162 6162
20055 "AAMC" "RESI" 6162 6162
20088 "AAMC" "RESI" 6162 6162
20118 "AAMC" "RESI" 6162 6162
end
format %d sdate
    For this dataset, I want to construct some basic summary statistics like:
    - number of customers in the sample per year
    - number of suppliers in the sample per year
    - average customer-supplier relationship duration (in years)

    I tried codes like
    Code:
    by yr: egen xyz=group(stic ctic)
    where yr was cosntructed from the date variable (gen yr=year(sdate)

    and I also tried working with duplicates. But Im really just trying things without knowing exactly what I am doing.

    Could someone perhaps help me in a better direction?
    Thansk in advance
    Tags: None
  • #2
    For the first two (assuming no missing values are present in the variables "ctic" and "stic"), you can count as follows:

    Code:
    gen year= year(sdate)
*CUSTOMERS IN THE SAMPLE IN A YEAR
bys year (ctic): egen customers=sum(ctic[_n]!=ctic[_n+1])
*SUPPLIERS IN THE SAMPLE IN A YEAR
bys year (stic): egen suppliers=sum(stic[_n]!=stic[_n+1])
    average customer-supplier relationship duration (in years)
    The following will give you the observed duration of the relationship.

    Code:
    egen cs= group(stic ctic), label
*IN DAYS
bys cs (sdate): gen duration_days= sdate[_N]- sdate[1] +1
*IN YEARS
bys cs (year): gen duration_years= year[_N]- year[1] +1

    Res.:


    Code:
    . sort sdate

. l, sepby(year)

     +-----------------------------------------------------------------------------------------------------------+
     |     sdate   stic   ctic   siccd_~p   siccd   year   custom~s   suppli~s          cs   durat~ys   durat~rs |
     |-----------------------------------------------------------------------------------------------------------|
  1. | 31jan2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
  2. | 28feb2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
  3. | 31mar2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
  4. | 30apr2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
  5. | 30may2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
  6. | 30jun2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
  7. | 31jul2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
  8. | 29aug2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
  9. | 30sep2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
 10. | 31oct2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
 11. | 28nov2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
 12. | 31dec2003      A    HPQ       3825    3571   2003          1          1       A HPQ        701          2 |
     |-----------------------------------------------------------------------------------------------------------|
 13. | 30jan2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 14. | 27feb2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 15. | 31mar2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 16. | 30apr2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 17. | 28may2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 18. | 30jun2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 19. | 30jul2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 20. | 31aug2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 21. | 30sep2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 22. | 29oct2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 23. | 30nov2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
 24. | 31dec2004      A    HPQ       3825    3571   2004          1          1       A HPQ        701          2 |
     |-----------------------------------------------------------------------------------------------------------|
 25. | 31jan2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 26. | 29feb2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 27. | 30mar2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 28. | 30apr2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 29. | 31may2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 30. | 29jun2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 31. | 31jul2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 32. | 31aug2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 33. | 28sep2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
 34. | 31oct2012    AAC    AET       9999    6324   2012          1          1     AAC AET       1796          5 |
     |-----------------------------------------------------------------------------------------------------------|
 35. | 31jan2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 36. | 28feb2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 37. | 31mar2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 38. | 30apr2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 39. | 30may2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 40. | 30jun2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 41. | 31jul2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 42. | 29aug2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 43. | 30sep2014    AAC    AET          .    6324   2014          3          2     AAC AET       1796          5 |
 44. | 30sep2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 45. | 31oct2014    AAC    AET       6799    6324   2014          3          2     AAC AET       1796          5 |
 46. | 31oct2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 47. | 28nov2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
 48. | 28nov2014    AAC    AET       6799    6324   2014          3          2     AAC AET       1796          5 |
 49. | 31dec2014    AAC    AET       6799    6324   2014          3          2     AAC AET       1796          5 |
 50. | 31dec2014    AAC   ANTM       6799    6324   2014          3          2    AAC ANTM        731          3 |
 51. | 31dec2014   AAMC   RESI       6162    6162   2014          3          2   AAMC RESI        365          2 |
     |-----------------------------------------------------------------------------------------------------------|
 52. | 30jan2015   AAMC   RESI       6162    6162   2015          3          2   AAMC RESI        365          2 |
 53. | 30jan2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 54. | 30jan2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 55. | 27feb2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 56. | 27feb2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 57. | 31mar2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 58. | 31mar2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 59. | 30apr2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 60. | 30apr2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 61. | 29may2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 62. | 29may2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 63. | 30jun2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 64. | 30jun2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 65. | 31jul2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 66. | 31jul2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 67. | 31aug2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 68. | 31aug2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 69. | 30sep2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 70. | 30sep2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 71. | 30oct2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 72. | 30oct2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 73. | 30nov2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 74. | 30nov2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
 75. | 31dec2015    AAC   ANTM       6799    6324   2015          3          2    AAC ANTM        731          3 |
 76. | 31dec2015    AAC    AET       6799    6324   2015          3          2     AAC AET       1796          5 |
     |-----------------------------------------------------------------------------------------------------------|
 77. | 29jan2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 78. | 29jan2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 79. | 29feb2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 80. | 29feb2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 81. | 31mar2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 82. | 31mar2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 83. | 29apr2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 84. | 29apr2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 85. | 31may2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 86. | 31may2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 87. | 30jun2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 88. | 30jun2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 89. | 29jul2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 90. | 29jul2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 91. | 31aug2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 92. | 31aug2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 93. | 30sep2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 94. | 30sep2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 95. | 31oct2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 96. | 31oct2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 97. | 30nov2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
 98. | 30nov2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
 99. | 30dec2016    AAC    AET       6799    6324   2016          2          1     AAC AET       1796          5 |
100. | 30dec2016    AAC   ANTM       6799    6324   2016          2          1    AAC ANTM        731          3 |
     +-----------------------------------------------------------------------------------------------------------+
    Last edited by Andrew Musau; 31 Oct 2019, 15:17.

    Comment

    Previous Next
    Working...
    X