Replacing dummy variables = 1 when a firm's activity is classified within a certain SIC code

[Giovanni Coppola]

Hi everyone, I am asking some help for the construction of a formula that maybe has some errors. I generated a dummy variable which should be 1 when firms'activities lie within a certain SIC code range (SIC1 for the primary activity; SIC2 for the secondary activity), but when I try to do so by using the following formula, no changes are applied to the variable. Specifically I used:


"replace LitigationRisk = 1 if YEAR == 2013 & inrange(SIC1|SIC2, 3600, 3674)"



What's wrong? I guess there are some troubles with the format of SIC1 and 2 variables. Here are the data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str117 CompanyName int YEAR str11 CountryofIncorporation float(SIC1 SIC2 LitigationRisk)
"AMAG Austria Metall AG"                        2013 "Austria" 177   . 0
"AMAG Austria Metall AG"                        2014 "Austria"   .   . .
"AMAG Austria Metall AG"                        2015 "Austria"   .   . .
"AMAG Austria Metall AG"                        2016 "Austria"   .   . .
"AMAG Austria Metall AG"                        2017 "Austria"   .   . .
"AMAG Austria Metall AG"                        2018 "Austria"   .   . .
"AMAG Austria Metall AG"                        2019 "Austria"   .   . .
"AT & S Austria Technologie & Systemtechnik AG" 2013 "Austria" 249   . 0
"AT & S Austria Technologie & Systemtechnik AG" 2014 "Austria"   .   . .
"AT & S Austria Technologie & Systemtechnik AG" 2015 "Austria"   .   . .
"AT & S Austria Technologie & Systemtechnik AG" 2016 "Austria"   .   . .
"AT & S Austria Technologie & Systemtechnik AG" 2017 "Austria"   .   . .
"AT & S Austria Technologie & Systemtechnik AG" 2018 "Austria"   .   . .
"AT & S Austria Technologie & Systemtechnik AG" 2019 "Austria"   .   . .
"Agrana Beteiligungs AG"                        2013 "Austria"  61   . 0
"Agrana Beteiligungs AG"                        2014 "Austria"   .   . .
"Agrana Beteiligungs AG"                        2015 "Austria"   .   . .
"Agrana Beteiligungs AG"                        2016 "Austria"   .   . .
"Agrana Beteiligungs AG"                        2017 "Austria"   .   . .
"Agrana Beteiligungs AG"                        2018 "Austria"   .   . .
"Agrana Beteiligungs AG"                        2019 "Austria"   .   . .
"Andritz AG"                                    2013 "Austria" 215 197 0
"Andritz AG"                                    2014 "Austria"   .   . .
"Andritz AG"                                    2015 "Austria"   .   . .
"Andritz AG"                                    2016 "Austria"   .   . .
"Andritz AG"                                    2017 "Austria"   .   . .
"Andritz AG"                                    2018 "Austria"   .   . .
"Andritz AG"                                    2019 "Austria"   .   . .
"Asamer Baustoffe AG"                           2013 "Austria"   .   . 0
"Asamer Baustoffe AG"                           2014 "Austria"   .   . .
"Asamer Baustoffe AG"                           2015 "Austria"   .   . .
"Asamer Baustoffe AG"                           2016 "Austria"   .   . .
"Asamer Baustoffe AG"                           2017 "Austria"   .   . .
"Asamer Baustoffe AG"                           2018 "Austria"   .   . .
"Asamer Baustoffe AG"                           2019 "Austria"   .   . .
"Athos Immobilien AG"                           2013 "Austria" 422   . 0
"Athos Immobilien AG"                           2014 "Austria"   .   . .
"Athos Immobilien AG"                           2015 "Austria"   .   . .
"Athos Immobilien AG"                           2016 "Austria"   .   . .
"Athos Immobilien AG"                           2017 "Austria"   .   . .
"Athos Immobilien AG"                           2018 "Austria"   .   . .
"Athos Immobilien AG"                           2019 "Austria"   .   . .
"Aventa AG"                                     2013 "Austria"   .   . 0
"Aventa AG"                                     2014 "Austria"   .   . .
"Aventa AG"                                     2015 "Austria"   .   . .
"Aventa AG"                                     2016 "Austria"   .   . .
"Aventa AG"                                     2017 "Austria"   .   . .
"Aventa AG"                                     2018 "Austria"   .   . .
"Aventa AG"                                     2019 "Austria"   .   . .
"Burgenland Holding AG"                         2013 "Austria" 327 380 0
"Burgenland Holding AG"                         2014 "Austria"   .   . .
"Burgenland Holding AG"                         2015 "Austria"   .   . .
"Burgenland Holding AG"                         2016 "Austria"   .   . .
"Burgenland Holding AG"                         2017 "Austria"   .   . .
"Burgenland Holding AG"                         2018 "Austria"   .   . .
"Burgenland Holding AG"                         2019 "Austria"   .   . .
"CA Immobilien Anlagen AG"                      2013 "Austria" 414 341 0
"CA Immobilien Anlagen AG"                      2014 "Austria"   .   . .
"CA Immobilien Anlagen AG"                      2015 "Austria"   .   . .
"CA Immobilien Anlagen AG"                      2016 "Austria"   .   . .
"CA Immobilien Anlagen AG"                      2017 "Austria"   .   . .
"CA Immobilien Anlagen AG"                      2018 "Austria"   .   . .
"CA Immobilien Anlagen AG"                      2019 "Austria"   .   . .
"Cleen Energy AG"                               2013 "Austria"   .   . 0
"Cleen Energy AG"                               2014 "Austria"   .   . .
"Cleen Energy AG"                               2015 "Austria"   .   . .
"Cleen Energy AG"                               2016 "Austria"   .   . .
"Cleen Energy AG"                               2017 "Austria"   .   . .
"Cleen Energy AG"                               2018 "Austria"   .   . .
"Cleen Energy AG"                               2019 "Austria"   .   . .
"DO & CO AG"                                    2013 "Austria" 398   . 0
"DO & CO AG"                                    2014 "Austria"   .   . .
"DO & CO AG"                                    2015 "Austria"   .   . .
"DO & CO AG"                                    2016 "Austria"   .   . .
"DO & CO AG"                                    2017 "Austria"   .   . .
"DO & CO AG"                                    2018 "Austria"   .   . .
"DO & CO AG"                                    2019 "Austria"   .   . .
"EVN AG"                                        2013 "Austria" 327 269 0
"EVN AG"                                        2014 "Austria"   .   . .
"EVN AG"                                        2015 "Austria"   .   . .
"EVN AG"                                        2016 "Austria"   .   . .
"EVN AG"                                        2017 "Austria"   .   . .
"EVN AG"                                        2018 "Austria"   .   . .
"EVN AG"                                        2019 "Austria"   .   . .
"FACC AG"                                       2013 "Austria" 260   . 0
"FACC AG"                                       2014 "Austria"   .   . .
"FACC AG"                                       2015 "Austria"   .   . .
"FACC AG"                                       2016 "Austria"   .   . .
"FACC AG"                                       2017 "Austria"   .   . .
"FACC AG"                                       2018 "Austria"   .   . .
"FACC AG"                                       2019 "Austria"   .   . .
"Fabasoft AG"                                   2013 "Austria" 436   . 0
"Fabasoft AG"                                   2014 "Austria"   .   . .
"Fabasoft AG"                                   2015 "Austria"   .   . .
"Fabasoft AG"                                   2016 "Austria"   .   . .
"Fabasoft AG"                                   2017 "Austria"   .   . .
"Fabasoft AG"                                   2018 "Austria"   .   . .
"Fabasoft AG"                                   2019 "Austria"   .   . .
"Flughafen Wien AG"                             2013 "Austria" 311   . 0
"Flughafen Wien AG"                             2014 "Austria"   .   . .
end
label values SIC1 SIC1
label def SIC1 61 "2062", modify
label def SIC1 177 "3354", modify
label def SIC1 215 "3554", modify
label def SIC1 249 "3672", modify
label def SIC1 260 "3724", modify
label def SIC1 311 "4581", modify
label def SIC1 327 "4911", modify
label def SIC1 398 "5812", modify
label def SIC1 414 "6513", modify
label def SIC1 422 "6798", modify
label def SIC1 436 "7372", modify
label values SIC2 SIC2
label def SIC2 197 "3559", modify
label def SIC2 269 "4924", modify
label def SIC2 341 "6512; 6519", modify
label def SIC2 380 "6719; 4923", modify

