Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • data management, creating new variables in one data set based on information in another data base

    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





  • #2
    In your raw data you need to make a new variable agecat that matches the agecat variable in file 2. After that it is just a merge: merge m:1 activity agecat using file2
    ---------------------------------
    Maarten L. Buis
    University of Konstanz
    Department of history and sociology
    box 40
    78457 Konstanz
    Germany
    http://www.maartenbuis.nl
    ---------------------------------

    Comment


    • #3
      Thank you for your quick response Maarten, it was a simple solution for what I thought was a complex situation.
      Best wishes,
      Patrick

      Comment

      Working...
      X