Transform text response variable to another variable

[Ana Altares]

I have a group of food source variables: 'foodgiven_free', 'foodpantry', 'grocery', 'conveniencestore', etc. and a text entry response variable 'otherCFAR_text'. There are some text response items that don't fall into the "other CFAR" category, but rather fall into the other food source variables like 'foodpantry'. I'm wondering how to add the responses from the 'otherCFAR_text' variable to the other food source variables. I've tried using the egen function, but can't seem to figure out the correct syntax.

.egen float foodgiven_free_new = rowtotal(foodgiven_free) otherCFAR_text == 2 | otherCFAR_text == 23

Other potentially helpful information: The food source variables have 7-pt likert scale response options.
First time posting, so please let me know if I'm missing other helpful information

[Ken Chui]

Welcome to Statalist.

.egen float foodgiven_free_new = rowtotal(foodgiven_free) otherCFAR_text == 2 | otherCFAR_text == 23

Other potentially helpful information: The food source variables have 7-pt likert scale response options.

What this code tries to do does not make sense given the data type. If someone answered, say, "Always" which is top 7 point in the Likert scale, if you they also say "otehrCFAR_text" == 2, which let's assume it's also "7", you'd sum them up to 14 which is no longer within the range of the 1-7 Likert's scale. The recoding scheme needs to be revised.

I've tried using the egen function, but can't seem to figure out the correct syntax.

To let us help with this:

• Do not just say you found an error, show us the error message.
• Better, use dataex command to show some data sample, replace the data content for privacy if you must. Having this sample data can let us immediate draft the codes, which you can use immediately.

