Change ID codes to a corresponding different ID codes in a panel dataset

[Li An]

I have a dataset where the industries are identified with the NAIC codes. I want to change the NAIC codes to the more globally recognised SIC codes. I have an excel file that shows the corresponding values for each industry, however, the values are not unique.

[Clyde Schechter]

Well, this is a problem. Multiple NAIC codes can correspond to a single SIC code, and multiple SIC codes can correspond to a single NAIC code. So you cannot use -merge- for this problem. The -joinby- command can get you started. First import your Excel file with the NAIC-SIC correspondence, save it as a Stata file, then load in your data, and

joinby naic_code using correspondence_file

(replacing the italicized material by the actual variable and file names.)

With that done each observation in your original data will now be paired with all SIC codes that correspond to that observation's NAIC code. You will then have to decide how to proceed from there. If you need to select a unique SIC code for each of your original observations, then you will have to decide upon some rule for how to select the most appropriate one. I can't advise you on that--this is completely outside my area. But you can look in your discipline's literature to see how others have handled this problem, or consult an expert in your field about it.

[Ahmed A Arif]

There is no easy way to do this. I am not sure the Excel file you have is the one that is located at the U.S. Census Bureau website

https://www.census.gov/eos/www/naics...cordances.html

The Excel file lists the SIC and the corresponding NAICS codes.

[David Benson]

Hi Li An, and welcome to Statalist!

So people have tried different ways to map these. If in your sample you have publicly-traded firms (so you have their SIC codes) and you know their NAIC codes, you can code the NAIC to the most common SIC for that NAIC code (that is often done). You can also take the first one, and them manually inspect if that choice is "good enough" on average for you.

If you need to go deeper, take a look at the following papers:

• Converting Historical Industry Time Series Data from SIC to NAICS
• A Concordance Between Ten-Digit U.S. Harmonized System Codes and SIC/NAICS Product Classes and Industries
  • The 2nd paper lists a website with their data. That website no longer exists, but can be downloaded from the Internet Archive here