Help with coding based on a repeated time values within panel

[Neelakanda Krishna]

Dear all, in the below sample data set, I need the following help

* Example generated by -dataex-. For more info, type help dataex
clear
input int(cocode year) str54 banker_name byte bank_number
 11 1998 "STATE BANK OF INDIA"                                    0
 11 1999 "STATE BANK OF INDIA"                                    0
 11 2000 "STATE BANK OF INDIA"                                    0
 11 2001 "STATE BANK OF INDIA"                                    0
 11 2002 "S B I FACTORS & COMMERCIAL SERVICES PVT. LTD. [MERGED]" 1
 11 2002 "SMALL INDUSTRIES DEVP. BANK OF INDIA"                   2
 11 2002 "STATE BANK OF INDIA"                                    0
 11 2003 "S B I FACTORS & COMMERCIAL SERVICES PVT. LTD. [MERGED]" 1
 11 2003 "SMALL INDUSTRIES DEVP. BANK OF INDIA"                   2
 11 2003 "STATE BANK OF INDIA"                                    0
 11 2006 "SMALL INDUSTRIES DEVP. BANK OF INDIA"                   1
 11 2006 "STATE BANK OF INDIA"                                    0
 11 2007 "SMALL INDUSTRIES DEVP. BANK OF INDIA"                   1
 11 2007 "STATE BANK OF INDIA"                                    0
 11 2009 "STATE BANK OF INDIA"                                    0
 11 2010 "EXPORT-IMPORT BANK OF INDIA"                            2
 11 2010 "I D B I BANK LTD."                                      1
 11 2010 "STATE BANK OF INDIA"                                    0
 11 2011 "EXPORT-IMPORT BANK OF INDIA"                            2
 11 2011 "I D B I BANK LTD."                                      1
 11 2011 "STATE BANK OF INDIA"                                    0
289 1990 "BANK OF BHUTAN"                                         5
289 1990 "BOMBAY MERCANTILE CO-OP. BANK LTD."                     8
289 1990 "CANARA BANK"                                            0
289 1990 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                1
289 1990 "INDIAN OVERSEAS BANK"                                   3
289 1990 "ORIENTAL BANK OF COMMERCE [MERGED]"                     7
289 1990 "STATE BANK OF INDIA"                                    2
289 1990 "STATE BANK OF SIKKIM"                                   6
289 1990 "UNITED BANK OF INDIA [MERGED]"                          4
289 1991 "BANK OF BHUTAN"                                         5
289 1991 "BOMBAY MERCANTILE CO-OP. BANK LTD."                     8
289 1991 "CANARA BANK"                                            0
289 1991 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                1
289 1991 "INDIAN OVERSEAS BANK"                                   3
289 1991 "NEW BANK OF INDIA [ERSTWHILE]"                          9
289 1991 "ORIENTAL BANK OF COMMERCE [MERGED]"                     7
289 1991 "STATE BANK OF INDIA"                                    2
289 1991 "STATE BANK OF SIKKIM"                                   6
289 1991 "UNITED BANK OF INDIA [MERGED]"                          4
289 1992 "BANK OF BHUTAN"                                         5
289 1992 "BOMBAY MERCANTILE CO-OP. BANK LTD."                     8
289 1992 "CANARA BANK"                                            0
289 1992 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                1
289 1992 "INDIAN OVERSEAS BANK"                                   3
289 1992 "NEW BANK OF INDIA [ERSTWHILE]"                          9
289 1992 "ORIENTAL BANK OF COMMERCE [MERGED]"                     7
289 1992 "STATE BANK OF INDIA"                                    2
289 1992 "STATE BANK OF SIKKIM"                                   6
289 1992 "UNITED BANK OF INDIA [MERGED]"                          4
289 1993 "BANK OF BHUTAN"                                         5
289 1993 "BOMBAY MERCANTILE CO-OP. BANK LTD."                     8
289 1993 "CANARA BANK"                                            0
289 1993 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                1
289 1993 "INDIAN OVERSEAS BANK"                                   3
289 1993 "ORIENTAL BANK OF COMMERCE [MERGED]"                     7
289 1993 "STATE BANK OF INDIA"                                    2
289 1993 "STATE BANK OF SIKKIM"                                   6
289 1993 "UNITED BANK OF INDIA [MERGED]"                          4
289 1994 "BANK OF BHUTAN"                                         5
289 1994 "CANARA BANK"                                            0
289 1994 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                1
289 1994 "INDIAN OVERSEAS BANK"                                   3
289 1994 "NEW BANK OF INDIA [ERSTWHILE]"                          8
289 1994 "ORIENTAL BANK OF COMMERCE [MERGED]"                     7
289 1994 "STATE BANK OF INDIA"                                    2
289 1994 "STATE BANK OF SIKKIM"                                   6
289 1994 "UNITED BANK OF INDIA [MERGED]"                          4
289 1995 "BANK OF BHUTAN"                                         5
289 1995 "CANARA BANK"                                            0
289 1995 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                1
289 1995 "INDIAN OVERSEAS BANK"                                   3
289 1995 "ORIENTAL BANK OF COMMERCE [MERGED]"                     8
289 1995 "PUNJAB NATIONAL BANK"                                   7
289 1995 "STATE BANK OF INDIA"                                    2
289 1995 "STATE BANK OF SIKKIM"                                   6
289 1995 "UNITED BANK OF INDIA [MERGED]"                          4
289 1996 "BANK OF BHUTAN"                                         5
289 1996 "CANARA BANK"                                            0
289 1996 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                1
289 1996 "INDIAN OVERSEAS BANK"                                   3
289 1996 "PUNJAB NATIONAL BANK"                                   7
289 1996 "STATE BANK OF INDIA"                                    2
289 1996 "STATE BANK OF SIKKIM"                                   6
289 1996 "UNITED BANK OF INDIA [MERGED]"                          4
289 1998 "CANARA BANK"                                            4
289 1998 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                3
289 1998 "INDIAN OVERSEAS BANK"                                   1
289 1998 "STATE BANK OF INDIA"                                    2
289 1999 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                3
289 1999 "INDIAN OVERSEAS BANK"                                   1
289 1999 "STATE BANK OF INDIA"                                    2
289 1999 "TIMES BANK LTD. [MERGED]"                               4
289 2000 "CANARA BANK"                                            3
289 2000 "CITIBANK N A"                                           1
289 2000 "INDIAN OVERSEAS BANK"                                   2
289 2000 "STATE BANK OF INDIA"                                    4
289 2000 "UCO BANK"                                               0
289 2001 "CANARA BANK"                                            3
289 2001 "CITIBANK N A"                                           1
end

