Multiple response variable - How to create dummies for each option in the question?

[danishussalam]

Hi everyone,

I want to analyze a variable in a questionnaire that goes like this, have you received any information to increase the cultivation of any crops in the last 12 months? if yes, which crops? Then for each crop, the individual is supposed to say Yes/No.

1- Black Beans
2- Organge Fleshed sweet potato
3- Amaranth
4- Pum
..
..
13 - Passion Fruit
99 others

But I don't have multiple binary variables for each crop. Rather in the data I just have 1 variable which mentions the id of the food crop for which the hh said yes. Is there a way I can create 14 binary variables one for each crop from this variable? Any help on this will be highly appreciated.

* Example generated by -dataex-. For more info, type help dataex
clear
input str41 key str29 section_4q411
"uuid:18f5276d-0ca0-45ab-972a-90e6fd9a591b" "99 10 11 2"               
"uuid:6209ef69-a716-4446-856f-c580be054e3f" "99"                       
"uuid:8143d8df-81a3-4bdd-a3a1-cca84dbb68d8" "2 1"                      
"uuid:00a8a941-6b7a-4f83-89fe-18629789e083" "2"                        
"uuid:d802962b-901b-4a6d-9d07-9647a110c416" "99"                       
"uuid:3aea104f-8455-4b7a-995c-2c5fcf26f47e" "99"                       
"uuid:015aad6a-3116-46dd-b369-fd8e85021ec3" "3 10 11 2 8"              
"uuid:c65e90ed-c0b8-4b7f-8f48-6af1e9886b02" "10 11"                    
"uuid:f120b3b9-7885-478b-8e9f-475831a296a3" "99"                       
"uuid:59f8ca4b-5af2-48c8-80e1-98a787fe02d3" "99"                       
"uuid:b0afdb2a-f07b-46e6-979f-605f26023c57" "3 99 13 11 10"            
"uuid:80b75c82-077e-4b37-999c-81720dde721b" "99"                       
"uuid:59142c30-f967-41d8-a9c2-40e16496914c" "1 2 10 13 12 11 4"        
"uuid:51778c9c-14e2-49e5-bdf7-e5187ed9c58f" "1 4 3 11 6"               
"uuid:72416c6e-6b8c-47f6-87e0-3bb98aac5263" "99"                       
"uuid:deae49ec-ef55-4211-a36b-af22e7ea3248" "2 1 13 11 3"              
"uuid:0eb236e1-3c10-4d22-80fa-be6388f9c5a6" "1 99"                     
"uuid:e363d397-501a-4235-acf5-f5b0fa34c5b9" "99"                       
"uuid:ec1650e1-8fee-4903-b5ba-ec7f13f42989" "99"                       
"uuid:aa346389-41de-4b35-a321-acc910229517" "11 10 13 99"              
"uuid:2baeafb6-d97b-418e-bc62-059645ac6403" "2"                        
"uuid:f308b44f-6976-4491-ba52-424edee845b4" "10 11 13 3"               
"uuid:5a96889f-6856-4dd4-bcb8-5bf9c693130f" "99"                       
"uuid:73c1b02f-cc43-47a9-9521-f32806998053" "1 99"                     
"uuid:c362654e-6b15-4168-9e17-211ed3f2420f" "99 11"                    
"uuid:e34e9cb3-b3eb-4810-8655-59b48501832f" "99"                       
"uuid:51c60d38-61ae-4aa2-b75c-9675dbaea8f1" "10 11"                    
"uuid:14129e31-05cb-49a6-8273-81522b1eade7" "11 99"                    
"uuid:fbaa31e1-a2ad-41ad-9367-3c27cbeb83d8" "4"                        
"uuid:1f179769-398f-4d17-8c15-007d69b076c6" "11 99"                    
"uuid:a15166f8-710c-4fa6-921e-a50e7af4c5f3" "1 99"                     
"uuid:343d8e4a-da2a-4b55-910d-9293750a536d" "1 3 8"                    
"uuid:c23467fa-27ea-4fd3-b593-e2e025bf4822" "99 13"                    
"uuid:08f0ce69-8d8a-42e5-9e9f-d72c28faa8a0" "2 11 13"                  
"uuid:1de45b2b-4f82-4db5-8191-f7af434933be" "99"                       
"uuid:fc3b1448-0cf4-4247-9e5d-3c2da51a1684" "99 9"                     
"uuid:c6e396b9-41d6-454e-8bff-322cb04463c7" "99 11 13"                 
"uuid:c3888859-938f-4a8b-816e-04ab3d3a3bb1" "10 3 13"                  
"uuid:1253de17-72a5-4236-b4c0-916e64b831a0" "99"                       
"uuid:c78ac436-598d-450e-b96e-1086a20e91db" "99"                       
"uuid:012c972e-97ff-4a52-a82f-27ebe894e175" "99"                       
"uuid:a474d623-d5b5-43da-bf03-39083a761e96" "99"                       
"uuid:f9d5d675-fb28-4851-9fe7-da899d44e65a" "7 8 1"                    
"uuid:c8649271-87b6-45c5-b9db-efc2a1b0d281" "2"                        
"uuid:59f8249a-084d-456a-9297-36e760bbd1d3" "1 8 2"                    
"uuid:dc9f25fa-7d5f-401c-a89f-9abefd833831" "99 11"                    
"uuid:13b91914-1768-4571-b835-ddcf70ca3c0b" "3"                        
"uuid:59661227-5a5f-434c-ba3d-993ea0ddc2a9" "1 2 3 4 5 8 9 10 11 12 13"
"uuid:1d49101e-7def-4c78-a960-dc9d4344c3ed" "99"                       
"uuid:badb370d-50f8-4c93-8afa-3c45f421ddef" "99 10 11 3"               
"uuid:06866240-a45a-4356-aa24-c8ae0f128236" "7 10 11"                  
"uuid:13d0a9a1-4245-4ee1-8f2b-b1332a0e0a05" "2 11"                     
"uuid:2a68d257-9c12-4cb7-bff8-4a9ba350f79f" "2 99 3 13 11"             
"uuid:6796fc75-8ad0-40e5-af7c-006ff5a8f1a4" "99"                       
"uuid:0c914404-21f2-43ed-911b-e64b49cbce6a" "11"                       
"uuid:0be832bc-33a7-4405-862f-17946bc2ce2b" "11"                       
"uuid:bbd161bb-ff6e-406e-b289-460bf8ceb6f1" "99"                       
"uuid:cfc13e21-61e3-4225-ae2b-e394532f98f0" "99"                       
"uuid:d86c37b0-ca55-4d97-bc32-21bc5f778b03" "99 11 10 13 1 2 3"        
"uuid:fcfd8c30-ba43-4a24-95da-557241d1607a" "99"                       
"uuid:3e57fe23-a2a6-4423-b8de-b170506926f6" "2"                        
"uuid:a35b6438-2c15-4566-99c2-021b2b0e612a" "99"                       
"uuid:cd39422c-2440-4c7a-87b5-1a749b3602b0" "99"                       
"uuid:56d0fbe6-7226-4f63-a0eb-89eeed95fec1" "4"                        
"uuid:354593d4-e1e6-4217-9a65-789a5bc45ada" "99"                       
"uuid:843c7df1-c9d8-474e-a3e0-b33f0ac46e70" "2 10 11"                  
"uuid:f92c8903-8b5b-4c6b-8b9a-a823e684437b" "99"                       
"uuid:be9ac7b4-828e-4b79-9265-397bec0ed2dc" "13 4 11 99"               
"uuid:7883299f-e2bf-4213-a2f6-3f62b6dba689" "3 4 11"                   
"uuid:07d589ea-c1df-4874-be7b-640f70ff8e87" "11 3"                     
"uuid:5c317b1a-299c-47b5-a6bc-8a217e75aa6f" "99"                       
"uuid:561f3796-7981-4e8e-ad36-c0adc4de5037" "11"                       
"uuid:ffab47c6-6927-4df9-ba44-f47fb56cbff0" "99"                       
"uuid:80dd584e-429a-4388-9eab-32643d51e239" "11"                       
"uuid:d8d4b54a-3899-4f57-b057-cf8a385a5ae2" "1 2 13 10"                
"uuid:dfd21a54-5bc9-497f-ba37-4174291e9d18" "99"                       
"uuid:420108df-02e3-40ec-914f-2637a035d4f8" "99"                       
"uuid:e4a62358-e0a0-4856-9958-d63d91fa3a49" "99"                       
"uuid:5583864d-09e9-4f1a-a55d-b0f034602ea2" "3 99"                     
"uuid:b715f761-9015-4f75-ab00-d1c638da7405" "99"                       
"uuid:e6d5378b-3f6c-4ba3-9600-917ed7756a85" "99"                       
"uuid:93ceb6e1-f6d9-4d72-bc1f-61cd24cf1af0" "1 2 11 9 13"              
"uuid:1b3fa421-c1c4-45a2-9f10-a565ee831b09" "99"                       
"uuid:f7cfcca0-37ca-46aa-bdcb-2767fbb94b26" "11 13 3 2 99"             
"uuid:e6fad88b-69d2-4db9-ba43-4ffefe97c64d" "2 99"                     
"uuid:792e3f5f-fa31-4050-bea6-43233b468080" "99"                       
"uuid:484c3cad-b726-4f4b-9ea8-9e5d17a835f5" "13 11 3 10 99"            
"uuid:a1ca38f6-0fbe-4200-ab72-bdf0e100cce5" "1 10"                     
"uuid:1e90e6af-693c-4d79-8fdc-376bd45a8998" "3 99"                     
"uuid:d1785dfc-e93b-42ad-ade5-c46cf610268f" "99 11"                    
"uuid:f20a3b69-d491-44f4-8666-f70d0094e8f7" "11 13 99"                 
"uuid:49c17548-b9f1-4aea-86ab-55ae31c167e7" "99"                       
"uuid:89207608-c704-4a5f-b103-13586aedc572" "4 2 10"                   
"uuid:c535e000-1b03-4861-b2cd-9e1a960ad18b" "1 2"                      
"uuid:13c2af52-e049-4c5c-bf66-a359f5666730" "99"                       
"uuid:c1aebf20-bfe7-4209-a09e-9bd9fe7b2c49" "3 4 6"                    
"uuid:b8e087aa-f126-403d-a16f-791ee4d9f74b" "99"                       
"uuid:d3a1cba3-ce61-41b9-b8d4-3e21446a99f1" "11 10 3 13 99"            
"uuid:feefbbf1-82ca-4d50-8666-a19dac370c04" "11 99"                    
"uuid:45aa4f5a-ca74-4b02-ad2e-48da8bfa26e2" "1 99"                     
end

