Count by groups and collapse

Question

I have a dataset in Stata and want to count by group (loc_ID) and year. I used the following two lines of code: egen count_obsv = tag(loc_ID year)

This adds a counter to my dataset (count_obsv) which is 1 (and 0 for every element that has the same combination of loc_ID and year) for every new combination. Then I use:

collapse (sum) count_obsv, by(loc_ID year)

according to various Stata forum posts this should result in eg.:

loc_ID  year  count_obsv
 1      2000    342
 1      2001     23
 2      2008     23
...

But my output is:

loc_ID  year  count_obsv
 1      2000     1
 1      2001     1
 2      2008     1
...

What am I summarizing wrong?

user_1820446 · Answer

When you call up the tag() function of the egen command, you assign the value 1 to just one of any number of observations with the same distinct values for the specified variables, and 0 to all the others. Then when you ask for the sum of those values in the same groups of observations, you get the group sums of one 1 and any number of 0s, and each sum is thus necessarily 1.

Your question is probably abstracted from some other calculations that worked as you expected, but if all you wanted was a dataset with frequencies, then

contract loc_ID year

would do that for you. If you wanted a dataset with summaries of other variables too, you would need something more like

collapse (count) count=foo (mean) mean=foo (sd) sd=foo, by(loc_ID year)

I doubt that any Statalist posts state otherwise. (I wrote tag() in 1999, and I am not aware of this as a misunderstanding.) There is a related but so to speak distinct problem where tag() comes in useful, which is counting distinct values (often called unique values).

sysuse auto, clear 
egen tag = tag(foreign rep78) 
egen distinct = total(tag), by(foreign) 
tabdisp foreign, c(distinct)

would be a way to get at the number of distinct values of rep78 within categories of foreign.

Count by groups and collapse

1 Answers