How to reference a variable within a variabe( ex) ID.N_DCODE == "2209")

[HOJUN YU]

Hello

I am new here.

I tried to find everywhere I know, but I cannot find how to reference a variable within a variable

here is my situation


ID N_DCODE N_TD_VOL N_TD_WT FQ_RICE FQ_BARLEY FQ_BIBIM
A651183001 0368 37.5 0 1 7 1
A651183001 1228 25 0 1 7 1
A651183001 1228 25 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 1228 25 0 1 7 1
A651183001 1096 15 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 1096 15 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0563 390 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 1113 25 0 1 7 1
A651183001 1113 25 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0563 390 0 1 7 1
A651183001 1096 15 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 2305 0 15 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 1228 25 0 1 7 1
A651183001 1228 25 0 1 7 1
A651183001 1096 15 0 1 7 1
A651183001 2034 0 355 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0136 175 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 1048 15 0 1 7 1
A651183001 1113 25 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 2034 0 300 1 7 1
A651183001 2002 0 110 1 7 1
A651183001 0243 0 220.3 1 7 1
A651183001 1096 15 0 1 7 1
A651183001 1113 25 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 1505 0 76 1 7 1
A651183001 1609 0 3.1 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0563 390 0 1 7 1
A651183001 1113 25 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 1228 25 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0563 390 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 1113 25 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0368 37.5 0 1 7 1
A651183001 0563 390 0 1 7 1
A651183001 0368 37.5 0 1 7 1


this is part of the dataset.

and here is a FFQ(food frequency questionnaire) dataset merged with 24RC questionnaire dataset. There are two different surveys for the same kinds of food eaten. FFQ asks how often you eat foodA on a yearly basis, 24RC asks what exactly was eaten previous 24 hours.

I want to give a table for matching percentages of foodA to foodZ by FFQ & 24RC.

N_DCODE denotes which kind of food in 24RC survey. "2209" denotes strawberry

Is there an expression in stata to describe for example,

