Hello All,

I hope this email finds you well!

I have two databases one includes my raw data, and the other includes guidelines for recommended values in my field to score one variable found within my raw data (I will clarify this a little more).

In my raw data (file 1) i have whether or not an individual participated in a sports activity (29 types of activities) at each survey cycle (participated=1, did not participate=0). My data are in long format, 20 cycles, 7 days, 29 activities, and I also have the exact age of participants at each survey cycle.

The file (file 2) that includes recommended values, is also in long format, includes the 29 activities that i have, but also assigns a metabolic equivalent to each activity based on specific age categories, therefore includes a categorical age variable and a variable called METy for metabolic equivalent.

I want to ask Stata to look at each activity (from the 29 activities) reported as yes by my participants, match it with the exact activity from the guidelines of my field, and then in my raw data set to create a METy variable by assigning/taking the metabolic equivalent from the guidelines (i.e file 2) that is age specific (i.e by corroborating that the participant received the value that is appropriate for their age category).


Below is how my raw data looks like (file 1)
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input double id byte(sc day activity) double NDTQ15 float age
11 1 1  1 1 10
11 1 1  2 1 10
11 1 1  3 1 10
11 1 1  4 1 10
11 1 1  5 1 10
11 1 1  6 1 10
11 1 1  7 1 10
11 1 1  8 1 10
11 1 1  9 1 10
11 1 1 10 1 10
11 1 1 11 1 10
11 1 1 12 1 10
11 1 1 13 1 10
11 1 1 14 1 10
11 1 1 15 1 10
11 1 1 16 1 10
11 1 1 17 1 10
11 1 1 18 1 10
11 1 1 19 1 10
11 1 1 20 1 10
11 1 1 21 1 10
11 1 1 22 0 10
11 1 1 23 1 10
11 1 1 24 1 10
11 1 1 25 1 10
end

the guidelines file looks like this (file 2)
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long(activity agecat) double METy
4 4   6
4 1 6.2
4 2 6.3
4 3 6.5
5 4 6.1
5 1 6.3
5 2 6.4
5 3 6.6
7 4 5.8
7 1   6
7 2 6.1
7 3 6.3
9 4 5.7
9 1 5.9
9 2   6
9 3 6.1
8 4 6.3
8 1 6.5
8 2 6.6
8 3 6.7
1 4 6.4
1 1 6.6
1 2 6.7
1 3 6.9
10 4 5.8
end
label values agecat agecat
label def agecat 1 "10-12 YEARS", modify
label def agecat 2 "13-15 YEARS", modify
label def agecat 3 "16-18 YEARS", modify
label def agecat 4 "6-9 YEARS", modify
I want to take the information under METy in file 2 by age and activity, and assign it to the specific age and activity in file 1.

Does this make sense? how do i do that? Any recommendations on resources that i can read that will help me accomplish what i want?

Thank you for your time,
Patrick