Sum of variable under two conditions in each year

[Samantha Will]

Hello!

I want to know how to create the sum of smallcaps ( If smallcap = 1 ) in EACH year, while having to look out for multilple fundnrs.
Basically:
fundnr month year smallcap
573 1 1990 1
719 10 1990 0
381 4 1990 0
718 10 1990 0
310 4 1990 0
573 4 1990 1
983 3 1990 1

At the end I should get: 2 smallcap firms, because fundnr 573 identifies as one firm, even if we have multiple observations in the year 1990.
So the conditions are: Smallcaps of each year (dataset goes up to 2018) while observations of the same fundnr within a year should be counted as only one

[Clyde Schechter]

by fundnr (year month), sort: egen wanted = total(cond(_n == 1, smallcap, .))

may be a solution.

What is unclear about your question is what you want to do if the same fundnr appears multiple times but is sometimes smallcap = 1 and sometimes smallcap = 0. Do you count the 1, or the 0? In this code, the value of smallcap in the observation with the earlier month is the one that will be counted. Perhaps this problem never arises in the real data (it doesn't arise in the example). If it does, and if earlier month is not your desired resolution of the inconsistency, you will need to modify the code accordingly.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Ali Atia]

I interpret the question slightly differently to #2. It seems what you are asking for is the count of firms, per year, which have at least one observation of smallcap equal to one. In this case, the count is two, because one firm has 1 appear once and another has 1 appear twice, adding up to two firms. If that interpretation is correct, use this code to generate such a count:

bysort fundnr year (smallcap): egen max = max(cond(smallcap==1,1,.)) if _n==1
bysort year: egen wanted = total(max)
list, noobs sep(0)

  +-------------------------------------------------+
  | fundnr   month   year   smallcap   max   wanted |
  |-------------------------------------------------|
  |    310       4   1990          0     .        2 |
  |    381       4   1990          0     .        2 |
  |    573       1   1990          1     1        2 |
  |    573       4   1990          1     .        2 |
  |    573       5   1990          .     .        2 |
  |    718      10   1990          0     .        2 |
  |    719      10   1990          0     .        2 |
  |    983       3   1990          1     1        2 |
  +-------------------------------------------------+

[Samantha Will]

by fundnr (year month), sort: egen wanted = total(cond(_n == 1, smallcap, .))

may be a solution.

What is unclear about your question is what you want to do if the same fundnr appears multiple times but is sometimes smallcap = 1 and sometimes smallcap = 0. Do you count the 1, or the 0? In this code, the value of smallcap in the observation with the earlier month is the one that will be counted. Perhaps this problem never arises in the real data (it doesn't arise in the example). If it does, and if earlier month is not your desired resolution of the inconsistency, you will need to modify the code accordingly.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 fundid float fundnr str12 isin float(month year) str42 name str12 esb float(mktrf smb hml mom rf mf_tna mf_mnret mf_expratio turnratio mf_m_cf age mf_max_age smallcap wanted)
"fs00008l08" 1 "US74316J7303"  7 2011 "Contravisory Strategic Equity Investor"   "Large Blend"  -2.36 -1.31 -1.23   .09   0    332705         -6 1.3751905 56          . .002737851 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  8 2011 "Contravisory Strategic Equity Investor"   "Large Blend"  -5.99 -3.06 -2.44  -.33 .01    585448   -5.95745 1.3928342 56   81.92355  .08761123 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  9 2011 "Contravisory Strategic Equity Investor"   "Large Blend"  -7.59 -3.48 -1.46 -2.57   0   3310683  -11.31222 1.1475599 56   476.8079   .1724846 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 10 2011 "Contravisory Strategic Equity Investor"   "Large Blend"  11.35  3.41  -.17 -1.43   0   4415153  11.840167  1.447985 56   21.52062  .25462013 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 11 2011 "Contravisory Strategic Equity Investor"   "Large Blend"   -.28  -.17  -.35  3.99   0   4469605 -1.3491888 1.2764448 56   2.582487   .3394935 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 12 2011 "Contravisory Strategic Equity Investor"   "Large Blend"    .74  -.71  1.74  1.93   0   4788197   .4632918 1.3071272 56   6.664676    .421629 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  1 2012 "Contravisory Strategic Equity Investor"   "Large Blend"   5.05  2.15 -1.09 -7.92   0   5309536  2.5078635 1.3421843 68   8.380138   .5065024 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  2 2012 "Contravisory Strategic Equity Investor"   "Large Blend"   4.42 -1.75   .09  -.31   0   5536767   2.922143   1.35023 68   1.357534  .59137577 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  3 2012 "Contravisory Strategic Equity Investor"   "Large Blend"   3.11  -.61   .87  1.51   0   6181172   3.088548  1.362856 68   8.550103   .6707734 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  4 2012 "Contravisory Strategic Equity Investor"   "Large Blend"   -.85  -.52  -.47  3.83   0   6107277    1.48305  1.336453 68  -2.678535   .7556468 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  5 2012 "Contravisory Strategic Equity Investor"   "Large Blend"  -6.19   .02  -.62  6.62 .01   6197676   -4.17537  1.260545 68   5.655555   .8377823 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  6 2012 "Contravisory Strategic Equity Investor"   "Large Blend"   3.89   .77   .44 -1.13   0   6568930    3.48584 1.3640442 68   2.504373   .9226557 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  7 2012 "Contravisory Strategic Equity Investor"   "Large Blend"    .79 -2.58  -.25  3.13   0   6547141    -.21053  1.314205 68 -.12116786  1.0047913 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  8 2012 "Contravisory Strategic Equity Investor"   "Large Blend"   2.55   .41  1.28 -2.48 .01   6896009      .7384 1.3261138 68  4.5901546  1.0896646 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  9 2012 "Contravisory Strategic Equity Investor"   "Large Blend"   2.73    .5  1.52 -1.02 .01   7094395  2.2760293 1.3473864 68    .600794   1.174538 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 10 2012 "Contravisory Strategic Equity Investor"   "Large Blend"  -1.76 -1.14  3.79   .09 .01   7049998   -.921438 1.3053206 68  .29563415  1.2566735 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 11 2012 "Contravisory Strategic Equity Investor"   "Large Blend"    .78   .59  -.96   .38 .01   7386680   1.446699 1.3403924 68   3.328934   1.341547 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 12 2012 "Contravisory Strategic Equity Investor"   "Large Blend"   1.18  1.47  3.55 -2.94 .01   7535032  .51222557 1.3270712 68  1.4961462  1.4236825 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  1 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   5.57   .39   .92 -1.86   0   7967994   5.196007 1.1563517 64  .54998076  1.5085558 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  2 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   1.29  -.45     0   1.3   0   8394749   .8044029 1.1051327 64   4.551462  1.5934292 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  3 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   4.03   .79  -.26  1.95   0   8797938  4.1279926 1.1424254 64   .6748788   1.670089 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  4 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   1.55 -2.44   .59   .24   0   9055238  2.4800675 1.1244211 64   .4444814  1.7549623 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  5 2013 "Contravisory Strategic Equity Investor"   "Large Blend"    2.8  1.67  2.55 -1.81   0   9234421  1.9914284 1.1193599 64 -.01265101   1.837098 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  6 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   -1.2  1.22  -.19    .6   0   9048571  -2.662564 1.0686921 64   .6499855  1.9219712 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  7 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   5.65  1.86   .55  1.72   0   9685646   5.379548 1.1534721 64  1.6610664  2.0041068 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  8 2013 "Contravisory Strategic Equity Investor"   "Large Blend"  -2.71    .3 -2.78   .03   0   9636066  -3.460949 1.0550116 64   2.949057  2.0889802 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  9 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   3.77  2.94 -1.18  3.01   0  10393426  4.4599648 1.1388398 64   3.399674  2.1738536 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 10 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   4.18 -1.49  1.15   .16   0  11854977   4.118799 1.1529814 64   9.943465   2.255989 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 11 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   3.12  1.24   .24   .41   0  12046746  1.3186208  1.121757 64   .2990036  2.3408625 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 12 2013 "Contravisory Strategic Equity Investor"   "Large Blend"   2.81   -.5   -.3   .07   0  12355125  2.9396176 1.1389996 64  -.3797645   2.422998 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  1 2014 "Contravisory Strategic Equity Investor"   "Large Blend"  -3.32   .87 -2.08   1.7   0  11812615   -3.32604 1.0410589 88  -1.064931  2.5078714 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  2 2014 "Contravisory Strategic Equity Investor"   "Large Blend"   4.65   .33  -.39  2.07   0  12347357  4.2795906 1.1213107 88  .24728173  2.5927446 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  3 2014 "Contravisory Strategic Equity Investor"   "Large Blend"    .43 -1.89  5.09 -3.31   0  12179222   -2.73598 1.0465432 88   1.374272  2.6694045 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  4 2014 "Contravisory Strategic Equity Investor"   "Large Blend"   -.19 -4.25  1.14 -3.87   0  11571634 -1.5979946 1.0623823 88  -3.390731   2.754278 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  5 2014 "Contravisory Strategic Equity Investor"   "Large Blend"   2.06 -1.83  -.26  1.14   0  11895378   2.438273 1.1066046 88    .359465  2.8364134 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  6 2014 "Contravisory Strategic Equity Investor"   "Large Blend"   2.61  3.06  -.74    .7   0  12090967  2.0519252 1.1021539 88  -.4076815   2.921287 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  7 2014 "Contravisory Strategic Equity Investor"   "Large Blend"  -2.04 -4.23   .01  -.24   0  11905682  -.4278609 1.0762048 88 -1.1045641   3.003422 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  8 2014 "Contravisory Strategic Equity Investor"   "Large Blend"   4.24   .37  -.57   .81   0  12476517   5.492631 1.1417296 88   -.697988   3.088296 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  9 2014 "Contravisory Strategic Equity Investor"   "Large Blend"  -1.97 -3.82 -1.22   .51   0  12041317  -2.679913 1.0540682 88  -.8082402   3.173169 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 10 2014 "Contravisory Strategic Equity Investor"   "Large Blend"   2.52  4.23 -1.69   .02   0  12372551    2.88534 1.1136719 88 -.13452792  3.2553046 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 11 2014 "Contravisory Strategic Equity Investor"   "Large Blend"   2.55 -2.06 -2.99  1.03   0  12799221  2.1411743 1.1056045 88  1.3073466   3.340178 3.7535934 0 0
"fs00008l08" 1 "US74316J7303" 12 2014 "Contravisory Strategic Equity Investor"   "Large Blend"   -.06  2.54  2.07  1.08   0  12430416  -.7996011 1.0752214 88 -2.0818634  3.4223135 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  1 2015 "Contravisory Strategic Equity Investor"   "Large Blend"  -3.11  -.57 -3.47  3.86   0  11578330  -2.498645  1.060982  .  -4.356202   3.507187 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  2 2015 "Contravisory Strategic Equity Investor"   "Large Blend"   6.13   .53 -1.77 -2.86   0  12507539   5.210761 1.1440629  .  2.8146536    3.59206 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  3 2015 "Contravisory Strategic Equity Investor"   "Large Blend"  -1.12  3.05  -.45  2.97   0  12236630  -1.489463 1.0710317  .  -.6765027    3.66872 3.7535934 0 0
"fs00008l08" 1 "US74316J7303"  4 2015 "Contravisory Strategic Equity Investor"   "Large Blend"    .59 -2.97  1.85 -7.37   0   1992258  .02187492  1.193412  .  -83.74078  3.7535934 3.7535934 0 0
"fs00008l0w" 2 "US00766Y4897"  8 2011 "Westfield Capital Large Cap Growth Instl" "Large Growth" -5.99 -3.06 -2.44  -.33 .01  87244584  -9.441621  .7702844 36          .  .05201916  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  9 2011 "Westfield Capital Large Cap Growth Instl" "Large Growth" -7.59 -3.48 -1.46 -2.57   0  84169200   -8.07175  .7820673 36  4.5467353  .13689254  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 10 2011 "Westfield Capital Large Cap Growth Instl" "Large Growth" 11.35  3.41  -.17 -1.43   0 100448240   12.36094  .9575397 36   6.979911  .21902806  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 11 2011 "Westfield Capital Large Cap Growth Instl" "Large Growth"  -.28  -.17  -.35  3.99   0 105600840   -3.03359  .8254303 36  8.1631975   .3039014  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 12 2011 "Westfield Capital Large Cap Growth Instl" "Large Growth"   .74  -.71  1.74  1.93   0 105889224 -.38230005  .8480377 36   .6553888    .386037  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  1 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"  5.05  2.15 -1.09 -7.92   0 114061704    6.62177  .9077219 82  1.0961826   .4709103  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  2 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"  4.42 -1.75   .09  -.31   0 116286928    4.84211  .8925307 82  -2.891215   .5557837  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  3 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"  3.11  -.61   .87  1.51   0 122008768    3.41365  .8803883 82  1.5067998   .6351814  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  4 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"  -.85  -.52  -.47  3.83   0 151342272    -.87379  .8919228 82   24.91592   .7200547  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  5 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth" -6.19   .02  -.62  6.62 .01 139329360   -7.63957  .8308649 82  -.2980085   .8021903  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  6 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"  3.89   .77   .44 -1.13   0 140861424    1.59067  .8646573 82  -.4910712   .8870637  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  7 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"   .79 -2.58  -.25  3.13   0 141863920     .93946  .8591354 82 -.22777045   .9691992  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  8 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"  2.55   .41  1.28 -2.48 .01 147010192    4.55016   .889759 82  -.9225484  1.0540725  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  9 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"  2.73    .5  1.52 -1.02 .01 150647104  3.3629575  .8792519 82  -.8890392  1.1389459  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 10 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth" -1.76 -1.14  3.79   .09 .01 143428512   -3.44493  .8215248 82 -1.3467927  1.2210815  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 11 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"   .78   .59  -.96   .38 .01 141535680  1.8829876 1.0198814 82  -3.202692  1.3059548  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 12 2012 "Westfield Capital Large Cap Growth Instl" "Large Growth"  1.18  1.47  3.55 -2.94 .01 140473664  1.3495474 1.0144837 82 -2.0998995  1.3880904  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  1 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  5.57   .39   .92 -1.86   0 156159856   6.116453 1.0622802 71   5.050189  1.4729637  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  2 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  1.29  -.45     0   1.3   0 163146032   .5488696 1.0064795 71   3.924864   1.557837  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  3 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  4.03   .79  -.26  1.95   0 165389888   2.638819 1.0274858 71  -1.263452   1.634497  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  4 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  1.55 -2.44   .59   .24   0 165520800   .6204902 1.0072029 71  -.5413366  1.7193702  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  5 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"   2.8  1.67  2.55 -1.81   0 173733088   3.436172 1.0354104 71  1.5253117  1.8015058  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  6 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  -1.2  1.22  -.19    .6   0 168639840 -1.9591722  .9812914 71  -.9724789  1.8863792  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  7 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  5.65  1.86   .55  1.72   0 172776848    5.99483 1.0610029 71 -3.5416684  1.9685147  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  8 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth" -2.71    .3 -2.78   .03   0 170596928  -.9836019  .9912707 71  -.2780949   2.053388  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  9 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  3.77  2.94 -1.18  3.01   0 178086480   4.884068 1.0498484 71  -.4938647  2.1382616  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 10 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  4.18 -1.49  1.15   .16   0 193704496  4.1830773 1.0428747 71   4.586828   2.220397  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 11 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  3.12  1.24   .24   .41   0 200032736   3.030184  .8766641 71  .23677163  2.3052704  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 12 2013 "Westfield Capital Large Cap Growth Instl" "Large Growth"  2.81   -.5   -.3   .07   0 204117072   3.427718  .8800923 71  -1.385884   2.387406  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  1 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth" -3.32   .87 -2.08   1.7   0 199165248  -2.562135  .8292495 49  .13616227   2.472279  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  2 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"  4.65   .33  -.39  2.07   0 204216000    5.49103  .8976938 49  -2.955069   2.557153  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  3 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"   .43 -1.89  5.09 -3.31   0 197005888 -1.9795718   .834199 49 -1.5510585  2.6338124  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  4 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"  -.19 -4.25  1.14 -3.87   0 192773888  -.7477948  .8446538 49 -1.4003643   2.718686  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  5 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"  2.06 -1.83  -.26  1.14   0 199261040   3.089635  .8772959 49  .27552602   2.800821  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  6 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"  2.61  3.06  -.74    .7   0 202175984  2.1929486  .8697405 49  -.7300715   2.885695  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  7 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth" -2.04 -4.23   .01  -.24   0 197467552 -1.4307023  .8387783 49  -.8981757    2.96783  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  8 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"  4.24   .37  -.57   .81   0 202034368  3.7009826  .8823615 49 -1.3882908  3.0527036  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  9 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth" -1.97 -3.82 -1.22   .51   0 196861248 -1.3996656  .8390329 49 -1.1608492   3.137577  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 10 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"  2.52  4.23 -1.69   .02   0 205145072  2.8388646  .8751618 49   1.369086  3.2197125  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 11 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"  2.55 -2.06 -2.99  1.03   0 213074560  3.1056645  .8877881 49   .7596431   3.304586  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 12 2014 "Westfield Capital Large Cap Growth Instl" "Large Growth"  -.06  2.54  2.07  1.08   0 202054384  -.9094238  .8531166 49 -4.2625575  3.3867214  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  1 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth" -3.11  -.57 -3.47  3.86   0 198214128  -2.934771  .8357028 82  1.0341657   3.471595  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  2 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth"  6.13   .53 -1.77 -2.86   0 212148640   7.105071  .9221092 82  -.0750414   3.556468  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  3 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth" -1.12  3.05  -.45  2.97   0 209169472  -1.199782  .8505788 82 -.20450144   3.633128  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  4 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth"   .59 -2.97  1.85 -7.37   0 202369072   -.428566    .85719 82  -2.822578  3.7180014  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  5 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth"  1.36   .94 -1.33  5.97   0 203269840   1.362967  .8726852 82  -.9178555   3.800137  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  6 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth" -1.53  2.81  -.81   3.1   0 198188672  -1.203177  .8504755 82 -1.2965387    3.88501  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  7 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth"  1.54 -4.15 -4.14 10.29   0 204622400    3.58163  .8917611 82  -.3353655   3.967146  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  8 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth" -6.04   .49  2.69 -2.08   0 197307392  -6.224092  .8073637 82   2.649211   4.052019  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  9 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth" -3.08 -2.64   .53  5.31   0 186629120  -5.088461  .8170843 82 -.32353655  4.1368923  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 10 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth"  7.75 -1.98  -.09 -3.94   0 202451872   8.546943  .9344718 82  -.0687631   4.219028  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 11 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth"   .56  3.64  -.51  2.27   0 203256080    1.36005  .8623444 82  -.9628157  4.3039017  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897" 12 2015 "Westfield Capital Large Cap Growth Instl" "Large Growth" -2.17 -2.81 -2.57  3.42 .01 198461248  -1.198936   .840619 82 -1.1600745   4.386037  6.885695 0 0
"fs00008l0w" 2 "US00766Y4897"  1 2016 "Westfield Capital Large Cap Growth Instl" "Large Growth" -5.77 -3.39   2.1  1.43 .01 183107792  -6.855882   .792486 63  -.8803672  4.4709105  6.885695 0 0
end

Thank you! This is really helpful. Wanted appears to be the same as the variable 'smallcap'. For creating the variable smallcap I used 'gen smallcap = substr(esb,1,5)=="Small" '

[Samantha Will]

I interpret the question slightly differently to #2. It seems what you are asking for is the count of firms, per year, which have at least one observation of smallcap equal to one. In this case, the count is two, because one firm has 1 appear once and another has 1 appear twice, adding up to two firms. If that interpretation is correct, use this code to generate such a count:

bysort fundnr year (smallcap): egen max = max(cond(smallcap==1,1,.)) if _n==1
bysort year: egen wanted = total(max)
list, noobs sep(0)

+-------------------------------------------------+
| fundnr month year smallcap max wanted |
|-------------------------------------------------|
| 310 4 1990 0 . 2 |
| 381 4 1990 0 . 2 |
| 573 1 1990 1 1 2 |
| 573 4 1990 1 . 2 |
| 573 5 1990 . . 2 |
| 718 10 1990 0 . 2 |
| 719 10 1990 0 . 2 |
| 983 3 1990 1 1 2 |
+-------------------------------------------------+

Thank you! This seems to be working. Exactly if the fundnr is the same within a year (as it was with 573) and the smallcap is 1, it will automatically enlargen my number of smallcap firms, if i dont differentiate between the fundnr from the beginning, thus providing me with a false number of smallcap firms. There is also no possibility for a firm to appear as 1 in smallcap in one year and as 0 in another.