The variable "bank_number" indicates the number of banking relationships, and 0 means 1 count, 1 means 2 count (0, and 1), 2 means 3 count (0,1,and 2). Based on the variable "bank_number", I need to create a categorical variable called "Principle_Banker" which takes the value 1 if in a given year a firm (cocode) has the value "0" only, and 2 if the firm has both 0,1 in a given year and 3 if the firms has 0,1,and 2 and so on. Let me explain by giving an example. For the cocode, "11" during the years 1998,1999,2000, and 2001, the value for "bank_number" is 0 for which I require to be coded as 1 by creating the variable "Principle_Banker". However, in 2002, the same company (cocode=11) has 3 banks (0,1,2) for which I must give the value 3 for the new variable principle banker.

I ran the following code

sort  cocode  year
egen Principle_Banker = count( bank_number ), by(year cocode )

Am I correct? Will my logic work for a big sample?
Reference:https://www.stata.com/statalist/arch.../msg00535.html

[Clyde Schechter]

Maybe. It's not clear if it even works correctly in your example. In much of the example data, for a given cocode-year combination, the values of bank_number are consecutive integers starting from 0. So, in that case, your code, which simply counts the number of observations in that combination and ignores what the numbers are, also gives the highest value of bank_number in the combination plus 1.

But, the regularity breaks down for cocode 289 (shown below). So I'm wondering whether you really just want a count, or if the value of Principal_Banker should actually be derived from the values of the variable bank_number in some way. Your description of how to define Principal_Banker is based on those values, but the values for cocode 289 don't seem to fit into the pattern you described.

  +-----------------------------------------------------------------------+
  | cocode   year                               banker_name   bank_number |
  |-----------------------------------------------------------------------|
  |    289   1998                      INDIAN OVERSEAS BANK             1 |
  |    289   1998                       STATE BANK OF INDIA             2 |
  |    289   1998   HONGKONG & SHANGHAI BANKING CORPN. LTD.             3 |
  |    289   1998                               CANARA BANK             4 |
  |-----------------------------------------------------------------------|
  |    289   1999                      INDIAN OVERSEAS BANK             1 |
  |    289   1999                       STATE BANK OF INDIA             2 |
  |    289   1999   HONGKONG & SHANGHAI BANKING CORPN. LTD.             3 |
  |    289   1999                  TIMES BANK LTD. [MERGED]             4 |
  |-----------------------------------------------------------------------|
  |    289   2001                              CITIBANK N A             1 |
  |    289   2001                               CANARA BANK             3 |
  +-----------------------------------------------------------------------+

In 1998, and 1999, there are no entries with bank_number == 0. Instead they run 1, 2, 3, 4. So is the correct answer 4 (which is what your code will give) or something else? If something else, what and why?

In 2001, we don't even have consecutive values of bank_number, just 1 and 3 (and, again, no 0). So is the answer 2 (which is what your code will give) or should it be something else? If something else, what and why?

[Neelakanda Krishna]

Dear Clyde Schechter, Thanks for pointing that "bank_number" has cases where there is no "0". But as you said, the code will give the correct value based on counting. My logic of that code is based on the following narrative "we code firm-years where a firm banks exclusively with one bank as R-firms (principal_banker) and firm-years where a firm deals with multiple banks simultaneously as T-firms (multiple_banker). As a robustness measure, we redefine principal_banker as a continuous spell of a firm dealing with a single bank for two, three, four, and five consecutive years"


With the same data, how will you code the above

[Clyde Schechter]

