pairs of 2 clients from different countries but same industry and year end

[Maysam Ayoub]

Hello,
I am interested in financial statement comparability; therefore I need to create pairs of two companies (var=CompanyID) that are from different countries (var=Country), are in the same industry (var=Industry), and have the same fiscal year ends (var=Year) to be able to calculate the difference in Accruals (TACC) between companies.

Any help is highly appreciated.



----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 A str8 B str7 C str4 D str7 E
"CompanyID"    "Industry" "Country" "Year" "TACC"   
"AT0000758008" "2082"     "AUSTRIA" "2012" ".08"    
"AT0000758008" "2082"     "AUSTRIA" "2013" ".081536"
"BE0974293251" "2082"     "BELGIUM" "2012" ".082536"
"BE0974293251" "2082"     "BELGIUM" "2013" ".083536"
"BE0003877942" "2834"     "BELGIUM" "2012" ".084536"
"BE0003877942" "2834"     "BELGIUM" "2013" ".085536"
"AT0000776307" "2834"     "AUSTRIA" "2012" ".086536"
"AT0000776307" "2834"     "AUSTRIA" "2013" ".087536"
"BE0003874915" "2834"     "BELGIUM" "2013" ".088536"
"BE0003739530" "2834"     "BELGIUM" "2012" ".089536"
"BE0003739530" "2834"     "BELGIUM" "2013" ".090536"
"BE0003874915" "2834"     "BELGIUM" "2012" ".091536"
"BE0003503118" "2834"     "BELGIUM" "2012" ".092536"
"BE0003503118" "2834"     "BELGIUM" "2013" ".093536"
"AT0000625504" "6029"     "AUSTRIA" "2012" ".094536"
"AT0000625504" "6029"     "AUSTRIA" "2013" ".095536"
"AT0000624705" "6029"     "AUSTRIA" "2012" ".096536"
"AT0000624705" "6029"     "AUSTRIA" "2013" ".097536"
"AT0000625108" "6029"     "AUSTRIA" "2012" ".098536"
"AT0000625108" "6029"     "AUSTRIA" "2013" ".099536"
"AT0000606306" "6029"     "AUSTRIA" "2012" ".100536"
"AT0000606306" "6029"     "AUSTRIA" "2013" ".101536"
"BE0003878957" "6512"     "BELGIUM" "2012" ".102536"
"AT0000A21KS2" "6512"     "AUSTRIA" "2012" ".103536"
"AT0000652250" "6512"     "AUSTRIA" "2012" ".104536"
"AT0000A21KS2" "6512"     "AUSTRIA" "2013" ".105536"
"BE0003674851" "6512"     "BELGIUM" "2012" ".106536"
"BE0003770840" "6512"     "BELGIUM" "2012" ".107536"
"BE0003674851" "6512"     "BELGIUM" "2013" ".108536"
"BE0003770840" "6512"     "BELGIUM" "2013" ".109536"
"AT0000641352" "6512"     "AUSTRIA" "2012" ".110536"
"AT0000641352" "6512"     "AUSTRIA" "2013" ".111536"
"BE0003724383" "6512"     "BELGIUM" "2012" ".112536"
"BE0003724383" "6512"     "BELGIUM" "2013" ".113536"
"AT0000652250" "6512"     "AUSTRIA" "2013" ".114536"
"BE0003851681" "6513"     "BELGIUM" "2012" ".115536"
"BE0003851681" "6513"     "BELGIUM" "2013" ".116536"
"AT0000697750" "6513"     "AUSTRIA" "2012" ".117536"
"AT0000697750" "6513"     "AUSTRIA" "2013" ".118536"
"BE0974409410" "6513"     "BELGIUM" "2012" ".119536"
"BE0974409410" "6513"     "BELGIUM" "2013" ".120536"
"BE0003664753" "6513"     "BELGIUM" "2013" ".121536"
"BE0003837540" "6513"     "BELGIUM" "2012" ".122536"
"BE0003837540" "6513"     "BELGIUM" "2013" ".123536"
end

------------------ copy up to and including the previous line ------------------

Listed 45 out of 45 observations

[Clyde Schechter]

Well, your first, and bigger, problem is that the data set was badly imported from Excel. You will get nowhere slowly with variables A B C D and E, all of which are non-numeric, so that no calculations can be done, and where the real variable names are hidden in the first observation of the data set. If you brought this in with -import excel- you should have used the -firstrow- option to get the variables named correctly. If you brought it in some other way, use -import excel- instead!