[Nick Cox]

Here are two ways to do it. Note that #2 may appeal more but can be hard to get right. Thus `1` as text is present whenever 11 12 13 is an answer and 9 is present whenever 99 is an answer. And similar comments for 2 and 3. So we must check for e.g. " 1 " or " 9 ". But that won't work if "1" or "9" is at the beginning or end of a string, hence the device of adding spaces.

* Example generated by -dataex-. For more info, type help dataex
clear
input str41 key str29 section_4q411
"uuid:18f5276d-0ca0-45ab-972a-90e6fd9a591b" "99 10 11 2"               
"uuid:6209ef69-a716-4446-856f-c580be054e3f" "99"                       
"uuid:8143d8df-81a3-4bdd-a3a1-cca84dbb68d8" "2 1"                      
"uuid:00a8a941-6b7a-4f83-89fe-18629789e083" "2"                        
"uuid:d802962b-901b-4a6d-9d07-9647a110c416" "99"                       
"uuid:3aea104f-8455-4b7a-995c-2c5fcf26f47e" "99"                       
"uuid:015aad6a-3116-46dd-b369-fd8e85021ec3" "3 10 11 2 8"              
"uuid:c65e90ed-c0b8-4b7f-8f48-6af1e9886b02" "10 11"                    
"uuid:f120b3b9-7885-478b-8e9f-475831a296a3" "99"                       
"uuid:59f8ca4b-5af2-48c8-80e1-98a787fe02d3" "99"                       
"uuid:b0afdb2a-f07b-46e6-979f-605f26023c57" "3 99 13 11 10"            
"uuid:80b75c82-077e-4b37-999c-81720dde721b" "99"                       
"uuid:59142c30-f967-41d8-a9c2-40e16496914c" "1 2 10 13 12 11 4"        
"uuid:51778c9c-14e2-49e5-bdf7-e5187ed9c58f" "1 4 3 11 6"               
"uuid:72416c6e-6b8c-47f6-87e0-3bb98aac5263" "99"                       
"uuid:deae49ec-ef55-4211-a36b-af22e7ea3248" "2 1 13 11 3"              
"uuid:0eb236e1-3c10-4d22-80fa-be6388f9c5a6" "1 99"                     
"uuid:e363d397-501a-4235-acf5-f5b0fa34c5b9" "99"                       
"uuid:ec1650e1-8fee-4903-b5ba-ec7f13f42989" "99"                       
"uuid:aa346389-41de-4b35-a321-acc910229517" "11 10 13 99"              
"uuid:2baeafb6-d97b-418e-bc62-059645ac6403" "2"                        
"uuid:f308b44f-6976-4491-ba52-424edee845b4" "10 11 13 3"               
"uuid:5a96889f-6856-4dd4-bcb8-5bf9c693130f" "99"                       
"uuid:73c1b02f-cc43-47a9-9521-f32806998053" "1 99"                     
"uuid:c362654e-6b15-4168-9e17-211ed3f2420f" "99 11"                    
"uuid:e34e9cb3-b3eb-4810-8655-59b48501832f" "99"                       
"uuid:51c60d38-61ae-4aa2-b75c-9675dbaea8f1" "10 11"                    
"uuid:14129e31-05cb-49a6-8273-81522b1eade7" "11 99"                    
"uuid:fbaa31e1-a2ad-41ad-9367-3c27cbeb83d8" "4"                        
"uuid:1f179769-398f-4d17-8c15-007d69b076c6" "11 99"                    
"uuid:a15166f8-710c-4fa6-921e-a50e7af4c5f3" "1 99"                     
"uuid:343d8e4a-da2a-4b55-910d-9293750a536d" "1 3 8"                    
"uuid:c23467fa-27ea-4fd3-b593-e2e025bf4822" "99 13"                    
"uuid:08f0ce69-8d8a-42e5-9e9f-d72c28faa8a0" "2 11 13"                  
"uuid:1de45b2b-4f82-4db5-8191-f7af434933be" "99"                       
"uuid:fc3b1448-0cf4-4247-9e5d-3c2da51a1684" "99 9"                     
"uuid:c6e396b9-41d6-454e-8bff-322cb04463c7" "99 11 13"                 
"uuid:c3888859-938f-4a8b-816e-04ab3d3a3bb1" "10 3 13"                  
"uuid:1253de17-72a5-4236-b4c0-916e64b831a0" "99"                       
"uuid:c78ac436-598d-450e-b96e-1086a20e91db" "99"                       
"uuid:012c972e-97ff-4a52-a82f-27ebe894e175" "99"                       
"uuid:a474d623-d5b5-43da-bf03-39083a761e96" "99"                       
"uuid:f9d5d675-fb28-4851-9fe7-da899d44e65a" "7 8 1"                    
"uuid:c8649271-87b6-45c5-b9db-efc2a1b0d281" "2"                        
"uuid:59f8249a-084d-456a-9297-36e760bbd1d3" "1 8 2"                    
"uuid:dc9f25fa-7d5f-401c-a89f-9abefd833831" "99 11"                    
"uuid:13b91914-1768-4571-b835-ddcf70ca3c0b" "3"                        
"uuid:59661227-5a5f-434c-ba3d-993ea0ddc2a9" "1 2 3 4 5 8 9 10 11 12 13"
"uuid:1d49101e-7def-4c78-a960-dc9d4344c3ed" "99"                       
"uuid:badb370d-50f8-4c93-8afa-3c45f421ddef" "99 10 11 3"               
"uuid:06866240-a45a-4356-aa24-c8ae0f128236" "7 10 11"                  
"uuid:13d0a9a1-4245-4ee1-8f2b-b1332a0e0a05" "2 11"                     
"uuid:2a68d257-9c12-4cb7-bff8-4a9ba350f79f" "2 99 3 13 11"             
"uuid:6796fc75-8ad0-40e5-af7c-006ff5a8f1a4" "99"                       
"uuid:0c914404-21f2-43ed-911b-e64b49cbce6a" "11"                       
"uuid:0be832bc-33a7-4405-862f-17946bc2ce2b" "11"                       
"uuid:bbd161bb-ff6e-406e-b289-460bf8ceb6f1" "99"                       
"uuid:cfc13e21-61e3-4225-ae2b-e394532f98f0" "99"                       
"uuid:d86c37b0-ca55-4d97-bc32-21bc5f778b03" "99 11 10 13 1 2 3"        
"uuid:fcfd8c30-ba43-4a24-95da-557241d1607a" "99"                       
"uuid:3e57fe23-a2a6-4423-b8de-b170506926f6" "2"                        
"uuid:a35b6438-2c15-4566-99c2-021b2b0e612a" "99"                       
"uuid:cd39422c-2440-4c7a-87b5-1a749b3602b0" "99"                       
"uuid:56d0fbe6-7226-4f63-a0eb-89eeed95fec1" "4"                        
"uuid:354593d4-e1e6-4217-9a65-789a5bc45ada" "99"                       
"uuid:843c7df1-c9d8-474e-a3e0-b33f0ac46e70" "2 10 11"                  
"uuid:f92c8903-8b5b-4c6b-8b9a-a823e684437b" "99"                       
"uuid:be9ac7b4-828e-4b79-9265-397bec0ed2dc" "13 4 11 99"               
"uuid:7883299f-e2bf-4213-a2f6-3f62b6dba689" "3 4 11"                   
"uuid:07d589ea-c1df-4874-be7b-640f70ff8e87" "11 3"                     
"uuid:5c317b1a-299c-47b5-a6bc-8a217e75aa6f" "99"                       
"uuid:561f3796-7981-4e8e-ad36-c0adc4de5037" "11"                       
"uuid:ffab47c6-6927-4df9-ba44-f47fb56cbff0" "99"                       
"uuid:80dd584e-429a-4388-9eab-32643d51e239" "11"                       
"uuid:d8d4b54a-3899-4f57-b057-cf8a385a5ae2" "1 2 13 10"                
"uuid:dfd21a54-5bc9-497f-ba37-4174291e9d18" "99"                       
"uuid:420108df-02e3-40ec-914f-2637a035d4f8" "99"                       
"uuid:e4a62358-e0a0-4856-9958-d63d91fa3a49" "99"                       
"uuid:5583864d-09e9-4f1a-a55d-b0f034602ea2" "3 99"                     
"uuid:b715f761-9015-4f75-ab00-d1c638da7405" "99"                       
"uuid:e6d5378b-3f6c-4ba3-9600-917ed7756a85" "99"                       
"uuid:93ceb6e1-f6d9-4d72-bc1f-61cd24cf1af0" "1 2 11 9 13"              
"uuid:1b3fa421-c1c4-45a2-9f10-a565ee831b09" "99"                       
"uuid:f7cfcca0-37ca-46aa-bdcb-2767fbb94b26" "11 13 3 2 99"             
"uuid:e6fad88b-69d2-4db9-ba43-4ffefe97c64d" "2 99"                     
"uuid:792e3f5f-fa31-4050-bea6-43233b468080" "99"                       
"uuid:484c3cad-b726-4f4b-9ea8-9e5d17a835f5" "13 11 3 10 99"            
"uuid:a1ca38f6-0fbe-4200-ab72-bdf0e100cce5" "1 10"                     
"uuid:1e90e6af-693c-4d79-8fdc-376bd45a8998" "3 99"                     
"uuid:d1785dfc-e93b-42ad-ade5-c46cf610268f" "99 11"                    
"uuid:f20a3b69-d491-44f4-8666-f70d0094e8f7" "11 13 99"                 
"uuid:49c17548-b9f1-4aea-86ab-55ae31c167e7" "99"                       
"uuid:89207608-c704-4a5f-b103-13586aedc572" "4 2 10"                   
"uuid:c535e000-1b03-4861-b2cd-9e1a960ad18b" "1 2"                      
"uuid:13c2af52-e049-4c5c-bf66-a359f5666730" "99"                       
"uuid:c1aebf20-bfe7-4209-a09e-9bd9fe7b2c49" "3 4 6"                    
"uuid:b8e087aa-f126-403d-a16f-791ee4d9f74b" "99"                       
"uuid:d3a1cba3-ce61-41b9-b8d4-3e21446a99f1" "11 10 3 13 99"            
"uuid:feefbbf1-82ca-4d50-8666-a19dac370c04" "11 99"                    
"uuid:45aa4f5a-ca74-4b02-ad2e-48da8bfa26e2" "1 99"                     
end

