Researcher

[Annalisa Russino]

I am using stata14. I have a panel database with the following structure: every group is a firm, SH_D are the shareholder stakes,

n Num_SH group SH_D Year
1 4 1 36.11 2011
2 4 1 26.93 2011
3 4 1 26.68 2011
4 4 1 10.26 2011
1 5 2 33.33 2011
2 5 2 33.33 2011
5 5 2 11.11 2011
4 5 2 11.11 2011
3 5 2 11.11 2011

For each group I need to get all the possible coalitions of shareholder with the corresponding coalition stake, and to get the coalition with the minimum stake >50. I use tuples to get all possible coalitions. Here is an example for the case of group 1


gen Coalition= ""

tuples 36.11 26.93 26.68 10.26, min(2)

set obs `ntuples'

forval i=1/`ntuples'{

replace Coalition = "`tuple`i''" in `i'

}

split Coalition
egen group=group(Coalition)
su group, meanonly

forvalues i = 1/`r(max)' {
destring Coalition`i' , generate(C_`i' )

}

ds C_*
egen CF_coalition = rowtotal(`r(varlist)')

ds C_*
egen N_coalition=rownonmiss(`r(varlist)')

gsort -CF_coalition

egen Min_CF_C=min(CF_coalition) if CF_coalition>=50

gen Min_N_C=N_coalition if CF_coalition==Min_CF_C

drop Coalition*

keep if Min_N_C!=. & Min_CF_C!=.

drop *coalition

missings dropvars,force

drop n group1 SH_D

I get one row for group one containing the informations I need : the total stake of the minimum stake coalition greater than 50, the stakes of the shareholders composing the coalition and the number of shareholder in the coalition.

Num_SH group Year C_1 C_2 Min_CF_C Min_N_C
4 1 2011 26.93 26.68 53.61 2

Problems:

1) I need to loop over all groups. Thus I need to read the list of shares from my database for each group. In the previous example the list was 36.11 26.93 26.68 10.26. Instead of providing by hand the required values, I need to retrieve the list of values. I tried the following:

levelsof SH_D
local list = r(levels)
tuples "`r(levels)'", min(2)

set obs `ntuples'

But it does not work!!
How can I make tuples to use a list a values created as a local list?

2) once I solve the previous problem I must cycle over all groups, get the previous final output for each group and append all those files to get the complete file. Any suggestion about the most efficient way to loop over groups?

Thanks

[William Lisowski]

Welcome to Statalist.

Here's some sample code using your data that may point you in a useful direction.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(n Num_SH group) float SH_D int Year
1 4 1 36.11 2011
2 4 1 26.93 2011
3 4 1 26.68 2011
4 4 1 10.26 2011
1 5 2 33.33 2011
2 5 2 33.33 2011
5 5 2 11.11 2011
4 5 2 11.11 2011
3 5 2 11.11 2011
end
generate obsnum = _n
quietly levelsof group, local(groups)
foreach g of local groups {
    * macro list _g
    quietly levelsof obsnum if group==`g', local(nn)
    * macro list _nn
    tuples `nn', asis min(2)
    * macro list _ntuples
    local minvalue 999999
    local mintuple
    forvalues c = 1/`ntuples' {
        * macro list _c _tuple`c'
        local value 0
        foreach obs in `tuple`c'' {
            * macro list _obs
            local value = `value' + SH_D[`obs']
        }
        if `value'>50 & `value'<`minvalue' {
            local minvalue `value'
            local mintuple `tuple`c''
            * macro list _minvalue _mintuple
        }
    }
    local C
    display "group `g'   value " `minvalue'
    foreach obs in `mintuple' {
        local n = n[`obs']
        local C = SH_D[`obs']
        display "n " `n' "   C " `C'
    }
}

group 1   value 53.610001
n 2   C 26.93
n 3   C 26.68
group 2   value 55.550001
n 2   C 33.330002
n 4   C 11.11
n 3   C 11.11

You may find the postfile command a useful way of creating a Stata dataset of the results.

[Annalisa Russino]

Dear William,

thank you very much. Your suggested code works perfectly. I have used the postfile command to create the final dataset. When running the code using the complete file I have got an error related to the number of combinations to be executed by tuples. I solved the problem imposing a threshold on the size of the shareholders to use for the combinations.

Again, thank you

AR

[William Lisowski]

Wow, I'm glad you were able to make it work for you - and that my postfile hint was useful. I had worried about the scope of the real data, and you found a good solution to the problem that arose, something I hadn't thought of. Thanks for closing the loop on this interesting problem.