Thanks a lot for the answers!

[Andrew Musau]

It appears that you are specifying values referencing the labels of the SIC codes and not their values. You need

decode SIC1, g(LSIC1)
decode SIC2, g(LSIC2)
destring LSIC1, replace
destring LSIC2, replace

then refer to LSIC1 and LSIC2 in place of SIC1 and SIC2, respectively.

[Giovanni Coppola]

Hi Andrew, thanks for the answer, I was able to solve the problem looking at another post here. However, I had multiple SIC codes within a single cell that disappeared when I used the destring command. Is there a way to maintain and use these codes within a single cell?

For example firm1 may have two codes for SIC1 (primary activity) and 3 for SIC2 (secondary activity)

Thanks!

[Nick Cox]

The explanation lies elsewhere.

SIC1 | SIC2 is a logical expression which will evaluate as 1 if true and 0 if false and either way it is never going to be within the interval [3600, 3674]

To see this directly, type at Stata

di 42 | 666

The result is 1. You asked to evaluate a logical expression and the result can only be 1 or 0. More at https://www.stata.com/support/faqs/d...rue-and-false/

It's worse than that because SIC1 and SIC2 are encoded variables so Stata will work with arbitrary numeric codes and ignore the value labels that you should want to work with.

Even worse, the codings of SIC1 and SIC2 are not identical, so they can't be compared with each other.

