Help with transferring information from one variable to another

[Christelle Alkhoury]

Hello team,

I kindly need your help with transferring values from one variable to another.
I have a dataset that includes Customers and Suppliers, whereby customers of some firms are suppliers of others. And suppliers of some firms are customers of others.
I have the variable Intbreached_supplierpre which is a dummy varible reflecting whether a supplier has been interlocked and breached in the past.
I want to create the same variable for customers (i.e., Intbreached_customerpre) using the Intbreached_supplierpre variable. That is, I want to set Intbreached_customerpre=1 if the customer (which is a supplier in some cases) has been interlocked and breached in the same year.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float Year str76(Customer Supplier) float Intbreached_supplierpre
2015 "16th Street Partners, LLC"      "PARKWAY PROPERTIES, INC."                                        0
2016 "24 Hour Fitness World, Inc."    "WEINGARTEN REALTY INVSTWEINGARTEN REALTY INVESTORS"              1
2017 "24 Hour Fitness World, Inc."    "WEINGARTEN REALTY INVSTWEINGARTEN REALTY INVESTORS"              0
2018 "24 Hour Fitness World, Inc."    "WEINGARTEN REALTY INVSTWEINGARTEN REALTY INVESTORS"              0
2019 "24 Hour Fitness World, Inc."    "WEINGARTEN REALTY INVSTWEINGARTEN REALTY INVESTORS"              0
2007 "3M Unitek"                      "CERADYNE INCCERADYNE, INC."                                      0
2008 "3M Unitek"                      "CERADYNE INCCERADYNE, INC."                                      0
2009 "3M Unitek"                      "CERADYNE INCCERADYNE, INC."                                      0
2010 "3M Unitek"                      "CERADYNE INCCERADYNE, INC."                                      0
2011 "3M Unitek"                      "CERADYNE INCCERADYNE, INC."                                      0
2016 "7-ELEVEN INC"                   "CARDTRONICS PLCCARDTRONICS PLC"                                  0
2017 "7-ELEVEN INC"                   "CARDTRONICS PLCCARDTRONICS PLC"                                  0
2012 "7-Eleven Inc"                   "NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC."      0
2013 "7-Eleven Inc"                   "NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC."      0
2014 "7-Eleven Inc"                   "NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC."      0
2016 "7-Eleven Inc"                   "REALTY INCOME CORPREALTY INCOME CORPORATION"                     1
2017 "7-Eleven Inc"                   "REALTY INCOME CORPREALTY INCOME CORPORATION"                     0
2018 "7-Eleven Inc"                   "REALTY INCOME CORPREALTY INCOME CORP."                           0
2018 "7-Eleven Inc"                   "NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC."      0
2019 "7-Eleven Inc"                   "NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC."      1
2019 "7-Eleven Inc"                   "REALTY INCOME CORPREALTY INCOME CORPORATION"                     1
2020 "7-Eleven Inc"                   "NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC."      0
2020 "7-Eleven Inc"                   "REALTY INCOME CORPREALTY INCOME CORPORATION"                     0
2021 "7-Eleven Inc"                   "NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC."      0
2021 "7-Eleven Inc"                   "REALTY INCOME CORPREALTY INCOME CORPORATION"                     0
2008 "A&P Supermarkets"               "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2009 "A&P Supermarkets"               "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2010 "A&P Supermarkets"               "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2021 "AAE Aerospace"                  "PARK AEROSPACE CORPPARK AEROSPACE CORP."                         0
2014 "AAH Pharmaceuticals Ltd (U.K.)" "MERCK & COMERCK & CO., INC."                                     1
2015 "AAH Pharmaceuticals Ltd (U.K.)" "MERCK & COMERCK & CO., INC."                                     1
2016 "AAH Pharmaceuticals Ltd (U.K.)" "MERCK & COMERCK & CO., INC."                                     1
2007 "ABBOTT LABORATORIES"            "MARTEK BIOSCIENCES CORPMARTEK BIOSCIENCES CORP."                 0
2008 "ABBOTT LABORATORIES"            "MARTEK BIOSCIENCES CORPMARTEK BIOSCIENCES CORP."                 0
2009 "ABBOTT LABORATORIES"            "MARTEK BIOSCIENCES CORPMARTEK BIOSCIENCES CORP."                 0
2010 "ABBOTT LABORATORIES"            "MARTEK BIOSCIENCES CORPMARTEK BIOSCIENCES CORP."                 0
2007 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2008 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2009 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2010 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2013 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2014 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2015 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2016 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2017 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2018 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL, INC."                    0
2019 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     1
2020 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2021 "ACE HARDWARE CORP"              "RPM INTERNATIONAL INCRPM INTERNATIONAL INC."                     0
2015 "ACME Supermarkets"              "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2016 "ACME Supermarkets"              "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2017 "ACME Supermarkets"              "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2018 "ACME Supermarkets"              "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES, INC."        0
2019 "ACME Supermarkets"              "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2020 "ACME Supermarkets"              "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2021 "ACME Supermarkets"              "URSTADT BIDDLE PROPERTIESURSTADT BIDDLE PROPERTIES INC."         0
2009 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               0
2010 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               0
2011 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               1
2012 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               1
2013 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               1
2014 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               1
2015 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               1
2016 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               0
2017 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               0
2018 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               0
2019 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               0
2020 "ADVANCE AUTO PARTS INC"         "STANDARD MOTOR PRODSSTANDARD MOTOR PRODUCTS, INC."               0
2013 "AGCO Corp"                      "RESOURCES CONNECTION, INC."                                      0
2014 "AGCO Corp"                      "RESOURCES CONNECTION, INC."                                      0
2018 "AGCO Corp"                      "DANA INCDANA, INC."                                              1
2019 "AGCO Corp"                      "DANA INCDANA INCORPORATED"                                       0
2020 "AGCO Corp"                      "DANA INCDANA INCORPORATED"                                       0
2021 "AGCO Corp"                      "DANA INCDANA INCORPORATED"                                       0
2012 "AK STEEL HOLDING CORP"          "SUNCOKE ENERGY INCSUNCOKE ENERGY, INC."                          1
2013 "AK STEEL HOLDING CORP"          "SUNCOKE ENERGY INCSUNCOKE ENERGY, INC."                          1
2014 "AK Steel Corp"                  "SUNCOKE ENERGY INCSUNCOKE ENERGY, INC."                          1
2015 "AK Steel Corp"                  "SUNCOKE ENERGY INCSUNCOKE ENERGY, INC."                          1
2016 "AK Steel Corp"                  "SUNCOKE ENERGY INCSUNCOKE ENERGY, INC."                          1
2017 "AK Steel Corp"                  "SUNCOKE ENERGY INCSUNCOKE ENERGY, INC."                          1
2018 "AK Steel Corp"                  "SUNCOKE ENERGY INCSUNCOKE ENERGY, INC."                          1
2019 "AK Steel Corp"                  "CLEVELAND-CLIFFS INCCLEVELAND-CLIFFS INC."                       0
2019 "AK Steel Corp"                  "SUNCOKE ENERGY INCSUNCOKE ENERGY, INC."                          0
2007 "ALCATEL-LUCENT -ADR"            "COMMSCOPE, INC."                                                 0
2007 "ALCATEL-LUCENT -ADR"            "CATAPULT COMMUNICATIONS CORPCATAPULT COMMUNICATIONS CORP."       0
2008 "ALCATEL-LUCENT -ADR"            "CATAPULT COMMUNICATIONS CORPCATAPULT COMMUNICATIONS CORPORATION" 0
2008 "ALCATEL-LUCENT -ADR"            "EXAR CORPEXAR CORPORATION"                                       0
2013 "ALCATEL-LUCENT -ADR"            "SANMINA CORPSANMINA CORPORATION"                                 0
2011 "ALLIANCE HEALTHCARE SVCS INC"   "MERCK & COMERCK & CO., INC."                                     1
2012 "ALLIANCE HEALTHCARE SVCS INC"   "MERCK & COMERCK & CO., INC."                                     1
2013 "ALLIANCE HEALTHCARE SVCS INC"   "MERCK & COMERCK & CO., INC."                                     1
2007 "ALLTEL Corp"                    "CONVERGYS CORPCONVERGYS CORP."                                   0
2007 "ALTRIA GROUP INC"               "UNIVERSAL CORP/VAUNIVERSAL CORP."                                0
2008 "ALTRIA GROUP INC"               "UNIVERSAL CORP/VAUNIVERSAL CORP."                                0
2017 "ALTRIA GROUP INC"               "UNIVERSAL CORP/VAUNIVERSAL CORPORATION"                          0
2018 "ALTRIA GROUP INC"               "UNIVERSAL CORP/VAUNIVERSAL CORP."                                0
2019 "ALTRIA GROUP INC"               "UNIVERSAL CORP/VAUNIVERSAL CORPORATION"                          0
2020 "ALTRIA GROUP INC"               "UNIVERSAL CORP/VAUNIVERSAL CORPORATION"                          0
2021 "ALTRIA GROUP INC"               "UNIVERSAL CORP/VAUNIVERSAL CORPORATION"                          0
2012 "AMC ShowPlace Theaters, Inc"    "NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC."      0
end

