Extracting the change in the Headquarter

[Fimi Karimi]

Hello,

I need to find the "year" in which the head office of companies in my data set has changed. For example in company 001001, the city has changed in 1982.

input str6 CompanyID double Year str100 HeadquarterCity
CompanyID Year HeadquarterCity
"001001" 1978 "Tulsa"
"001001" 1979 "Tulsa"
"001001" 1980 "Tulsa"
"001001" 1981 "Tulsa"
"001001" 1982 "Dallas"
"001001" 1983 "Dallas"
"001001" 1984 "Dallas"
"001001" 1985 "Dallas"
"001002" 1960 "Hunt Valley"
"001002" 1961 "Hunt Valley"
"001002" 1962 "Hunt Valley"
"001002" 1963 "Hunt Valley"
"001002" 1964 "Hunt Valley"

Could I ask you to help me with command which can extract the year for each company?
Thanks

[Andrew Musau]

What if there are multiple changes?

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 CompanyID double Year str100 HeadquarterCity
"001001" 1978 "Tulsa"      
"001001" 1979 "Tulsa"      
"001001" 1980 "Tulsa"      
"001001" 1981 "Tulsa"      
"001001" 1982 "Dallas"     
"001001" 1983 "Dallas"     
"001001" 1984 "Dallas"     
"001001" 1985 "Dallas"     
"001002" 1960 "Hunt Valley"
"001002" 1961 "Hunt Valley"
"001002" 1962 "Hunt Valley"
"001002" 1963 "Hunt Valley"
"001002" 1964 "Hunt Valley"
end

bys Co (Year): gen wanted= Year if Headq!=Headq[_n-1] & _n>1
bys Co (wanted): replace wanted=wanted[1]

Res.:

. sort Co Year

. l, sepby(Co)

     +----------------------------------------+
     | Compan~D   Year   Headquart~y   wanted |
     |----------------------------------------|
  1. |   001001   1978         Tulsa     1982 |
  2. |   001001   1979         Tulsa     1982 |
  3. |   001001   1980         Tulsa     1982 |
  4. |   001001   1981         Tulsa     1982 |
  5. |   001001   1982        Dallas     1982 |
  6. |   001001   1983        Dallas     1982 |
  7. |   001001   1984        Dallas     1982 |
  8. |   001001   1985        Dallas     1982 |
     |----------------------------------------|
  9. |   001002   1960   Hunt Valley        . |
 10. |   001002   1961   Hunt Valley        . |
 11. |   001002   1962   Hunt Valley        . |
 12. |   001002   1963   Hunt Valley        . |
 13. |   001002   1964   Hunt Valley        . |
     +----------------------------------------+

[Fimi Karimi]

Thanks a lot, Andrew.

[Fimi Karimi]

if there are multiple change, I need to extract all dates that headquarter has changed. For some cases, they have changed their head office more than three times.

Andrew, I run your command but It gave me this error: "weights not allowed"

[Andrew Musau]

The code in #2 does not produce any such error. Can you show the exact Stata output? Regarding #4, what do you want to do with this variable? You can create a string with a list of dates, but this cannot be used for any analysis.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 CompanyID double Year str100 HeadquarterCity
"001001" 1978 "Tulsa"      
"001001" 1979 "Tulsa"      
"001001" 1980 "Tulsa"      
"001001" 1981 "Tulsa"      
"001001" 1982 "Dallas"    
"001001" 1983 "Dallas"    
"001001" 1984 "Dallas"    
"001001" 1985 "Dallas"  
"001001" 1986 "Houston"    
"001001" 1987 "Houston"    
"001002" 1960 "Hunt Valley"
"001002" 1961 "Hunt Valley"
"001002" 1962 "Hunt Valley"
"001002" 1963 "Hunt Valley"
"001002" 1964 "Hunt Valley"
"001002" 1965 "Dallas"    
"001002" 1965 "Dallas"  
"001003" 1960 "Hunt Valley"
"001003" 1961 "Hunt Valley"
"001003" 1962 "Hunt Valley"
"001003" 1963 "Hunt Valley"  
end


bys Co (Year): gen change= Year if Headq!=Headq[_n-1] & _n>1
by Co: gen which=_n
qui sum which
gen wanted=""
forval i=2/`r(max)'{
    by Co: replace wanted=cond(!missing(change), wanted[_n-1]+ " "+ string(change) + ";", wanted[_n-1])
}
by Co: replace wanted= ustrregexra(wanted[_N], "(.*);$", "$1")
drop change which

Res.:

. l, sepby(Co)

     +---------------------------------------------+
     | Compan~D   Year   Headquart~y        wanted |
     |---------------------------------------------|
  1. |   001001   1978         Tulsa    1982; 1986 |
  2. |   001001   1979         Tulsa    1982; 1986 |
  3. |   001001   1980         Tulsa    1982; 1986 |
  4. |   001001   1981         Tulsa    1982; 1986 |
  5. |   001001   1982        Dallas    1982; 1986 |
  6. |   001001   1983        Dallas    1982; 1986 |
  7. |   001001   1984        Dallas    1982; 1986 |
  8. |   001001   1985        Dallas    1982; 1986 |
  9. |   001001   1986       Houston    1982; 1986 |
 10. |   001001   1987       Houston    1982; 1986 |
     |---------------------------------------------|
 11. |   001002   1960   Hunt Valley          1965 |
 12. |   001002   1961   Hunt Valley          1965 |
 13. |   001002   1962   Hunt Valley          1965 |
 14. |   001002   1963   Hunt Valley          1965 |
 15. |   001002   1964   Hunt Valley          1965 |
 16. |   001002   1965        Dallas          1965 |
 17. |   001002   1965        Dallas          1965 |
     |---------------------------------------------|
 18. |   001003   1960   Hunt Valley               |
 19. |   001003   1961   Hunt Valley               |
 20. |   001003   1962   Hunt Valley               |
 21. |   001003   1963   Hunt Valley               |
     +---------------------------------------------+

[Fimi Karimi]

Thank you Andrew, It works.