All these general tips on how to ask a good question are in the FAQ above (http://www.statalist.org/forums/help). Please read through section 12 in details and revise your question.

[Ana Altares]

Hi Ken,
See code below. Hopefully, this is more helpful. I think my point of confusion is how to transform a free-form text response option that shows up as its own variable into other variables that it should be categorized as. As I mentioned, some of the text response options should be under a different variable. The italics at the bottom show how I need to convert some of these values to other variables. I have the original string variables here as well in case I need to start from scratch and not convert to numerical first.

* Example generated by -dataex-. For more info, type help dataex
clear
input str14(Foodpantry Foodgiven OtherCFAR) str65 TextResponseCFAR float foodpantry long(foodgiven_free otherCFAR otherCFAR_text)
"Almost all"     "Less than half" "All"            ""                   6 4 2  .
"More than half" "Only a little"  ""               ""                   5 7 .  .
"None at all"    "Only a little"  ""               ""                   1 7 .  .
""               ""               ""               ""                   . . .  .
"Less than half" "None at all"    "None at all"    ""                   3 6 6  .
"Almost all"     "Only a little"  "None at all"    ""                   6 7 6  .
"More than half" "More than half" "None at all"    ""                   5 5 6  .
"About half"     "Only a little"  "None at all"    ""                   4 7 6  .
"None at all"    "None at all"    "About half"     "community market"   1 6 1 33
"Only a little"  "Only a little"  "None at all"    ""                   2 7 6  .
"About half"     "About half"     "About half"     ""                   4 1 1  .
"Less than half" "None at all"    "Only a little"  ""                   3 6 7  .
"Only a little"  "About half"     "None at all"    ""                   2 1 6  .
"None at all"    "Almost all"     "Almost all"     "seNeitheror center" 1 3 3 37
"None at all"    "Almost all"     "None at all"    ""                   1 3 6  .
"About half"     "None at all"    "None at all"    ""                   4 6 6  .
"Only a little"  "None at all"    "Only a little"  ""                   2 6 7  .
"More than half" "Only a little"  "None at all"    ""                   5 7 6  .
"Only a little"  "Only a little"  "Only a little"  ""                   2 7 7  .
""               "Only a little"  "Only a little"  ""                   . 7 7  .
"None at all"    "None at all"    ""               ""                   1 6 .  .
"None at all"    "Less than half" ""               ""                   1 4 .  .
"Only a little"  "Only a little"  "None at all"    ""                   2 7 6  .
"Only a little"  "None at all"    "Only a little"  ""                   2 6 7  .
"Almost all"     "None at all"    "None at all"    ""                   6 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"About half"     "None at all"    "None at all"    ""                   4 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"Only a little"  "None at all"    "None at all"    ""                   2 6 6  .
"None at all"    "Only a little"  ""               ""                   1 7 .  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"Only a little"  "Only a little"  ""               ""                   2 7 .  .
"Only a little"  "Only a little"  "Only a little"  ""                   2 7 7  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"Less than half" "Only a little"  "None at all"    ""                   3 7 6  .
""               "None at all"    "Only a little"  ""                   . 6 7  .
"None at all"    "Less than half" "None at all"    ""                   1 4 6  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"Less than half" "Only a little"  "None at all"    ""                   3 7 6  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"Less than half" "Only a little"  "Only a little"  ""                   3 7 7  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"Less than half" "None at all"    "None at all"    ""                   3 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "Only a little"  ""                   1 6 7  .
"None at all"    "Less than half" "None at all"    ""                   1 4 6  .
"About half"     "None at all"    "None at all"    ""                   4 6 6  .
"None at all"    "None at all"    "More than half" "Wic"                1 6 5 29
"Only a little"  "Only a little"  "None at all"    ""                   2 7 6  .
"None at all"    "None at all"    "Only a little"  ""                   1 6 7  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"Only a little"  "None at all"    "None at all"    ""                   2 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "Less than half" "None at all"    ""                   1 4 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"Only a little"  "Only a little"  "None at all"    ""                   2 7 6  .
"Only a little"  "Only a little"  ""               ""                   2 7 .  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"Only a little"  "None at all"    "None at all"    ""                   2 6 6  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"Less than half" "Only a little"  "None at all"    ""                   3 7 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"About half"     "Less than half" "Only a little"  ""                   4 4 7  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"Only a little"  "About half"     "None at all"    ""                   2 1 6  .
"All"            "Less than half" "All"            ""                   7 4 2  .
"Less than half" "None at all"    "Less than half" ""                   3 6 4  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
""               ""               ""               ""                   . . .  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "Only a little"  "None at all"    ""                   1 7 6  .
"Only a little"  "None at all"    "None at all"    ""                   2 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"None at all"    "None at all"    "None at all"    ""                   1 6 6  .
"Less than half" "None at all"    ""               ""                   3 6 .  .
"More than half" "Only a little"  "Only a little"  ""                   5 7 7  .
"Less than half" "Less than half" ""               ""                   3 4 .  .
"About half"     "Only a little"  ""               ""                   4 7 .  .
"About half"     "About half"     ""               ""                   4 1 .  .
"Less than half" "Only a little"  ""               ""                   3 7 .  .
"Less than half" "Less than half" ""               ""                   3 4 .  .
"About half"     "Only a little"  ""               ""                   4 7 .  .
"Less than half" "Only a little"  ""               ""                   3 7 .  .
"Only a little"  "Only a little"  ""               ""                   2 7 .  .
"Less than half" "Only a little"  ""               ""                   3 7 .  .
"Less than half" "Only a little"  ""               ""                   3 7 .  .
"About half"     "Only a little"  ""               ""                   4 7 .  .
"About half"     "Only a little"  ""               ""                   4 7 .  .
"About half"     "Only a little"  ""               ""                   4 7 .  .
"Less than half" "Less than half" ""               ""                   3 4 .  .
"Less than half" "Less than half" ""               ""                   3 4 .  .
end
label values foodgiven_free foodgiven_free
label def foodgiven_free 1 "About half", modify
label def foodgiven_free 3 "Almost all", modify
label def foodgiven_free 4 "Less than half", modify
label def foodgiven_free 5 "More than half", modify
label def foodgiven_free 6 "None at all", modify
label def foodgiven_free 7 "Only a little", modify
label values otherCFAR otherCFAR
label def otherCFAR 1 "About half", modify
label def otherCFAR 2 "All", modify
label def otherCFAR 3 "Almost all", modify
label def otherCFAR 4 "Less than half", modify
label def otherCFAR 5 "More than half", modify
label def otherCFAR 6 "None at all", modify
label def otherCFAR 7 "Only a little", modify
label values otherCFAR_text otherCFAR_text
label def otherCFAR_text 29 "Wic", modify
label def otherCFAR_text 33 "community market", modify
label def otherCFAR_text 37 "seNeitheror center", modify

**Text responses for otherCFAR_text recodes
*1 community charity -> otherCFAR
*2 0 -> otherCFAR
*3 A friend of mine shares with us when -> foodgiven_free
*4 Charity groups -> otherCFAR
*5 Church helps -> church
*6 Church help orgaNeithersations -> church
*7 Church orgaNeitherzation -> church
*8 Edwards -> otherCFAR
*9 Farmer's market cooking demos w/coupon -> otherCFAR
*10 Grupos de iglesias -> church
*11 Help orgaNeithersations -> otherCFAR
*12 Help orgaNeitherzations -> otherCFAR
*13 Iglesia de Acción de Gracias -> church
*14 Joint group savings -> otherCFAR
*15 Less than half -> otherCFAR
*16 Lift Up -> foodpantry
*17 Lls bancos de cómoda nos ayudan micho -> foodpantry
*18 Mis Neitherños comen en la escuela -> schools
*19 Mp -> otherCFAR
*20 NA -> otherCFAR = none at all (1)
*21 No -> otherCFAR = none at all (1)
*22 None -> otherCFAR = none at all (1)
*23 None at all -> otherCFAR = none at all (1)
*24 ReuNeitheron assistance -> otherCFAR
*25 Salvation army -> soupkitchen
*26 School meals -> schools
*27 Thanksgivings -> otherCFAR
*28 Wic -> otherCFAR
*29 Yes -> otherCFAR
*30 ayuda comuNeithertaria -> otherCFAR
*31 community Gathering -> otherCFAR
*32 community market -> foodpantry
*33 community market -> foodpantry
*34 dinner at Some neighbors house -> foodgiven_free
*35 none -> otherCFAR
*36 seNeither center -> otherCFAR

[Dirk Enzmann]

It seems that you did generate the variables "foodgiven_free" and "otherCFAR" by using encode with "Foodgiven" and "OtherCFAR". But note that if you use encode the resulting values most often are not coded as (ordinal or quasi-continuous) Likert scale items (such as your variable "foodpantry" which, by the way, has no value labels): Instead, the values from 1 to 7 (if there are 7 different categories) are simply numbering the alphabetically sorted value labels.

For example, the values and value labels of your your variable "foodgiven_free" are as follows (I am using fre from SSC here, the first two lines of code will install fre if necessary):

cap which fre           // check if -fre- is installed already
if _rc ssc install fre  // install -fre- if it is not installed

fre foodgiven_free

The resulting frequency table is:

. fre foodgiven_free

foodgiven_free
----------------------------------------------------------------------
                         |      Freq.    Percent      Valid       Cum.
-------------------------+--------------------------------------------
Valid   1 About half     |          4       4.00       4.08       4.08
        3 Almost all     |          2       2.00       2.04       6.12
        4 Less than half |         11      11.00      11.22      17.35
        5 More than half |          1       1.00       1.02      18.37
        6 None at all    |         41      41.00      41.84      60.20
        7 Only a little  |         39      39.00      39.80     100.00
        Total            |         98      98.00     100.00          
Missing .                |          2       2.00                      
Total                    |        100     100.00                      
----------------------------------------------------------------------

Note that the value 2 does not exist (not only in the data, but the label for value 2 doesn't exist either) and that, for example, the value 1 (= about half) is smaller than 3 (= almost all) but that the value 3 indicates something that is larger than "less than half" (= 4) although its value is smaller. This contradicts the idea of a Likert scale whereby larger values consistently indicate more (or consistently less) of "something".

To repair these issues I would suggest to label the values of "foodpantry", recode and relabel the values of "foodgiven_free" and "otherCFAR", and clean up by dropping the superfluous variables "Foodpantry", "Foodgiven", and "OtherCFAR". Note that I will use program elabel from SSC:

cap which elabel
if _rc ssc install elabel

lab def foodpantry 1 "none at all" ///
                   2 "only a little" ///
                   3 "less than half" ///
                   4 "about half" ///
                   5 "more than half" ///
                   6 "almost all" ///
                   7 "all"
lab val foodpantry foodpantry

recode foodgiven_free (6=1) (7=2) (4=3) (1=4) (5=5) (3=6) (2=7)
elabel recode foodgiven_free (6=1) (7=2) (4=3) (1=4) (5=5) (3=6) (2=7)
lab def foodgiven_free 7 "all", modify

recode otherCFAR (6=1) (7=2) (4=3) (1=4) (5=5) (3=6) (2=7)
elabel recode otherCFAR (6=1) (7=2) (4=3) (1=4) (5=5) (3=6) (2=7)

drop Foodpantry Foodgiven OtherCFAR

From here on I don't know how to go on because I don't understand what you mean by "1 community charity -> otherCFAR" or "2 Church helps -> church".

It seems as if you want to modify the variable "otherCFA" if some other variable ("TextResponseCFAR"?) has the string value "community charity", etc. But because "otherCFA" is a Likert scale item: Which value do you want to use? While it might make sense to code otherCFAR to the value of "none at all" (which is only 1 if you recode its values as shown above) if the string in "TextResponseCFAR" is "no", I wonder how you want to encode something like "Joint group savings" without invalidating the meaning of the values of your Likert scale items.

[Ana Altares]

Thanks Dirk. I clean up variables and assigned values.

The main question I am asking is this: Participants input responses for the category 'otherCFAR' which has a text response option 'otherCFAR_text'. This would be fine to leave alone, except that some people typed responses that actually fall under the other categories such as 'foodpantry' or 'freefood_given'. I encoded the 'otherCFAR_text' variable so that all the typed responses are now numbered. I need to figure out how to recode these responses such as *A friend of mine shares with us when (a value of 3) to the variable 'foodgiven_free' since it should categorized as 'freefood_given' and not 'otherCFAR'.

All of the italicized items show what the 'otherCFAR' text responses need to be recoded as. Some should stay as 'otherCFAR', but others fit into a different category (or variable). Thank you.

[Ana Altares]

I'm basically trying to say I want a variable 'freefood_given_new' to include the variable 'freefood_given' AND 'otherCFAR' IF 'otherCFAR_text' is equal to values of 3 and 34

[Andrew Musau]

It seems to me that your variables "Foodpantry", "Foodgiven", and "OtherCFAR" express some intensity, from "none at all" to "almost all". On the other hand, "OtherCFAR_text" can mention one of the intensity variables. The difficulty in answering your question lies in understanding how a mention of a variable should be translated into intensity. For example, if we have:

*3 A friend of mine shares with us when -> foodgiven_free

in the variable "OtherCFAR_text", what intensity value should we assign to "freefood_given" when merging the response to this variable? Or what should the output of "freefood_given_new" be in this case, say if "freefood_given" == "about half"?

[Ana Altares]

Yes! That is the issue I'm trying to figure out... the following output shows the likert scale value that needs to be assigned to each text option.. but this value of 3 actually should be under the variable 'foodgiven_free'
. tab otherCFAR if otherCFAR_text == 3

otherCFAR | Freq. Percent Cum.
---------------+-----------------------------------
Only a little | 1 100.00 100.00
---------------+-----------------------------------
Total | 1 100.00


Similarly, this value'16' should be under the variable 'foodpantry'
. tab otherCFAR if otherCFAR_text == 16

otherCFAR | Freq. Percent Cum.
---------------+-----------------------------------
Less than half | 1 100.00 100.00
---------------+-----------------------------------
Total | 1 100.00

[Dirk Enzmann]

You can modify values of a variable easily using replace ... if . Personally I would not have tried to transform the string values of "TextResponseCFAR" into a new variable with numeric values but would have used the original string values directly because then your commands document your decisions immediately in an intelligible way for human readers.

Hence, if (as an example) "TextResponseCFAR" had more variants of "a little" I would use commands like this:

replace foodgiven_free = 2 if inlist(lower(TextResponseCFAR),"a little", ///
                                                             "only a little")
replace TextResponseCFAR = "" if inlist(lower(TextResponseCFAR),"a little", ///
                                                                "only a little")

Hence you need not use replace for each value of "TextResponseCFAR" but can specify more than one condition via inlist. But note that in case of string variables inlist allows only a maximum of 10 elements or better 9 string values -- if this is not sufficient you have to use more than one command with inlist. And note that I not only modified "foodgiven_free" but also set "TextResponseCFAR" to missing because the values of these cases have already been used for correcting another variable.

If you want to make sure not to overwrite already existing (valid) values of "foodgiven_free", you could use (for example):

replace foodgiven_free = 2 if foodgiven_free < . & inlist(lower(TextResponseCFAR),"a little", ///
                                                                                  "only a little")
replace TextResponseCFAR = "" if foodgiven_free == 2 & inlist(lower(TextResponseCFAR),"a little", ///
                                                                                      "only a little") // note that because of "== 2" more modifications of "TextResponseCFAR" are possible

Of course, if nevertheless you want to go the more error prone way to use the values of otherCFAR_text, you can do so:

replace foodgiven_free = 2 if foodgiven_free < . & otherCFAR_text==3
replace otherCFAR_text = . if foodgiven_free == 2

replace foodgiven_free = 3 if foodgiven_free < . & otherCFAR_text==16
replace otherCFAR_text = . if foodgiven_free == 3

(always assuming that "foodgiven_free" is coded with 2 = "only a little" and 3 = "less than half").

[Ana Altares]

Thanks! This is very helpful. Can you clarify what the 'foodgiven_free <. ' is indicating?

[Dirk Enzmann]

"not missing" or "smaller than missing", see help missing.