For example, "WEINGARTEN REALTY INVSTWEINGARTEN REALTY INVESTORS" is an interlocked breached supplier (Intbreached_supplierpre=1) in 2016. I want the variable Intbreached_customerpre=1 for the same company when it is a customer in the year 2016).
Note: I have IDs associated with suppliers, but not with customers. I am not sure whether as a first step I should create an ID for customers using suppliers IDs.

Your help is much appreciated!

[Clyde Schechter]

Fortunately, your explanation is very clear. Unfortunately, your example data does not really support investigation of this problem because there are no instances at all of a customer who is also a supplier. So consider the code shown below as untested.

One assumption is necessary for your problem to be solvable, and the code verifies this assumption: since there can be more than one observation for the same Supplier in the same Year, it must be the case that all such observations give the same value for Intbreached_supplierpre. If that were not true, then there would be no way to know what value to assign to Intbreached_customer for that Customer Year combination.

frame put Supplier Year Intbreached_supplierpre, into(working)
frame working {
    by Supplier Year (Intbreached_supplierpre), sort: ///
        assert Intbreached_supplierpre[1] == Intbreached_supplierpre[_N]
    by Supplier Year: keep if _n == 1
}

frlink m:1 Customer Year, frame(working Supplier Year)
frget Intbreached_customer = Intbreached_supplierpre