The code below begins by fixing up this problem. It then goes on to form the pairs you requested.

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 A str8 B str7 C str4 D str7 E
"CompanyID"    "Industry" "Country" "Year" "TACC"   
"AT0000758008" "2082"     "AUSTRIA" "2012" ".08"    
"AT0000758008" "2082"     "AUSTRIA" "2013" ".081536"
"BE0974293251" "2082"     "BELGIUM" "2012" ".082536"
"BE0974293251" "2082"     "BELGIUM" "2013" ".083536"
"BE0003877942" "2834"     "BELGIUM" "2012" ".084536"
"BE0003877942" "2834"     "BELGIUM" "2013" ".085536"
"AT0000776307" "2834"     "AUSTRIA" "2012" ".086536"
"AT0000776307" "2834"     "AUSTRIA" "2013" ".087536"
"BE0003874915" "2834"     "BELGIUM" "2013" ".088536"
"BE0003739530" "2834"     "BELGIUM" "2012" ".089536"
"BE0003739530" "2834"     "BELGIUM" "2013" ".090536"
"BE0003874915" "2834"     "BELGIUM" "2012" ".091536"
"BE0003503118" "2834"     "BELGIUM" "2012" ".092536"
"BE0003503118" "2834"     "BELGIUM" "2013" ".093536"
"AT0000625504" "6029"     "AUSTRIA" "2012" ".094536"
"AT0000625504" "6029"     "AUSTRIA" "2013" ".095536"
"AT0000624705" "6029"     "AUSTRIA" "2012" ".096536"
"AT0000624705" "6029"     "AUSTRIA" "2013" ".097536"
"AT0000625108" "6029"     "AUSTRIA" "2012" ".098536"
"AT0000625108" "6029"     "AUSTRIA" "2013" ".099536"
"AT0000606306" "6029"     "AUSTRIA" "2012" ".100536"
"AT0000606306" "6029"     "AUSTRIA" "2013" ".101536"
"BE0003878957" "6512"     "BELGIUM" "2012" ".102536"
"AT0000A21KS2" "6512"     "AUSTRIA" "2012" ".103536"
"AT0000652250" "6512"     "AUSTRIA" "2012" ".104536"
"AT0000A21KS2" "6512"     "AUSTRIA" "2013" ".105536"
"BE0003674851" "6512"     "BELGIUM" "2012" ".106536"
"BE0003770840" "6512"     "BELGIUM" "2012" ".107536"
"BE0003674851" "6512"     "BELGIUM" "2013" ".108536"
"BE0003770840" "6512"     "BELGIUM" "2013" ".109536"
"AT0000641352" "6512"     "AUSTRIA" "2012" ".110536"
"AT0000641352" "6512"     "AUSTRIA" "2013" ".111536"
"BE0003724383" "6512"     "BELGIUM" "2012" ".112536"
"BE0003724383" "6512"     "BELGIUM" "2013" ".113536"
"AT0000652250" "6512"     "AUSTRIA" "2013" ".114536"
"BE0003851681" "6513"     "BELGIUM" "2012" ".115536"
"BE0003851681" "6513"     "BELGIUM" "2013" ".116536"
"AT0000697750" "6513"     "AUSTRIA" "2012" ".117536"
"AT0000697750" "6513"     "AUSTRIA" "2013" ".118536"
"BE0974409410" "6513"     "BELGIUM" "2012" ".119536"
"BE0974409410" "6513"     "BELGIUM" "2013" ".120536"
"BE0003664753" "6513"     "BELGIUM" "2013" ".121536"
"BE0003837540" "6513"     "BELGIUM" "2012" ".122536"
"BE0003837540" "6513"     "BELGIUM" "2013" ".123536"
end

//  MAKE THIS A USABLE STATA DATA SET
foreach v of varlist _all {
    rename `v' `=`v'[1]'
}
drop in 1
destring Industry Year TACC, replace

//  CREATE A MIRROR OF THE DATA
preserve
ds Industry Year, not
rename (`r(varlist)') =2
tempfile matches
save `matches'

//  FORM PAIRS, THEN ELIMINATE THOSE IN THE SAME COUNTRY
restore
joinby Industry Year using `matches'
drop if Country == Country2

[Maysam Ayoub]

Dear Clyde,
Thank you so much. Highly appreciated.

[Maysam Ayoub]

Hello Clyde,
I tried to change what I want from different countries to same countries but the code did not work. Can you kindly tell me what how to amend the code?

create pairs of two companies (var=CompanyID) that are from the same countries (var=Country), are in the same industry (var=Industry), and have the same fiscal year ends (var=Year) to be able to calculate the difference in Accruals (TACC) between companies.

I changed the code as per the below but it doesn't seem correct.

// FORM PAIRS, THEN ELIMINATE THOSE IN THE SAME COUNTRY restore joinby Industry Year using `matches' drop if Country != Country2

[Clyde Schechter]

//  MAKE THIS A USABLE STATA DATA SET
foreach v of varlist _all {
    rename `v' `=`v'[1]'
}
drop in 1
destring Industry Year TACC, replace

//  CREATE A MIRROR OF THE DATA
preserve
ds Country Industry Year, not
rename (`r(varlist)') =2
tempfile matches
save `matches'

//  FORM PAIRS MATCHING ON COUNTRY, INDUSTRY, & YEAR
restore
joinby Country Industry Year using `matches'

[Maysam Ayoub]

Thank you so much! Appreciated