align the auditors (data manipulation)?

[River Huang]

Dear All, I found this question here (in Chinese). Suppose that I have the data

// https://bbs.pinggu.org/thread-11172305-1-1.html
* Example generated by -dataex-. For more info, type help dataex
clear
input str12 stkcd double year str54 auditor1 str18 auditor2
"000002" 2000 "许丽周" "李莉"   
"000002" 2001 "赵奇"    "金乃雯"
"000002" 2002 "赵奇"    "罗科"   
"000002" 2003 "罗科"    "金乃雯"
"000002" 2004 "罗科"    "彭菁"   
"000002" 2005 "李婉薇" "彭菁"   
"000002" 2006 "李婉薇" "彭菁"   
end

For each company (`stkcd'), there are two auditors (`auditor1' and `auditor2'). For this company (000002), there are two auditors "赵奇" (auditor1) and "罗科" (auditor2) in year 2002. In year 2003, there are two auditors "罗科" (auditor1) and "金乃雯" (auditor2). Since "罗科" is auditor2 in the previous year, I'd like to put "罗科" into auditor2 in year 2003 (Of course, "金乃雯" is now auditor1). That is, whether a person is auditor1 or auditor2 depends on its status (1 or 2) in the prior year.

The purpose is to obtain

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 stkcd double year str54 auditor1 str18 auditor2
"000002" 2000 "许丽周" "李莉"   
"000002" 2001 "赵奇"    "金乃雯"
"000002" 2002 "赵奇"   "罗科"   
"000002" 2003  "金乃雯"   "罗科"
"000002" 2004 "彭菁"     "罗科"  
"000002" 2005 "彭菁"    "李婉薇"
"000002" 2006 "彭菁"   "李婉薇"
end

Any suggestions are highly appreciated.

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 stkcd double year str54 auditor1 str18 auditor2
"000002" 2000 "许丽周" "李莉"   
"000002" 2001 "赵奇"    "金乃雯"
"000002" 2002 "赵奇"    "罗科"   
"000002" 2003 "罗科"    "金乃雯"
"000002" 2004 "罗科"    "彭菁"   
"000002" 2005 "李婉薇" "彭菁"   
"000002" 2006 "李婉薇" "彭菁"   
end

g holding= auditor1
bys stkcd (year): g tag= auditor1==auditor2[_n-1]| auditor2==auditor1[_n-1]
replace auditor1= auditor2 if tag
replace auditor2= holding if tag
drop holding

Res.:

. l, sepby(stkcd)

     +-------------------------------------------+
     |  stkcd   year   auditor1   auditor2   tag |
     |-------------------------------------------|
  1. | 000002   2000     许丽周       李莉     0 |
  2. | 000002   2001       赵奇     金乃雯     0 |
  3. | 000002   2002       赵奇       罗科     0 |
  4. | 000002   2003     金乃雯       罗科     1 |
  5. | 000002   2004       罗科       彭菁     0 |
  6. | 000002   2005     李婉薇       彭菁     0 |
  7. | 000002   2006     李婉薇       彭菁     0 |
     +-------------------------------------------+

[River Huang]

Dear Andrew, Thanks for this suggestion. We are almost there.

As you can see, in year 2004, 罗科 is auditor1 but he (罗科) is auditor2 in the prior year. Thus, in year 2004, 罗科 should be auditor2, and 彭菁 is auditor1.
More complex, since now 彭菁 is auditor1 in 2004, she (彭菁) should be auditor1 in 2005 and 2006 as well.

[Andrew Musau]

Ah yes, this sorting is dynamic. We can loop the same code. Does this do it?

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 stkcd double year str54 auditor1 str18 auditor2
"000002" 2000 "许丽周" "李莉"   
"000002" 2001 "赵奇"    "金乃雯"
"000002" 2002 "赵奇"    "罗科"   
"000002" 2003 "罗科"    "金乃雯"
"000002" 2004 "罗科"    "彭菁"   
"000002" 2005 "李婉薇" "彭菁"   
"000002" 2006 "李婉薇" "彭菁"   
end

qui levelsof year,local(years)

forval i=1/`=wordcount("`years'")'{
    g holding= auditor1
    bys stkcd (year): g tag= auditor1==auditor2[_n-1]| auditor2==auditor1[_n-1]
    qui replace auditor1= auditor2 if tag
    qui replace auditor2= holding if tag
    drop tag holding
}

Res.:

. l, sepby(stkcd)

     +-------------------------------------+
     |  stkcd   year   auditor1   auditor2 |
     |-------------------------------------|
  1. | 000002   2000     许丽周       李莉 |
  2. | 000002   2001       赵奇     金乃雯 |
  3. | 000002   2002       赵奇       罗科 |
  4. | 000002   2003     金乃雯       罗科 |
  5. | 000002   2004       彭菁       罗科 |
  6. | 000002   2005       彭菁     李婉薇 |
  7. | 000002   2006       彭菁     李婉薇 |
     +-------------------------------------+