Identify Subgroups wtihin Groups

[Bertille Mezaguer]

Hello, I am studying a dataset of commercial real estate transactions. Some transaction span across multiple observations/addresses but represent one unique transaction. In the example below, all 11 parcels were bought for $25M.

If I were to sum the portfolioprice without a filter, it would not be accurate, so I created a variable called "uniquetransaction" that is a unique identifier for each transaction.
It allows me to do something like "sum portfolioprice if uniquetransaction==1" and I would get an accurate result.

Here is how I obtained uniquetransaction:

bysort trueowner lastsaledate portfolioprice (propertytype): gen uniquetransaction = _n
replace uniquetransaction = . if uniquetransaction!=1


However, now I run into a 2nd issue. Some transactions have multiple property types, like the one below.
I would like to create the "uniquepropertytransaction" variable, that identifies the first occurence of a property type within each transaction, but I can't figure out how.
This is so I could complete commands such as "sum portfolioprice if propertytype=="Land" & uniquepropertytransaction==1

Please note the transactions are grouped by trueowner, lastsaledate and portfolioprice and could be sorted based on them folio number or address.

Could you please help me? Thank you.

uniquepropertytransaction	uniquetransaction	propertytype	folio	address	trueowner	lastsaledate	portfolioprice
1	1	Office	3136491240853	240 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
1		MultiFamily	3136491240855	242 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
		MultiFamily	3136491240857	244 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
		MultiFamily	3136491240859	246 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
1		Land	3136491240861	248 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
		Land	3136491240863	250 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
		Land	3136491240865	252 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
		Land	3136491240867	254 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
		Land	3136491240869	256 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
		Land	3136491240871	258 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800
		Land	3136491240873	260 NW 29 ST	AVENTURA MIXED USE PROPERTY LLC	2/10/22	$25,384,800

[George Ford]

Maybe pull off the first 8 digits or so of portfolio or use address in the unique line?

[Daniel Schaefer]

Welcome to the forum. I might slightly modify the way you create a "unique identifier" for each transaction. What you have here isn't a unique identifier exactly, since you don't have a separate (unique) number for each transaction. I might modify your code like so:

clear
input str20(propertytype    folio    address    trueowner    lastsaledate    portfolioprice)
"Office"    "3136491240853"    "240 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"MultiFamily"    "3136491240855"    "242 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"MultiFamily"    "3136491240857"    "244 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"MultiFamily"    "3136491240859"    "246 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"Land"    "3136491240861"    "248 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"Land"    "3136491240863"    "250 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"Land"    "3136491240865"    "252 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"Land"    "3136491240867"    "254 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"Land"    "3136491240869"    "256 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"Land"    "3136491240871"    "258 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
"Land"    "3136491240873"    "260 NW 29 ST"    "AVENTURA MIXED USE PROPERTY LLC"    "2/10/22"    "$25,384,800"
end

bysort trueowner lastsaledate portfolioprice (propertytype): gen uniquetransaction = _n
replace uniquetransaction = 0 if uniquetransaction!=1
replace uniquetransaction = sum(uniquetransaction)

Then you should be able to get your transition indicator like so:

bysort uniquetransaction: gen uniquepropertytransaction = propertytype != propertytype[_n-1]

Thanks for posting a data example, but please use the dataex command for this in the future. Not only is it a bit of a pain to translate your data into something I can run on my end, I have to make some assumptions about what your data looks like (e.g.: the type) to get this to work.

[Daniel Schaefer]

Actually, looks like that last line doesn't correctly handle cases where the uniquetransaction value has changed, or the first case. Try this instead:

bysort uniquetransaction: gen uniquepropertytransaction = propertytype != propertytype[_n-1] & uniquetransaction == uniquetransaction[_n-1]

[Clyde Schechter]

Wouldn't it be simpler to generate both of these variables with

egen uniquetransaction = tag(trueowner lastsaledate portfolioprice)
egen uniquepropertytransaction = tag(trueowner lastsaledate portfolioprice propertytype)