CODEWITHSUNDEEP
stata
TiagoCC
TiagoCC

Reputation: 40

Creating duplicate observations to compare as variables in Stata

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!

Upvotes: 1

Views: 238

Answers (1)

dimitriy
dimitriy

Reputation: 9460

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

Upvotes: 3

Related Questions