Your code will provide, for each firm-year, a count of the number of banks with which it has dealt. But that is not the same thing as "we redefine principal_banker as a continuous spell of a firm dealing with a single bank for two, three, four, and five consecutive years" The following code will identify continuous spells of a firm dealing with a single bank and give you the duration of each such spell.

isid cocode year banker_name, sort
by cocode year (banker_name), sort: gen n_banks = _N
gen byte single_banker = n_banks == 1

//  IDENTIFY SPELLS OF FIRM-YEARS WITH SINGLE BANKER
by cocode (year banker_name): gen spell_num = sum(single_banker != single_banker[_n-1])
by cocode spell_num (year banker_name), sort: gen duration = year[_N]-year[1] + 1 ///
    if single_banker

[Neelakanda Krishna]

Thanks Clyde Schechter for the help. Your code can be readily used. However, I thought of understanding them before using it so that in future, I can try this myself. I have small doubt regarding "duration" and spell_num. Based on the below dataset which has the output of the codes, what does spell_num and duration indicate for say cocode 11 for the years 1998-2001. How come in the year 2010, cocode 11 duration got reduced to 2. I am unable to give a plain explanation for the meaning of codes, hence I am again asking this. If you have some time to spare in this regard for helping me with the plain interpretation of spell_num and duration, it will be highly helpful.

clear
input int(cocode year) str54 banker_name byte bank_number float n_banks byte single_banker float(spell_num duration)
 11 1998 "STATE BANK OF INDIA"                                    0 1 1 1  4
 11 1999 "STATE BANK OF INDIA"                                    0 1 1 1  4
 11 2000 "STATE BANK OF INDIA"                                    0 1 1 1  4
 11 2001 "STATE BANK OF INDIA"                                    0 1 1 1  4
 11 2002 "S B I FACTORS & COMMERCIAL SERVICES PVT. LTD. [MERGED]" 1 3 0 2  6
 11 2002 "SMALL INDUSTRIES DEVP. BANK OF INDIA"                   2 3 0 2  6
 11 2002 "STATE BANK OF INDIA"                                    0 3 0 2  6
 11 2003 "S B I FACTORS & COMMERCIAL SERVICES PVT. LTD. [MERGED]" 1 3 0 2  6
 11 2003 "SMALL INDUSTRIES DEVP. BANK OF INDIA"                   2 3 0 2  6
 11 2003 "STATE BANK OF INDIA"                                    0 3 0 2  6
 11 2006 "SMALL INDUSTRIES DEVP. BANK OF INDIA"                   1 2 0 2  6
 11 2006 "STATE BANK OF INDIA"                                    0 2 0 2  6
 11 2007 "SMALL INDUSTRIES DEVP. BANK OF INDIA"                   1 2 0 2  6
 11 2007 "STATE BANK OF INDIA"                                    0 2 0 2  6
 11 2009 "STATE BANK OF INDIA"                                    0 1 1 3  1
 11 2010 "EXPORT-IMPORT BANK OF INDIA"                            2 3 0 4  2
 11 2010 "I D B I BANK LTD."                                      1 3 0 4  2
 11 2010 "STATE BANK OF INDIA"                                    0 3 0 4  2
 11 2011 "EXPORT-IMPORT BANK OF INDIA"                            2 3 0 4  2
 11 2011 "I D B I BANK LTD."                                      1 3 0 4  2
 11 2011 "STATE BANK OF INDIA"                                    0 3 0 4  2
289 1990 "BANK OF BHUTAN"                                         5 9 0 1 12
289 1990 "BOMBAY MERCANTILE CO-OP. BANK LTD."                     8 9 0 1 12
289 1990 "CANARA BANK"                                            0 9 0 1 12
289 1990 "HONGKONG & SHANGHAI BANKING CORPN. LTD."                1 9 0 1 12
289 1990 "INDIAN OVERSEAS BANK"                                   3 9 0 1 12
289 1990 "ORIENTAL BANK OF COMMERCE [MERGED]"                     7 9 0 1 12
289 1990 "STATE BANK OF INDIA"                                    2 9 0 1 12
289 1990 "STATE BANK OF SIKKIM"                                   6 9 0 1 12
289 1990 "UNITED BANK OF INDIA [MERGED]"                          4 9 0 1 12
end

[/CODE]

[Clyde Schechter]

The actual numerical values of spell_num have no real meaning. They are really there just to distinguish which episodes belong together in a spell. The easiest way to code this is to use consecutive numbers starting from 1, but really spell_num should be thought of as a categorical variable, not as truly numeric. It's just breaking up the data into blocksof years where a single cocode retains the same banking relationships (that is, single banker or not).

As for what happens with cocode 11 in 2010, we see that in 2009 cocode 11 has a single bank relationship. In 2010 it has multiple banks. So a new spell begins in 2010. Relationship with multiple banks persists into 2011, so the spell extends through 2011, and then the data for that cocode ends. So the spell begins in 2010 and ends in 2011 which is a duration 2 years.

[Neelakanda Krishna]

Thanks Clyde Schechter for your valuable time and helpful explanation