Keep only matching values

[Ele Kol]

I have a dataset that lists all products sold in a one year period in two stores. The quantity sold is aggregated on a monthly basis. A product is normally observed many times. I want to drop all observations of products that are only sold in one of the stores. I only want to keep product_ID's that are sold in both stores. When it was sold is not a matter.

My key variables are month, product_ID, dummy variable Store.

If would be very happy if someone could help me.


Example data:

Month | Product_ID | Store
------------------------------------
1 | 1000 | 0
1 | 1000 | 0
2 | 1001 | 0
7 | 1002 | 0
8 | 1002 | 0
1 | 1000 | 1
1 | 1003 | 1
5 | 1001 | 1
5 | 1003 | 1


If this was my enitre data set I would like to drop all observartions of product_ID 1002 and 1003

[Felix Bittmann]

From your data example I think you want this here:

bysort product_ID: egen sd = sd(store)
drop if sd == 0

[Ele Kol]

Thanks for your help Felix. I tried your code but it seems that it only drops observations of products that are in store B and not in A, but not the other way around (observations of products that are in store A but not in B). Do you have any tips?

[Felix Bittmann]

The idea of the code shown is to check whether one store has a constant value of store for all its occurrences. If this is the case then it is only sold at one store and dropped. This should work for both your cases so the order of A and B should not matter. From the data, what is the difference between entry 1 and 2. They are identical yet contained 2 times? Is there a reason for this?

[Ele Kol]

Ok, I see.

There is a typo in the example data. Entry 1 and 2 should be different months, for example like this:

Month | Product_ID | Store
------------------------------------
1 | 1000 | 0
2 | 1000 | 0
3 | 1001 | 0
7 | 1002 | 0
8 | 1002 | 0
1 | 1000 | 1
1 | 1003 | 1
5 | 1001 | 1
5 | 1003 | 1

Thank you for your help so far!

[Ele Kol]

I managed to do what I wanted by the code you sent now. Thank you very much Felix