Added: While it would probably be a good idea to create a common numeric identifier for customers and suppliers, it is not strictly needed for this particular problem. How useful it would be to do that really depends on what you're going to do next. Suffice it to say that most work with panel data eventually requires using -xt- commands, and -xt- commands require numeric identifiers.

[Christelle Alkhoury]

Thank you Clyde for your swift support!
I tried the codes, however the last line resulted in an error and I am not sure how to fix it. Please check it below.

frget Intbreached_customer = Intbreached_supplierpre
option from() required
r(198);

[Clyde Schechter]

Sorry, that was a copy/paste error on my part. The last line should be

frget Intbreached_customer = Intbreached_supplierpre, from(working)

[Christelle Alkhoury]

Thank you for the adjustment Clyde.
All the observations for the new variable are = "."
Is there another way to do it or maybe some part of the code that we could adjust?

[Clyde Schechter]

While the code given earlier is not really tested and may be wrong, I think the problem is actually with your data.

As I indicated, in the example data, there were no Suppliers who also appeared as Customers. Perhaps the same is actually true in the full data. Remember that with text names, things can go badly wrong. For example if you have a customer "ABC Corp." and a supplier "ABC Corp" (note the missing .) those will not match. Nor will "ABC Corp" match with "ABC Co., Inc" nor with "ABC CORP", nor "Abc Corp", nor "ABC CORP" (note extra space), etc. These kinds of mismatch are very common when we use text to identify entities in our data. I'm particularly suspicious that you are facing this problem with your data because in the example data I see that all of the Supplier names are in all upper case, whereas many of the Customer names are in mixed case. This suggests that perhaps they came from different data sources and the files were put together without first harmonizing the use of capitalization and punctuation, etc. Also some of the Supplier names are strange in their own right, as they seem to have the name repeated, sometimes with a tag added on the end, e.g. NATIONAL RETAIL PROPERTIESNATIONAL RETAIL PROPERTIES, INC. But nothing like that is seen in the example Customer names. Data like that can not be properly matched to each other.

So I suggest you start by doing the following: for both variables make everything upper case, and remove all punctuation and supernumerary spaces.

foreach v of varlist Customer Supplier {
    replace `v' = upper(`v')  // MAKE EVERYTHING UPPER CASE
    replace `v' = ustrregexra(`v', "[^a-zA-Z0-9]", " ") // ELIMINATE PUNCTUATION
    replace `v' = trim(itrim(`v')) // REMOVE REDUNDANT SPACES
}

and then try the code in #2 on the results of that.

If that still produces no results, then you need to create a new example data set for me that includes some observations with Suppliers that you believe have matching Customers, and your example data set must include the observations for both of them so I have something to work with when I try to troubleshoot the code..

Now, and I suspect this will actually happen, it may be that the data cleaning I suggested just above will solve part nof the problem, but still leave many unmatched that should be matched. Those could represent things like spelling errors, or abbreviations vs spellouts (CORP. vs CORPORATION)--those things cannot be solved with simple data cleaning. If you have problems like those in the data set, and if there are too many of them for you to just write out some -replace- commands that will fix them, then we may need to try fuzzy matching. Better still, if each of these firms has a standard numeric code identifier like a CUSIP or ISIN or DUNS number or something of that nature, it would be best to add those to the data set and rely on those, rather than these troublesome names.