Count the number of observation by group (2 variables)

[Rochelle Zhang]

Dear Statalisters,

This might be too easy for some of you, but I got stuck with coding:

I want to count the number of different firmid by each group of sic3 and year, so for 111, 1970 pair, the number of different firmid is 2; for 111, 1971 pair, the count is 3.

input sic3 year firmid
111 1970 2122
111 1970 2123
111 1971 2122
111 1971 2211
111 1971 2235
113 1970 3122
113 1970 3198
113 1970 4022
end

Thanks,
Rochelle

[William Lisowski]

Note that the collapse command replaces your data in memory with the results.

. clear

. input sic3 year firmid

          sic3       year     firmid
  1. 111 1970 2122
  2. 111 1970 2123
  3. 111 1971 2122
  4. 111 1971 2211
  5. 111 1971 2235
  6. 113 1970 3122
  7. 113 1970 3198
  8. 113 1970 4022
  9. end

. egen firm = tag(sic3 year firmid)

. collapse  (sum) firm, by(sic3 year)

. list

     +--------------------+
     | sic3   year   firm |
     |--------------------|
  1. |  111   1970      2 |
  2. |  111   1971      3 |
  3. |  113   1970      3 |
     +--------------------+

.

[Nick Cox]

It can be done in place too:

 
clear 

input sic3 year firmid
111 1970 2122
111 1970 2123
111 1971 2122
111 1971 2211
111 1971 2235
113 1970 3122
113 1970 3198
113 1970 4022
end

bysort sic3 year firmid : gen tag = _n == 1 

by sic3 year : gen wanted = sum(tag) 

by sic3 year : replace wanted = wanted[_N] 

by sic3 year : replace tag = _n == 1 

list if tag 

     +-------------------------------------+
     | sic3   year   firmid   tag   wanted |
     |-------------------------------------|
  1. |  111   1970     2122     1        2 |
  3. |  111   1971     2122     1        3 |
  6. |  113   1970     3122     1        3 |
     +-------------------------------------+

See also help egen for its tag() and total() functions and

SJ-8-4 dm0042 . . . . . . . . . . . . Speaking Stata: Distinct observations
(help distinct if installed) . . . . . . N. J. Cox and G. M. Longton
Q4/08 SJ 8(4):557--568
shows how to answer questions about distinct observations
from first principles; provides a convenience command
http://www.stata-journal.com/sjpdf.h...iclenum=dm0042

[Rochelle Zhang]

Many Thanks to William and Nick !!!

-Rochelle