A previous encode by you or someone else was misguided. I can see why that was done because some firms straddle SIC codes, but it took you down a road which you must now retrace.

The missing values here are irrelevant to you, but that doesn't stop SIC1 | SIC2 returning true also.

So, several problems there but I think this is soluble.

You need to

1. Reverse the encode.

2. Strip semi-colons which can only complicate comparisons.

3. Put the two SIC variables together somehow. Concatenation is one way.

4. Search the "words" of the combined variable for codes between 3600 and 3674,

* Example generated by -dataex-. For more info, type help dataex
clear
input str117 CompanyName int YEAR str11 CountryofIncorporation float(SIC1 SIC2 LitigationRisk)
"Andritz AG"               2013 "Austria" 215 197 0
"Burgenland Holding AG"    2013 "Austria" 327 380 0
"CA Immobilien Anlagen AG" 2013 "Austria" 414 341 0
"EVN AG"                   2013 "Austria" 327 269 0
end
label values SIC1 SIC1
label def SIC1 215 "3554", modify
label def SIC1 327 "4911", modify
label def SIC1 414 "6513", modify
label values SIC2 SIC2
label def SIC2 197 "3559", modify
label def SIC2 269 "4924", modify
label def SIC2 341 "6512; 6519", modify
label def SIC2 380 "6719; 4923", modify

forval j  = 1/2 {
    decode SIC`j', gen(sic`j')
    replace sic`j' = subinstr(sic`j', ";", " ", .)
}

egen both = concat(sic1 sic2), p(" ")

* what is maximum number of codes? it is the maximum number of words in the combination.
gen wc = wordcount(both)
su wc
local max = r(max)
drop wc

 gen is36003674 = 0
 
 forval j = 1/`max' {
     replace is36003674 = 1 if inrange(word(both, `j'), "3600", "3674")
 }

 list both is
 
 
     +----------------------------+
     |            both   is3600~4 |
     |----------------------------|
  1. |       3554 3559          0 |
  2. | 4911 6719  4923          0 |
  3. | 6513 6512  6519          0 |
  4. |       4911 4924          0 |
     +----------------------------+

.

Wanting a particular year too is just an extra criterion.

Watch out: your current SIC1 SIC2 are essentially useless not only for this purpose but for most others.

EDIT: This was long in the writing and previous replies were not visible while that was happening. I think Andrew Musau has the main point but my reply covers the difficulties raised in reply to that.

[Giovanni Coppola]

Thanks Nick! I'll try to fix based on your suggestions.