Hi,

I have a dataset on property transactions (a sample below, for those of you from the UK, this is from Land Registry). My data contains properties that have been sold twice in the time period.

I wish to collapse the two observations per property (first and second sale) into a single observation. Some variables are obviously the same for each transaction, e.g. Prop_Type, Old_New etc, so I would like these to collapse into one (forgive me if I am loose on terminology). Other variables are different within each property however, e.g. Price, Date, and Holiday (referring to the SDLT holiday in the UK). As these are different, I would like to somehow create a new variable within the collapsed observation denoting the value taken in the second transaction of the property, if different to the value taken in the first transaction of the property (essentially a "Holiday 2" variable).

Is there a way of doing this using the collapse command or is this a little too fiddly for that?

Thanks for your help,

Jack



Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long Price str17 Date str1(Prop_Type Old_New Duration) str35 Locality str22 City str35 County byte(Freehold Leasehold) float Holiday str77 Address
345000 "08 April 2019"    "D" "N" "F" "STOKE ST MICHAEL" "RADSTOCK"   "SOMERSET"                   1 0 0 "(TUCKER)  WITHYBROOK BA3 5JQ"            
352000 "27 August 2020"   "D" "N" "F" "STOKE ST MICHAEL" "RADSTOCK"   "SOMERSET"                   1 0 1 "(TUCKER)  WITHYBROOK BA3 5JQ"            
382500 "02 December 2019" "F" "N" "L" ""                 "LONDON"     "GREATER LONDON"             0 1 0 "06-Oct FLAT 15 ABERDEEN PARK N5 2BN"     
412000 "09 November 2020" "F" "N" "L" ""                 "LONDON"     "GREATER LONDON"             0 1 1 "06-Oct FLAT 15 ABERDEEN PARK N5 2BN"     
150000 "30 July 2019"     "F" "N" "L" ""                 "FOLKESTONE" "KENT"                       0 1 0 "07-Sep FLAT 3 LANGHORNE GARDENS CT20 2EA"
180000 "16 March 2020"    "F" "N" "L" ""                 "FOLKESTONE" "KENT"                       0 1 0 "07-Sep FLAT 3 LANGHORNE GARDENS CT20 2EA"
 57001 "20 February 2020" "S" "N" "F" ""                 "HULL"       "CITY OF KINGSTON UPON HULL" 1 0 0 "1  9TH AVENUE HU6 8HU"                   
102000 "10 November 2020" "S" "N" "F" ""                 "HULL"       "CITY OF KINGSTON UPON HULL" 1 0 1 "1  9TH AVENUE HU6 8HU"                   
312500 "05 July 2019"     "D" "N" "F" "BOROUGHBRIDGE"    "YORK"       "NORTH YORKSHIRE"            1 0 0 "1  ABEL CLOSE YO51 9US"                  
299000 "05 July 2019"     "D" "N" "F" "BOROUGHBRIDGE"    "YORK"       "NORTH YORKSHIRE"            1 0 0 "1  ABEL CLOSE YO51 9US"                  
 92000 "28 January 2019"  "S" "N" "F" ""                 "LEEDS"      "WEST YORKSHIRE"             1 0 0 "1  ABERFIELD GARDENS LS10 3QL"           
115000 "08 November 2019" "S" "N" "F" ""                 "LEEDS"      "WEST YORKSHIRE"             1 0 0 "1  ABERFIELD GARDENS LS10 3QL"           
212500 "21 August 2020"   "S" "N" "F" ""                 "TROWBRIDGE" "WILTSHIRE"                  1 0 1 "1  ADCROFT DRIVE BA14 8PU"               
210000 "27 August 2020"   "S" "N" "F" ""                 "TROWBRIDGE" "WILTSHIRE"                  1 0 1 "1  ADCROFT DRIVE BA14 8PU"               
146000 "26 July 2019"     "T" "N" "F" ""                 "LEICESTER"  "LEICESTER"                  1 0 0 "1  ADELAIDE CLOSE LE4 2NZ"               
153000 "04 October 2019"  "T" "N" "F" ""                 "LEICESTER"  "LEICESTER"                  1 0 0 "1  ADELAIDE CLOSE LE4 2NZ"               
113000 "02 October 2020"  "S" "N" "F" "MILE OAK"         "TAMWORTH"   "STAFFORDSHIRE"              1 0 1 "1  AFFLECK AVENUE B78 3NJ"               
130000 "26 November 2020" "S" "N" "F" "MILE OAK"         "TAMWORTH"   "STAFFORDSHIRE"              1 0 1 "1  AFFLECK AVENUE B78 3NJ"               
end