Summary statistics per cik per year

[Patrick Raap]

Dear all,

I have a sample of panel data which contains data on a main firm: cik, a given year: year and the corresponding number of peer firms to a main firm:peercik (for each cik).

I would like to generate summary statistics (mean, median, minimum, maximum, st dev and N) for each year (my sample goes from 2006-2016).

However, I have to rearrange my data to find data I need: the mean, median, min, max, st dev, number of peers per cik per year.

I've tried the following:

sort year cik peercik
replace peercik=1
by year cik: egen npeer_cik=sum(peercik) if _n==1

However this does not work. Can anyone help me to find the average peercik statistics per year?

Best regards,
Patrick

[Nick Cox]

Your new variable is created only for the first observation in each block. There is no way that it will takes account of the values in the other observations in that block.

You didn't give a data example. Please read the entire FAQ Advice and come back with a data example. Otherwise people will be obliged to try to imagine what your data look like, and so forth.

If you had read the FAQ Advice, it would have made clear to you that "does not work" is a poor error report.

[Patrick Raap]

Dear all,

I am using egen and sum/tabstat in stata 15.1 and I am trying to get summary statistics.


I have a sample of panel data which contains data on a main firm: cik, a given year: year and the corresponding number of peer firms to a main firm:peercik (for each cik).

I would like to generate summary statistics (mean, median, minimum, maximum, st dev and N) for each year (my sample goes from 2006-2016).

However, I have to rearrange my data to find data I need: the mean, median, min, max, st dev, number of peers per cik per year.

My data looks at following:


Code:
. sum

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
cik | 206,285 662795.5 483074.7 1800 1634997
year | 206,285 2011.548 2.958477 2006 2016
grantid | 206,285 343459.5 231642.7 51 1119292
peercik | 206,285 684337.5 484767 0 1681459

* Example generated by -dataex-. To install: ssc install dataex
clear
input long cik double(year peercik)
28412 2006 1281761
28412 2006  713676
28412 2006 1399315
28412 2006   69970
28412 2006   91576
28412 2006   36270
28412 2006   49196
28412 2006  101542
28412 2006   35527
28412 2006  750556
28412 2006  109380
28412 2006   92230
28412 2006   69970
28412 2006  713676
28412 2006   92230
28412 2006   91576
28412 2006   35527
28412 2006 1281761
28412 2006  750556
28412 2006   49196
28412 2006  101542
28412 2006   36270
28412 2006  109380
28412 2006 1399315
28412 2006   92230
28412 2006  101542
28412 2006  750556
28412 2006 1399315
28412 2006  109380
28412 2006  713676
28412 2006   91576
28412 2006   35527
28412 2006   49196
28412 2006   69970
28412 2006 1281761
28412 2006   36270
28412 2006  713676
28412 2006   36270
28412 2006   92230
28412 2006   49196
28412 2006   69970
28412 2006 1399315
28412 2006   91576
28412 2006  101542
28412 2006   35527
28412 2006 1281761
28412 2006  750556
28412 2006  109380
28412 2006  750556
28412 2006   36270
28412 2006  713676
28412 2006 1281761
28412 2006  109380
28412 2006 1399315
28412 2006   49196
28412 2006   91576
28412 2006  101542
28412 2006   69970
28412 2006   92230
28412 2006   35527
48039 2006  101778
48039 2006 1325955
48039 2006  856465
48039 2006  107263
48039 2006  941548
48039 2006 1035002
48039 2006   50104
48039 2006 1066107
48039 2006  110430
48039 2006   95304
48039 2006 1135152
48039 2006  717423
48039 2006  856716
48039 2006  869087
48039 2006 1209821
48039 2006  856465
48039 2006 1066107
48039 2006  107263
48039 2006  101778
48039 2006  110430
48039 2006 1135152
48039 2006   95304
48039 2006 1325955
48039 2006  869087
48039 2006 1209821
48039 2006  856716
48039 2006  717423
48039 2006  941548
48039 2006   50104
48039 2006 1035002
48039 2006 1066107
48039 2006 1035002
48039 2006  110430
48039 2006  856716
48039 2006 1135152
48039 2006  101778
48039 2006 1209821
48039 2006  941548
48039 2006 1325955
48039 2006  717423
end

However, I need to find data on on peercik per cik per year. Therefore, I need to adjust the dataset to get the number of peers per cik per year.

Can anyone help me to find the average peercik statistics per year?

Best regards,
Patrick

[Nick Cox]

Thanks for the example data. You left off the opening CODE delimiter. You have time to edit it into your posting. I don't know what grantid is doing. You don't (seem to) mention it in #1 or #3.

This may help. In general, as the same peer can evidently appear several times for each combination of cik and year, you need to watch out for double counting.

See help for egen for more.

egen tag = tag(cik year peer)
egen total1 = total(tag), by(cik year)
bysort cik year : gen total2 = _N

----------------
          | year
      cik | 2006
----------+-----
    28412 |   12
          |   60
          |
    48039 |   15
          |   40
----------------

.

[Patrick Raap]

Thank you it worked!