by ID, sort: gen straw_match = 1 if (N_DCODE == "2209).N_TD_WT !=0 & FQ_STRAW !=0
replace straw_match = 0 if (ID.N_DCODE == "2209).N_TD_WT= 0 | FQ_STRAW =0 

I will use the food match results to calculate match percentages and fill up the table.

thank you.

[Nick Cox]

list if ND_CODE == "0368"

is the kind of thing that will work if the variable is string, which seems possible but not certain. That is, you need " " at the beginning and end of each string.

[Jared Greathouse]

I think what you mean is the level of a categorical variable, right?

[Ken Chui]

If I'd have to revise the code, it may be:

gen straw_match = 1 if N_DCODE == "2209" & N_TD_WT !=0 & FQ_STRAW !=0
replace straw_match = 0 if N_DCODE == "2209" & N_TD_WT ==0 & FQ_STRAW ==0

But I'm not entirely sure because:

1. The described variables in text were not clearly linked with the variable name.
2. The code involves variables that were not even in the sample data set.
3. You used a special expression like ID.N_DCODE with a motive that was not clear to me.

As how it's looked, I don't think you need to sort the data and do that by ID. But I may have misunderstood. If you want to flag all the rows of the same ID as "1" if even one food item under 2209 matches, then yes, a "by" may be needed.

[HOJUN YU]

@Ken Chui

that is more like what I wanted to do

and FQ_STRAW is amount of strawberry eaten from FFQ dataset.

Nick Cox list if N_DCODE =="2209" what will this return ? I want to refer to N_TD_WT value from N_DCODE == "2209" from ID (iterative by ID)

thank you. Appreciate such early answers..

[Nick Cox]

Why not try it to find out?

[HOJUN YU]

Nick Cox OK. this is what it returns. Most are truncated d/t their sizes.

so from what I see, is there really a way to reference N_TD_WT within an ID?

I got the answer I wanted, but the question still remains...

Thank you for answers!

list if N_DCODE == "2019"

gen straw_match = 1 if N_TD_WT== 0 & FQ_STRAW ==0
replace straw_match = 1 if N_TD_WT !=0 & FQ_STRAW !=0 
replace straw_match = 0 if  N_TD_WT !=0 & FQ_STRAW ==0
replace straw_match = 0 if  N_TD_WT ==0 & FQ_STRAW !=0 

178066. | ID | N_DCODE | N_TD_VOL | N_TD_WT | FQ_RICE | FQ_BARLEY | FQ_BIBIM | FQ_GIMBAB | FQ_CURRY |
| R654288402 | 2209 | 0 | 84.4 | 14 | 7 | 1 | .23255814 | 0 |
|------------+--------------------------------------------------------------------------------------------|
| FQ_INSTNO | FQ_WHNO | FQ_CHNO | FQ_BUNO | FQ_S_RCAK | FQ_DUMP | FQ_BRE | FQ_BUTTER |
| 3 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_JAM | FQ_RB_BRE | FQ_CAKE | FQ_PIZZA | FQ_HAMBER | FQ_RCAK | FQ_F_RCAK | FQ_CEREAL |
| 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_T_BEEF | FQ_T_POTA | FQ_T_LOAC | FQ_J_POLL | FQ_S_SWED | FQ_S_BEEF | FQ_S_POLL | FQ_S_SOYP |
| .23255814 | 0 | 0 | 0 | .23255814 | .23255814 | 0 | 0 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_J_SOYP | FQ_J_KIMC | FQ_J_BUDA | FQ_J_BNCD | FQ_BNCURD | FQ_S_BEAN | FQ_F_EGG | FQ_S_EGG |
| 5.5 | 3 | .23255814 | .58139535 | .23255814 | .23255814 | 1 | 1 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_R_PORK | FQ_S_PORK | FQ_F_PORK | FQ_C_PORK | FQ_R_BEEF | FQ_F_BEEF | FQ_HAM | FQ_PORKBY |
| .58139535 | .23255814 | .23255814 | .23255814 | 1 | .23255814 | 0 | 0 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_T_CHIC | FQ_S_CHIC | FQ_F_CHIC | FQ_R_DUCK | FQ_MACKER | FQ_HAIRT | FQ_ANCH | FQ_SQUID |
| .23255814 | 0 | .23255814 | .23255814 | .23255814 | .23255814 | 3 | .23255814 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_SCRAB | FQ_SFISH | FQ_FPASTE | FQ_SPROU | FQ_SPINAC | FQ_BELLFI | FQ_PUMPKI | FQ_OVEG |
| 0 | .23255814 | .23255814 | 1 | .58139535 | .23255814 | 0 | 3 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_CUCUMB | FQ_RADI | FQ_VSALAD | FQ_GREENO | FQ_RAWVEG | FQ_BROCOL | FQ_GARLIC | FQ_FER_BN |
| .23255814 | 1 | 3 | 1 | 1 | .58139535 | 1 | 1 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_KIMCHI | FQ_OKIMCH | FQ_SVEG | FQ_ROOT | FQ_PCAKE | FQ_F_VEG | FQ_MUSHRO | FQ_LAV |
| 5.5 | 5.5 | .58139535 | .23255814 | 0 | .23255814 | 1 | 5.5 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_GLAV | FQ_F_SWED | FQ_F_POTA | FQ_S_POTA | FQ_SWPOTA | FQ_CORN | FQ_MILK | FQ_L_YOGU |
| 1 | .23255814 | .58139535 | .23255814 | .58139535 | .23255814 | .58139535 | .23255814 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_C_YOGU | FQ_BN_M~K | FQ_STRAW | FQ_TOMATO | FQ_MMELON | FQ_WMELON | FQ_PEACH | FQ_GRAPE |
| .23255814 | .23255814 | 0 | 1.0975 | 0 | 0 | 1.2133333 | 1.2891667 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_APPLE | FQ_PEAR | FQ_PERS | FQ_TANG | FQ_BANANA | FQ_CITRUS | FQ_KIWI | FQ_COFFEE |
| 3 | .4 | .495 | 1 | 1 | 1 | .58139535 | 21 |
|------------+------------+------------+------------+------------+------------+-------------+-------------|
| FQ_CREAM | FQ_SUGAR | FQ_TEA | FQ_SODA | FQ_FJUICE | FQ_GBAVER | FQ_SNACK | FQ_COOKIE |
| 21 | 21 | 7 | .23255814 | 1 | 1 | 0 | 0 |
|---------------------------------------------------------------------------------------------------------|
| FQ_CHOCO | FQ_ICECM | FQ_PEANUT | FQ_CHNUT | FQ_SOJU | FQ_BEER | FQ_RWINE | AR_JAM | AR_CAKE |
| .23255814 | .58139535 | .58139535 | .23255814 | 5.5 | 5.5 | .23255814 | 0 | 0 |
|---------------------------------------------------------------------------------------------------------|
| AR_RB_~E | AR_SODA | AR_FJU~E | AR_SNACK | AR_COO~E | AR_CHOCO | AR_ICECM | AR_MME~N | AR_WME~N |
| 0 | 1 | 1 | 0 | 0 | .25 | 1 | .33 | 3 |
|---------------------------------------------------------------------------------------------------------|
| AR_PERS | AR_KIWI | AR_STRAW | AR_TOM~O | AR_PEACH | AR_GRAPE | AR_APPLE | AR_PEAR | AR_TANG | AR_BAN~A |
| 2 | 1 | 5 | 2 | 1 | 2 | 1 | .5 | 3 | 1 |
|---------------------------------------------------------------------------------------------------------|
| AR_CIT~S |
| 1 |

[Jared Greathouse]

Watch this. HOJUN YU

[Nick Cox]

The syntax in #1 does not look close to anything I know about in Stata. Ken Chui gave a nice answer showing how conditions can be combined in an if qualifier.

The difficulty for us is that you're asking is there a syntax like this -- to which the answer is No -- but I can't work out what it is that you want the syntax to do.