Creating a loop with egen, conditional on a specific value of a variable

[Giovanni Pasquali]

Dear Stata users,

I am working with a pooled cross section dataset on export transactions, using Stata 14.2. My dataset shows dyadic transactions between buying and supplying firms over a period of 10 years (2006 and 2015), with their respective traded value. This is an example of how the dataset looks like:

year	supplier	buyer	traded_value	value_ABHolding	value_GPLtd	supplier_value_year	HHI
2006	Alpha	ABHolding	10	22	0	22
2006	Alpha	ABHolding	12	22	0	22
2010	Alpha	GPLtd	8	5	8	13
2010	Alpha	ABHolding	5	5	8	13
2011	Omega	GPLtd	4	0	11	11
2011	Omega	GPLtd	7	0	11	11
2014	Beta	GPLtd	2	0	2	2

Year is the year in which the transaction occurred. Supplier is the name of the firm supplying the product. Buyer is the name of the firm buying the product. The traded value is the value of the traded goods between the supplier and the buyer. value_'buyer' reports the total value that a certain buyer (e.g. ABHolding) bought from a specific supplier (e.g. Alpha) in that given year and it is repeated for the same combination of supplier-year. Supplier_value_year is the total traded value for a certain supplier in a given year (including all buyers).

I want to create an HHI index that is specific to each consignor-year and indicates whether the traded value by each supplier in a given year goes to one or many buyers. The HHI approaches 1 if the supplier traded only with 1 client in a given year and it approaches 0 as the number of clients in a given year increases. It is computed as:

sum[(value_ABHolding/supplier_value_year)^2 + (value_GPLtd/supplier_value_year)^2 + (value_'n'/supplier_value_year)^2....+....]

I figured how to compute the HHI. However, as there are 6000 buyers, the procedure requires over 6000 lines of code. I am asking for help to put this into a loop. At the moment the correct coding looks as follows:

   
bys supplier year: egen value_ABHolding = sum(cond(buyer=="AB Holding", fob_usd, .))   
  
bys supplier year: egen value_GPLtd = sum(cond(buyer=="AGP Ltd", fob_usd, .))  
  
.....
.....
     
bys supplier year: egen supplier_value_year=sum(traded_value)
      
bys consignorname_n year: gen HHI=[(fob_ABHolding / supplier_value_year)^2 + (fob_GPLtd / supplier_value_year)^2 + .... + ....]

It works perfectly fine, but once I put it in a loop, I get a error message. My attempt is the following:

 
foreach buyer {
  
bys supplier year: egen newvalue_`j’ = sum(cond(buyer==`j’, fob_usd, .))
  
bys consignorname_n year: egen HHI=sum[(newvalue_`j’/supplier_value_year)^2]
  }

Could you please help me to put the single codes into a loop?

[Nick Cox]

There have been many posts here on HHI, well over 100 if we take into account mentions of Herfindahl and/or Hirschman without mentioning HHI, misspellings of those names, the same construct under different names, and so on. Which ones did you read before posting?

Typically, HHI entails about 2 lines of code. Calculating concentration of transactions really does not depend on knowing the names of parties.

Your loop won't work for at least 3 reasons:

foreach buyer

is nothing like any of the permitted syntaxes.

That aside, each time around the loop you are asking to egen a variable, which will only work first time. Later, the variable already exists and egen won't overwrite.

That said, I don't think I fully understand your data example and code. Your code refers to variables that aren't in the example.

Here's a minimal data and code example starting from the other end. Note the use of input code. Your data example leaves ambiguous whether names are string values or value labels.

clear
input year str1(supplier buyer) amount
2015 A X 1
2015 A Y 2
2016 B Y 3
2016 B X 4
end

bysort supplier year : egen pc = pc(amount), prop
bysort supplier year : egen HHI = total(pc^2)

     +--------------------------------------------------------+
     | year   supplier   buyer   amount         pc        HHI |
     |--------------------------------------------------------|
  1. | 2015          A       X        1   .3333333   .5555556 |
  2. | 2015          A       Y        2   .6666667   .5555556 |
  3. | 2016          B       Y        3   .4285714   .5102041 |
  4. | 2016          B       X        4   .5714286   .5102041 |
     +--------------------------------------------------------+

[Giovanni Pasquali]

Hi Nick,

Thanks a lot for the explanation. I apologise for the use of undefined variables in my codes. I did not change the variables names from my original document, making it unclear. I am truly sorry for this.
To answer your question, I checked the following posts:

https://www.statalist.org/forums/for...index-in-stata
https://www.statalist.org/forums/for...imilar-regions

In the second post, I found some useful codes on conditioning my egen function, yet it still does not answer my original question. I'll try to explain.

The code you suggest works well. However, I am trying to input a condition - i.e. that the calculated share (pc) is relative to a specific buyer with whom the supplier dealt with in a given year.
Note that, I have a dataset reporting transactions where the same supplier may transact with the same buyer more than once during the same year. Something like the following:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float year str1(supplier buyer) float amount
2015 "A" "X" 1
2015 "A" "Y" 2
2015 "A" "X" 5
2016 "B" "Y" 3
2016 "B" "X" 4
2016 "B" "Y" 2
end

The HHI should give me an indication of weather a buyer's amount sold in a given year is concentrated with fewer buyers or, instead, a relatively larger base of buyers. It would be equal to 1 if the supplier sold the entire amount transacted in that year to a single buyer (it does not matter if this happened in one single transaction or in several transactions, as long as such transactions are with the same buyer and in the same year).
My attempt would be to condition your first code to the specific buyer in this way:

bysort supplier year : egen pc = pc(cond(buyer==`i’, amount, .)), prop
bysort supplier year : egen HHI = total(pc^2)

However, I get the error message: { required
There are over 1000 suppliers and 3000 buyers, so running separate codes for each buyer is problematic. I believe I would need a loop for the first line of code that looks something like this:

foreach buyer=`i’ {
bysort consignorname_n year : egen pc = pc(cond(buyer==`i’, amount, .)), prop
}

This, however, gives the message: 'invalid syntax'

[Nick Cox]

Working backwards, some reasons why your code isn't working are evident.

As already mentioned in #2 foreach only allows certain syntaxes and this attempt is not legal any more than the earlier one was.

If that were fixed, then -- again as already mentioned in #2 -- the first time around the loop (assuming no other errors, a big assumption) you would produce a new variable, but second and later times the egen call would fail as that command never overwrites an existing variable.

So, you are just repeating errors already pointed out to you.

You invoke various local macros but nowhere do you say where they are defined.

I don't know for certain what consignorname_n is: it doesn't appear in your data example. I guess it means supplier.

But, but, but it seems to me that your code is an unnecessary loop and that you want

  
 bysort consignorname_n buyer year : egen pc = pc(amount), prop

with some considerable need to aggregate multiple transactions first. But then doesn't HHI end up vacuously as always 1?

Regardless of that, you should aggregate multiple transactions first if you don't care about distinguishing them.

[Giovanni Pasquali]

Thanks a lot for your help Nick