Table of frequency for multiple variables with common words

[andrew roder]

Hi All,

Can you help me generating a frequency table for the variable. I have splited the variable "Topthreeusecases" into 5 variables by splitting all words separated by comma in the variables "Topthreeusecases". I want to now generate a table of frequency but want to combine all the similar words in the five variables into one frequency table. Is there an easier way to do that.

copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str36 Topthreeusecases1 str37 Topthreeusecases2 str36 Topthreeusecases3 str37 Topthreeusecases4 str26 Topthreeusecases5
"digital facilitation"                 " brainstorming"                        " productivity and team collaboration" ""            ""                          
"collective intelligence"              " crowdsourcing"                        " co-creation"                         ""            ""                          
"collective intelligence"              " brainstorming"                        " digital facilitation"                " productivity and team    collaboration    "    " system thinking"          
"digital facilitation"                 " collective intelligence"              " productivity and team collaboration" ""            ""                          
"horizon scanning"                     " social media analysis"                ""                                     ""            ""                          
"brainstorming"                        " co-creation"                          " collective intelligence"             ""            ""                          
"solutions mapping"                    " ethnography"                          " co-creation"                         ""            ""                          
"design sprints"                       " brainstorming"                        " productivity and team collaboration" ""            ""                          
"digital facilitation"                 " brainstorming"                        " sensemaking"                         ""            ""                          
"collective intelligence"              " digital facilitation"                 " brainstorming"                       ""            ""                          
"horizon scanning"                     " foresight"                            ""                                     ""            ""                          
"design sprints"                       " design thinking"                      " productivity and team collaboration" ""            ""                          
"collective intelligence"              " brainstorming"                        " co-creation"                         ""            ""                          
"pilots"                               " digital facilitation"                 " data visualisation"                  ""            ""                          
"system thinking"                      ""                                      ""                                     ""            ""                          
"randomized controlled trials"         " other"                                ""                                     ""            ""                          
"minimal viable product (mvp)"         " prototyping"                          " proof of concept"                    " brainstorming"            " visual thinking & mapping"
"proof of concept"                     " prototyping"                          " minimal viable product (mvp)"        " solutions mapping"            " collective intelligence"  
"digital facilitation"                 ""                                      ""                                     ""            ""                          
"collective intelligence"              " (community) asset mapping"            " crowdsourcing"                       ""            ""                          
"collective intelligence"              " foresight"                            " future analysis"                     ""            ""                          
"data visualisation"                   " visual thinking & mapping"            ""                                     ""            ""                          
"foresight"                            " horizon scanning"                     " future analysis"                     ""            ""                          
"data visualisation"                   " productivity and team collaboration " " other"                               ""            ""                          
"crowdsourcing"                        ""                                      ""                                     ""            ""                          
"digital facilitation"                 ""                                      ""                                     ""            ""                          
"gamification"                         " design thinking"                      " participatory design"                ""            ""                          
"digital facilitation"                 " productivity and team collaboration"  ""                                     ""            ""                          
"sensemaking"                          " visual thinking & mapping"            " brainstorming"                       ""            ""                          
"digital facilitation"                 " productivity and team collaboration"  ""                                     ""            ""                          
"citizen science"                      " crowdsourcing"                        " collective intelligence"             ""            ""                          
"brainstorming"                        " digital facilitation"                 " collective intelligence"             ""            ""                          
"system thinking"                      ""                                      ""                                     ""            ""                          
"(community) asset mapping"            " solutions mapping"                    " other"                               ""            ""                          
"productivity and team collaboration " " other"                                ""                                     ""            ""                          
"productivity and team collaboration " " co-creation"                          " brainstorming"                       ""            ""                          
"prototyping"                          " (community) asset mapping"            " other"                               ""            ""                          
"solutions mapping"                    " collective intelligence"              " other"                               ""            ""                          
"sensemaking"                          " co-creation"                          " randomized controlled trials"        ""            ""                          
"(community) asset mapping"            " collective intelligence"              " data ethnography"                    " pilots"            " solutions mapping"        
"micro narratives"                     " solutions mapping"                    " other"                               ""            ""                          
"digital facilitation"                 " collective intelligence"              " participatory design"                ""            ""                          
"foresight"                            " co-creation"                          " future analysis"                     ""            ""                          
"participatory design"                 " foresight"                            " future analysis"                     ""            ""                          
"foresight"                            " co-creation"                          " collective intelligence"             ""            ""                          
"sensemaking"                          " productivity and team collaboration " " other"                               ""            ""                          
"sensemaking"                          " participatory design"                 " productivity and team collaboration" ""            ""                          
"co-creation"                          " other"                                ""                                     ""            ""                          
"co-creation"                          " other"                                " productivity and team collaboration" ""            ""                          
"system thinking"                      " co-creation"                          " design sprints"                      ""            ""                          
"design sprints"                       " participatory design"                 " productivity and team collaboration" ""            ""                          
"design sprints"                       " pilots"                               " minimal viable product (mvp)"        ""            ""                          
"system thinking"                      ""                                      ""                                     ""            ""                          
"co-creation"                          " collective intelligence"              " productivity and team collaboration" ""            ""                          
"co-creation"                          " collective intelligence"              " participatory design"                ""            ""                          
"co-creation"                          " collective intelligence"              " productivity and team collaboration" ""            ""                          
"system thinking"                      ""                                      ""                                     ""            ""                          
"design thinking"                      " other"                                " sensemaking"                         ""            ""                          
"system thinking"                      ""                                      ""                                     ""            ""                          
"collective intelligence"              " citizen science"                      ""                                     ""            ""                          
"anticipatory regulation"              " future analysis"                      " sensemaking"                         ""            ""                          
"collective intelligence"              " design thinking"                      " productivity and team collaboration" ""            ""                          
"data ethnography"                     " system thinking"                      ""                                     ""            ""                          
"co-creation"                          " design sprints"                       " prototyping"                         ""            ""                          
"visual thinking & mapping"            ""                                      ""                                     ""            ""                          
"collective intelligence"              " ethnography"                          " participatory design"                ""            ""                          
"visual thinking & mapping"            ""                                      ""                                     ""            ""                          
"collective intelligence"              " future analysis"                      " foresight"                           " horizon scanning"            ""                          
"citizen science"                      " solutions mapping"                    ""                                     ""            ""                          
"co-creation"                          " collective intelligence"              " citizen science"                     " pilots"            ""                          
"co-creation"                          " collective intelligence"              " pilots"                              " citizen science"            ""                          
end

copy up to and including the previous line ------------------

[Nick Cox]

If "similar words" means really "identical phrases" then I can offer suggestions.

First, note that you need to trim leading spaces and trailing spaces and reduce internal spaces. .

That done tabm from tab_chi on SSC and mrtab from the Stata Journal may help, but I think your best moves would follow reshape long.