Simpler way to count observation count of a variable?

[Mav Mehmood]

Hi,

I have data similar to the below where I am looking at the products sold in stores over 3 years:

Store Name	Product	Year	Store_Product
Tesco	Biscuits	2010	1
Tesco	Biscuits	2011	1
Tesco	Biscuits	2012	1
Tesco	Water Bottles	2010	2
Tesco	Water Bottles	2011	2
Tesco	Cakes	2010	3
Tesco	Cakes	2011	3
Asda	Biscuits	2010	4
Asda	Biscuits	2011	4
Asda	Water Bottles	2010	5
Asda	Cakes	2010	6
Asda	Cakes	2011	6
Asda	Cakes	2012	6
Sainsburys	Water Bottles	2010	7
Sainsburys	Water Bottles	2011	7
Sainsburys	Water Bottles	2012	7
Sainsburys	Cakes	2010	8
Sainsburys	Cakes	2011	8
Sainsburys	Cakes	2012	8
Morrisons	Biscuits	2011	9
Morrisons	Biscuits	2011	9
Morrisons	Water Bottles	2010	10
Morrisons	Cakes	2011	11

Store_Product is a new variable I have made that combines the store name and product and gives these a unique identifier. For example Biscuits in Tesco would be a combination, Biscuits in Asda would be a different combination and so on.

I want to see how many times these combinations are observed based on the time column. For example, store_product combination 1 is observed 3 times (Tesco Biscuits in 2010, 2011 and 2012), whilst store_product combination 2 is observed twice (Tesco Water Bottles 2010, 2011). I was able to obtain this via "tab product year" however this results in a long table. I`m aware of the totals after manually adding in Excel but is there a simpler way to bring this up in Stata please?

Thank you!

[Marcos Almeida]

If I understood right, you can use - count if - for that matter.

[Carlo Lazzaro]

See also -group- function in -egen-.

[Mav Mehmood]

If I understood right, you can use - count if - for that matter.

Hi Marcos,

Thank you for your reply.

I have tried using the command "count if time==2011" as well as "count

Store_Product

if time==2011" but I receive an error informed 2011 or varlist not allowed does not exist. Do I need to assign a number to my years before I attempt Count If?

Thanks

[Phil Bromiley]

bysort time: egen = count(Store_Product)

[Andrew Musau]

You also don't need a third variable. The identifiers are sufficient.

bys storename product: gen count= _N

[Mav Mehmood]

bysort time: egen = count(Store_Product)

Hi Phil,

Thank you for your response. I`ve tried this command however I receive an "invalid syntax" error. I`ve also tried amending time within the command to year but this brings back the same error.

[Nick Cox]

The problem with #5 is just a typo:

bysort time: egen somename = count(Store_Product)

I am not saying enough attention to know for sure what you want.