Creating new variables based on string categories in other variables

[Zora Ng]

I'm trying to clean and restructure a poorly designed dataset on industry and sector classifications. I was provided with a spreadsheet of businesses, one per row, which could have selected any number of 24 main industry categories and 112 subcategories. I want to create 24 binary main industry categories and 112 subcategories. .
The data provided was a spreadsheet where all industry selections were presented in a single cell for main category, and a single cell for subcategory, separated by commas. I have separated them out using the comma delimiter. This created 6 main category variables, but with industries randomly ordered/distributed throughout the new variables (i.e. the businesses selected up to six main categories) and 17 subcategory variables, again with the names of the subcategories randomly ordered and distributed across the 17 variables.
These variables are currently mcat1-6 and scat1-17, and are string variables, where the values are the name of an industry selected by the business, e.g. "Accommodation Food Services Travel and Tourism". Where the industry selected fewer than 6 (or 17) categories, there is just blanks.

I want to generate one variable for each industry category, e.g. for "Accommodation Food Services Travel and Tourism" I had hoped I could use my usual approach with a numeric variable but using quotes:

gen afstt=0
replace afstt=1 if ///
mcat1=="Accommodation Food Services Travel and Tourism" | ///
mcat2=="Accommodation Food Services Travel and Tourism" | ///
mcat3=="Accommodation Food Services Travel and Tourism" | ///
mcat4=="Accommodation Food Services Travel and Tourism" | ///
mcat5=="Accommodation Food Services Travel and Tourism" | ///
mcat6=="Accommodation Food Services Travel and Tourism"

This runs, but creates zero changes. I guess the 'generate' syntax is not recognising the string content of the variables?

I did not use encode because that results in different numeric codes for the same industry across the 23 different mcat/scat variables, which would make it even more complicated to create the variables.
I'm seeking a way to generate the variables based on the actual string content across different variables, hopefully someone has some good suggestions?
I have looked up the existing discussions but haven't found anything that specifically answers my question. Thanks in advance!

[Mike Lacy]

Your description of your problem and the structure of your data is difficult to understand. (Having a colleague look at a description before posting is always a good idea.) Moreover, while I think I understand the general kind of problem you face, giving you precise and relevant help would be much easier if, per the StataList FAQ, you had offered a data example.

Here's a sketch of the kind of thing that *might* be relevant for your situation:

local industrycat1 = "Some Distinctive Text"
local industrycat2 = "Some Kind of Text"
...
local industrycat24 = "Some Other Text"
//
forval i = 1/24 {
   gen byte industry`i' = strpos(YourMainCategoryVariable, "`industry`i'") > 0
}

A similar kind of approach might work for your subcategories.

I'm not going to offer any more precise detail here, as doing so efficiently and accurately would depend on seeing an actual example of your data. At worst, what I have suggested may be quite irrelevant or make quite erroneous assumptions about your data.

[Nick Cox]

I agree with Mike Lacy I offer only the small point that your displayed code could be trimmed to

gen afstt = inlist("Accommodation Food Services Travel and Tourism", mcat1, mcat2, mcat3, mcat4, mcat5, mcat6)

although as Mike points out it seems more likely that you need strpos().

See also https://journals.sagepub.com/doi/pdf...867X1101100308

[Zora Ng]

Your description of your problem and the structure of your data is difficult to understand. (Having a colleague look at a description before posting is always a good idea.) Moreover, while I think I understand the general kind of problem you face, giving you precise and relevant help would be much easier if, per the StataList FAQ, you had offered a data example.

Here's a sketch of the kind of thing that *might* be relevant for your situation:

local industrycat1 = "Some Distinctive Text"
local industrycat2 = "Some Kind of Text"
...
local industrycat24 = "Some Other Text"
//
forval i = 1/24 {
gen byte industry`i' = strpos(YourMainCategoryVariable, "`industry`i'") > 0
}

A similar kind of approach might work for your subcategories.

I'm not going to offer any more precise detail here, as doing so efficiently and accurately would depend on seeing an actual example of your data. At worst, what I have suggested may be quite irrelevant or make quite erroneous assumptions about your data.

Sorry, I thought I described it quite well in words, but 'word-describing brain' and 'data-describing brain' are really different. I don't have any colleagues to consult with on data analysis either.

But say for example, you have a survey where you have multichoice options for an answer, but can choose as many answers as you want to a question.
And instead of outputting binary variables for each answer choice, the spreadsheet gives you a single cell for that answer for each respondent, with all their ticked options in one cell but comma-separated, as strings.
Like this;
Example A:

UID	industry
1	construction,admin,electricity,transport
2	professional,design
3	electricity,wastewater,telecoms
4	arts,telecoms,information

So if you use the comma delimiters to split out cells, it gives you something maybe even more annoying like this. I did this because thought there would be a way to select on string content to recode into binary variables for each industry.

Example B:

UID	industry1	industry2	industry3	industry4
1	construction	admin	electricity	transport
2	professional	design
3	electricity	wastewater	telecoms
4	arts	telecoms	information

What would you do to turn these into binary variables for each industry, either using the data from Example A or Example B? I feel like there's got to be a common way to do this because I'm always being given weird survey outputs like this, but am only used to working with 'normally' structured datasets.

[Clyde Schechter]

I think this does what you want:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte uid str40 industry
1 "construction,admin,electricity,transport"
2 "professional,design"                     
3 "electricity,wastewater,telecoms"         
4 "arts,telecoms,information"               
end

// FIRST GENERATE A LIST OF ALL THE INDUSTRIES
preserve
split industry, parse(,) generate(ind)
drop industry
reshape long ind, i(uid)
keep ind
duplicates drop
drop if missing(ind)
levelsof ind, local(industries)
restore

// NOW CREATE A DICHOTOMOUS INDICATOR FOR EACH INDUSTRY
foreach i of local industries {
    local ii = strtoname(`"`i'"')
    gen `ii' = !!strpos(industry, `"`i'"')
}

In the future, when showing example data, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Clyde Schechter]

Sorry, I thought I described it quite well in words, but 'word-describing brain' and 'data-describing brain' are really different.

I'm sure you did your best. But, frankly, even the most carefully thought-out descriptions of data in words are only occasionally adequate. And even when they are adequate to describe the data, they generally omit the metadata, which is even harder to express in words, and is sometimes just as important. So, especially since it is so easy to just use the -dataex- command to provide those who respond to you with code that can create a faithful replica of a part of your data set, it isn't worth all the effort it takes to provide a good description in words, or to generate tables that are of limited usefulness, or screenshots, which are almost entirely useless. Do it the easy and effective way: use -dataex-.

[Zora Ng]

I think this does what you want:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte uid str40 industry
1 "construction,admin,electricity,transport"
2 "professional,design"
3 "electricity,wastewater,telecoms"
4 "arts,telecoms,information"
end

// FIRST GENERATE A LIST OF ALL THE INDUSTRIES
preserve
split industry, parse(,) generate(ind)
drop industry
reshape long ind, i(uid)
keep ind
duplicates drop
drop if missing(ind)
levelsof ind, local(industries)
restore

// NOW CREATE A DICHOTOMOUS INDICATOR FOR EACH INDUSTRY
foreach i of local industries {
local ii = strtoname(`"`i'"')
gen `ii' = !!strpos(industry, `"`i'"')
}

In the future, when showing example data, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Thank you for the tips! Sorry for being such a n00b