* method 1 
split section_4q411, destring 
local args `r(varlist)'
local args : subinstr local args " " ",", all 

foreach x of num 1/13 99 { 
    gen is`x' = inlist(`x', `args')
}

su is* 

* method 2 
foreach x of numlist 1/13 99 { 
    gen IS`x' = strpos(" " + section_4q411 + " ", " `x' ") > 0 
    assert is`x' == IS`x'
}



    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
         is1 |        100         .17    .3775252          0          1
         is2 |        100         .22    .4163332          0          1
         is3 |        100          .2    .4020151          0          1
         is4 |        100         .09    .2876235          0          1
         is5 |        100         .01          .1          0          1
-------------+---------------------------------------------------------
         is6 |        100         .02    .1407053          0          1
         is7 |        100         .02    .1407053          0          1
         is8 |        100         .05    .2190429          0          1
         is9 |        100         .03    .1714466          0          1
        is10 |        100         .19    .3942772          0          1
-------------+---------------------------------------------------------
        is11 |        100         .37    .4852366          0          1
        is12 |        100         .02    .1407053          0          1
        is13 |        100         .19    .3942772          0          1
        is99 |        100         .64    .4824182          0          1

[Carlo Lazzaro]

An inefficient and computationally demanding approach may be:

. split section_4q411, p()
. foreach var of varlist section_4q411-section_4q41111 {
  2. destring  `var', gen(num_`var')
  3.   }
///to be repeated for each crop///
. gen var_crop_1=.

. foreach var of varlist num_section_4q4111-num_section_4q41111 {
  2. replace var_crop_1=1 if `var'==1
  3.   }

The more I use Stata, the more I notice that Nick's edge over my skills is unsormountable.
The good new is that I can still learn!

[danishussalam]

Thank you so much Nick Cox and Carlo Lazzaro. Both of your codes work and I am also able to understand the difference in approach. Thanks again for always being so helpful.

[Nick Cox]

We all can learn from each other. The trick of adding spaces is one that is easy to invent -- and even easier not to imagine or to forget. I was reminded of it by (I think) a posting by Joseph Coveney a while back.

[Nick Cox]

Browsing previous stuff I find that this is all mentioned somewhere within a 2003 paper https://www.stata-journal.com/articl...article=pr0008 -- although in detail many of the functions named still work but now have different names.

https://www.stata-journal.com/articl...article=pr0005 from 2002 was rewritten as https://www.stata-journal.com/articl...article=pr0074 in 2020

https://www.stata-journal.com/articl...article=pr0009 from 2003 has been rewritten and will appear in Stata Journal 21(4) shortly.

Perhaps that one should be rewritten, but the needed changes are fairly small.