Creating duplicate observations to compare as variables in Stata

Question

I have a database with different products per city. They are listed as single observations. Instead, I want it to be like an observation that compares two products in different cities. My actual database looks as follows:

Date Product Market Price  
 A      X       H     10
 A      X       I     5
 A      X       J     7

And I want it to look like this:

Date Product Market1 Market2 Price1 Price2
 A      X       H       I      10     5
 A      X       H       J      10     7     
 A      X       I       J       5     7

Thanks!

Answer 1

This should get you all the way there. The basic idea is form all pairwise combinations within groups with joinby on an almost identical copy of the data, and then drop self matches and duplicates.

clear

input str1(Date Product Market) byte(Price)  
A X H 10
A X I 5
A X J 7
end

rename (Market Price) =2
tempfile markets
save "`markets'"
rename *2 *1
joinby Date Product using "`markets'"
drop if Market1>=Market2 // drop duplicates
order Date Product Market1 Market2 Price1 Price2
drop if Market1==Market2
list