Problem with sorting variables from one dataset to another (different years)

[Natalie Lim]

Dear statalist users,

Below are the codes from 2 data sets in two different years, I manually categorized 'NRC,NRI,RC and RM' according the 'scaleid' importance value for each 'elementname'. How do I categorize the same elements to their respective 'NRC,NRI,RC and RM' from the second dataset according to the first dataset without me copy and paste the 'scaleid' manually?

Please advise. Thanks.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 onetsoccode str139(elementid elementname) str8 scaleid double datavalue str11 occ_code double(NRC NRI RC RM)
"11-9011.03" "4.A.2.a.4"   "Analyzing Data or Information"                                                "IM"               3.33 "11" 3.33                  .                  .                  .
"11-2021.00" "4.A.3.a.2"   "Handling and Moving Objects"                                                  "IM"                1.5 "11"    .                  .                  .                1.5
""           "4.C.1.b.1.g" "Coordinate or Lead Others"                                                    "CX"                  4 "11"    .                  4                  .                  .
"11-3011.00" "4.A.4.a.6"   "Selling or Influencing Others"                                                "IM"               2.16 "11"    .               2.16                  .                  .
"11-9011.01" "4.A.4.b.3"   "Training and Teaching Others"                                                 "IM"               2.66 "11"    .               2.66                  .                  .
"11-9031.00" "4.A.2.b.5"   "Scheduling Work and Activities"                                               "IM"               3.75 "11"    .                  .                4.4                  .
""           "4.C.2.d.1.c" "Spend Time Climbing Ladders, Scaffolds, or Poles   "                          "CX" 1.2000000476837158 "11"    .                  .                  . 1.2000000476837158
"11-9041.00" "4.A.4.a.3"   "Communicating with Persons Outside Organization"                              "IM"               4.16 "11"    .               4.16                  .                  .
"11-3071.01" "4.A.4.b.5"   "Coaching and Developing Others"                                               "IM"                1.8 "11"    .                1.8                  .                  .
"11-9121.00" "4.A.2.a.4"   "Analyzing Data or Information"                                                "IM"                4.5 "11"  4.5                  .                  .                  .
"11-9111.00" "4.A.3.b.2"   "Drafting, Laying Out, and Specifying Technical Devices, Parts, and Equipment" "IM"                1.4 "11"    .                  .                1.4                  .
""           "4.C.3.b.7"   "Importance of Repeating Same Tasks"                                           "CX" 1.3333330154418945 "11"    .                  . 1.3333330154418945                  .
"11-9061.00" "4.A.2.a.4"   "Analyzing Data or Information"                                                "IM"               2.33 "11" 2.33                  .                  .                  .
"11-3031.02" "4.A.2.b.2"   "Thinking Creatively"                                                          "IM"                3.5 "11"  3.5                  .                  .                  .
""           "4.C.1.b.1.g" "Coordinate or Lead Others"                                                    "CX"  3.799999952316284 "11"    .  3.799999952316284                  .                  .
""           "4.C.1.b.1.g" "Coordinate or Lead Others"                                                    "CX"  4.199999809265137 "11"    .  4.199999809265137                  .                  .
""           "4.C.1.b.1.g" "Coordinate or Lead Others"                                                    "CX" 3.4000000953674316 "11"    . 3.4000000953674316                  .                  .
""           "4.C.1.b.1.g" "Coordinate or Lead Others"                                                    "CX"  4.400000095367432 "11"    .  4.400000095367432                  .                  .
""           "4.C.1.b.1.g" "Coordinate or Lead Others"                                                    "CX"  4.400000095367432 "11"    .  4.400000095367432                  .                  .
""           "4.C.1.b.1.g" "Coordinate or Lead Others"                                                    "CX"  4.199999809265137 "11"    .  4.199999809265137                  .                  .
""           "4.C.1.b.1.g" "Coordinate or Lead Others"                                                    "CX" 3.5999999046325684 "11"    . 3.5999999046325684                  .                  .
"11-9061.00" "4.A.2.b.2"   "Thinking Creatively"                                                          "IM"                  2 "11"    2                  .                  .                  .
"11-9031.00" "4.A.2.b.1"   "Making Decisions and Solving Problems"                                        "IM"               4.16 "11" 4.16                  .                  .                  .
""           "4.C.2.d.1.c" "Spend Time Climbing Ladders, Scaffolds, or Poles   "                          "CX" 1.2000000476837158 "11"    .                  .                  . 1.2000000476837158
"11-9012.00" "4.A.4.c.1"   "Performing Administrative Activities"                                         "IM"                3.5 "11"    .                  .                  .                3.5
"11-3021.00" "4.A.4.a.2"   "Communicating with Supervisors, Peers, or Subordinates"                       "IM"               4.33 "11"    .               4.33                  .                  .
""           "4.C.3.b.4"   "Importance of Being Exact or Accurate"                                        "CX" 3.4000000953674316 "11"    .                  . 3.4000000953674316                  .
""           "4.C.2.d.1.d" "Spend Time Walking and Running "                                              "CX" 2.5999999046325684 "11"    .                  .                  . 2.5999999046325684
"11-3061.00" "4.A.4.b.5"   "Coaching and Developing Others"                                               "IM"                  2 "11"    .                  2                  .                  .
"11-9151.00" "4.A.4.b.4"   "Guiding, Directing, and Motivating Subordinates"                              "IM"                4.5 "11"    .                4.5                  .                  .
"11-1011.02" "4.A.2.a.1"   "Judging the Qualities of Things, Services, or People"                         "IM"                3.5 "11"  3.5                  .                  .                  .
"11-9031.00" "4.A.4.a.2"   "Communicating with Supervisors, Peers, or Subordinates"                       "IM"               4.66 "11"    .               4.66                  .                  .
"11-9081.00" "4.A.4.b.3"   "Training and Teaching Others"                                                 "IM"               2.66 "11"    .               2.66                  .                  .
"11-9151.00" "4.A.4.c.1"   "Performing Administrative Activities"                                         "IM"               4.16 "11"    .                  .                  .               4.16
"11-3071.01" "4.A.4.b.2"   "Developing and Building Teams"                                                "IM"                2.2 "11"  2.2                  .                  .                  .
"11-3071.02" "4.A.2.b.6"   "Organizing, Planning, and Prioritizing Work"                                  "IM"                3.6 "11"  3.6                  .                  .                  .
"11-9033.00" "4.A.4.c.3"   "Monitoring and Controlling Resources"                                         "IM"                4.5 "11"    .                  .                4.5                  .
""           "4.C.3.d.3"   "Pace Determined by Speed of Equipment"                                        "CX" 1.2000000476837158 "11"    .                  .                  . 1.2000000476837158
"11-9031.00" "4.A.3.a.4"   "Operating Vehicles, Mechanized Devices, or Equipment"                         "IM"                1.5 "11"    .                  .                  .                1.5
"11-9011.01" "4.A.3.a.3"   "Controlling Machines and Processes"                                           "IM"               2.16 "11"    .                  .                  .               2.16
"11-2022.00" "4.A.4.b.5"   "Coaching and Developing Others"                                               "IM"                  4 "11"    .                  4                  .                  .
""           "4.C.2.d.1.d" "Spend Time Walking and Running "                                              "CX" 2.6666669845581055 "11"    .                  .                  . 2.6666669845581055
"11-9031.00" "4.A.4.a.1"   "Interpreting the Meaning of Information for Others"                           "IM"               3.83 "11" 3.83                  .                  .                  .
"11-1011.01" "4.A.4.b.3"   "Training and Teaching Others"                                                 "IM"               3.33 "11"    .               3.33                  .                  .
"11-3021.00" "4.A.4.b.6"   "Provide Consultation and Advice to Others"                                    "IM"                  4 "11"    .                  4                  .                  .
"11-9012.00" "4.A.4.a.7"   "Resolving Conflicts and Negotiating with Others"                              "IM"                2.5 "11"    .                2.5                  .                  .
""           "4.C.2.d.1.c" "Spend Time Climbing Ladders, Scaffolds, or Poles   "                          "CX"                  1 "11"    .                  .                  .                  1
"11-3011.00" "4.A.2.b.1"   "Making Decisions and Solving Problems"                                        "IM"                  4 "11"    4                  .                  .                  .
"11-9051.00" "4.A.2.b.5"   "Scheduling Work and Activities"                                               "IM"               4.33 "11"    .                  .               1.16                  .
"11-3021.00" "4.A.2.a.4"   "Analyzing Data or Information"                                                "IM"               4.16 "11" 4.16                  .                  .                  .
end

* Example generated by -dataex-. To install: ssc install dataex
clear
input str139(elementid elementname) str8 scaleid float datavalue str11 occ_code double(NRC NRI RC RM)
"4.A.4.a.1"   "Interpreting the Meaning of Information for Others"                              "IM"      2.5 "11" .                  .                  .                  .
"4.C.3.b.2"   "Degree of Automation"                                                            "CX"    1.932 "11" .                  .                  . 1.9320000410079956
"4.C.1.b.1.g" "Coordinate or Lead Others"                                                       "CX"      3.4 "11" . 3.4000000953674316                  .                  .
"4.A.3.a.1"   "Performing General Physical Activities"                                          "IM"     1.66 "11" .                  .                  .                  .
"4.C.2.d.1.d" "Spend Time Walking and Running "                                                 "CX"        3 "11" .                  .                  .                  3
"4.A.4.a.6"   "Selling or Influencing Others"                                                   "IM"     3.16 "11" .                  .                  .                  .
"4.C.2.d.1.b" "Spend Time Standing"                                                             "CX"        3 "11" .                  .                  .                  3
"4.A.2.b.3"   "Updating and Using Relevant Knowledge"                                           "IM"     3.66 "11" .                  .                  .                  .
"4.A.3.b.6"   "Documenting/Recording Information"                                               "IM"        4 "11" .                  .                  .                  .
"4.A.2.a.4"   "Analyzing Data or Information"                                                   "IM"     4.16 "11" .                  .                  .                  .
"4.C.1.b.1.g" "Coordinate or Lead Others"                                                       "CX"      3.2 "11" .  3.200000047683716                  .                  .
"4.A.4.c.3"   "Monitoring and Controlling Resources"                                            "IM"      2.5 "11" .                  .                  .                  .
"4.A.4.b.2"   "Developing and Building Teams"                                                   "IM"     4.33 "11" .                  .                  .                  .
"4.A.2.a.4"   "Analyzing Data or Information"                                                   "IM"     4.16 "11" .                  .                  .                  .
"4.C.2.d.1.c" "Spend Time Climbing Ladders, Scaffolds, or Poles   "                             "CX" 1.166667 "11" .                  .                  . 1.1666669845581055
"4.A.3.a.3"   "Controlling Machines and Processes"                                              "IM"      2.1 "11" .                  .                  .                  .
"4.C.1.b.1.g" "Coordinate or Lead Others"                                                       "CX"      3.5 "11" .                3.5                  .                  .
"4.A.4.b.2"   "Developing and Building Teams"                                                   "IM"     3.45 "11" .                  .                  .                  .
"4.C.3.d.3"   "Pace Determined by Speed of Equipment"                                           "CX"     1.75 "11" .                  .                  .               1.75
"4.A.4.a.3"   "Communicating with Persons Outside Organization"                                 "IM"     4.33 "11" .                  .                  .                  .
"4.A.4.b.5"   "Coaching and Developing Others"                                                  "IM"     4.83 "11" .                  .                  .                  .
"4.A.4.b.1"   "Coordinating the Work and Activities of Others"                                  "IM"      4.5 "11" .                  .                  .                  .
"4.A.4.c.3"   "Monitoring and Controlling Resources"                                            "IM"      3.5 "11" .                  .                  .                  .
"4.A.2.b.1"   "Making Decisions and Solving Problems"                                           "IM"     4.66 "11" .                  .                  .                  .
"4.A.2.a.3"   "Evaluating Information to Determine Compliance with Standards"                   "IM"     2.83 "11" .                  .                  .                  .
"4.C.3.b.4"   "Importance of Being Exact or Accurate"                                           "CX"      4.2 "11" .                  .  4.199999809265137                  .
"4.A.4.c.1"   "Performing Administrative Activities"                                            "IM"      3.4 "11" .                  .                  .                  .
"4.A.4.c.3"   "Monitoring and Controlling Resources"                                            "IM"      3.5 "11" .                  .                  .                  .
"4.A.4.a.1"   "Interpreting the Meaning of Information for Others"                              "IM"     3.66 "11" .                  .                  .                  .
"4.A.4.c.3"   "Monitoring and Controlling Resources"                                            "IM"      4.4 "11" .                  .                  .                  .
"4.A.2.b.6"   "Organizing, Planning, and Prioritizing Work"                                     "IM"      3.4 "11" .                  .                  .                  .
"4.C.2.d.1.d" "Spend Time Walking and Running "                                                 "CX"      2.5 "11" .                  .                  .                2.5
"4.A.3.a.4"   "Operating Vehicles, Mechanized Devices, or Equipment"                            "IM"        1 "11" .                  .                  .                  .
"4.A.4.a.1"   "Interpreting the Meaning of Information for Others"                              "IM"        2 "11" .                  .                  .                  .
"4.A.2.b.5"   "Scheduling Work and Activities"                                                  "IM"      3.5 "11" .                  .                  .                  .
"4.C.3.b.7"   "Importance of Repeating Same Tasks"                                              "CX"      1.7 "11" .                  . 1.7000000476837158                  .
"4.A.4.b.6"   "Provide Consultation and Advice to Others"                                       "IM"     3.16 "11" .                  .                  .                  .
"4.A.2.b.6"   "Organizing, Planning, and Prioritizing Work"                                     "IM"     4.33 "11" .                  .                  .                  .
"4.A.4.c.3"   "Monitoring and Controlling Resources"                                            "IM"     4.16 "11" .                  .                  .                  .
"4.C.1.b.1.g" "Coordinate or Lead Others"                                                       "CX" 2.833333 "11" . 2.8333330154418945                  .                  .
"4.A.2.a.3"   "Evaluating Information to Determine Compliance with Standards"                   "IM"        4 "11" .                  .                  .                  .
"4.C.3.b.7"   "Importance of Repeating Same Tasks"                                              "CX"      1.1 "11" .                  .  1.100000023841858                  .
"4.A.4.c.1"   "Performing Administrative Activities"                                            "IM"      3.2 "11" .                  .                  .                  .
"4.A.1.b.3"   "Estimating the Quantifiable Characteristics of Products, Events, or Information" "IM"     3.64 "11" .                  .                  .                  .
"4.A.4.b.3"   "Training and Teaching Others"                                                    "IM"     2.79 "11" .                  .                  .                  .
"4.A.4.a.2"   "Communicating with Supervisors, Peers, or Subordinates"                          "IM"      4.2 "11" .                  .                  .                  .
"4.A.4.a.2"   "Communicating with Supervisors, Peers, or Subordinates"                          "IM"     4.66 "11" .                  .                  .                  .
"4.A.4.c.1"   "Performing Administrative Activities"                                            "IM"     2.33 "11" .                  .                  .                  .
"4.A.4.b.1"   "Coordinating the Work and Activities of Others"                                  "IM"      3.6 "11" .                  .                  .                  .
"4.A.4.b.5"   "Coaching and Developing Others"                                                  "IM"      1.8 "11" .                  .                  .                  .
end

[Clyde Schechter]

I find your explanation confusing and I don't know what you've done, and have only a vague idea where you want to go from here. From exploring the data examples (thank you for using -dataex-!) it appears the NRC NRI RC and RM depend on elementid, elementname, scaleid, datavalue, and occ_code in the sense that if two observations fail to agree on all of the latter variables, then they will have different values for NRC NRI RC and RM. (The first data set contains an additional variable onetsoccode, that doesn't seem to have anything to do with anything else here.)

So, I'm guessing that you want to update the second data file by filling in the values of NRC NRI RC and RM in that data set with the values shown in those observations of the first data set that match elementid, elementname, scaleid, datavalue, and occ_code. That can be done with the -merge- command. But in the examples you show, there is almost no overlap between the values of elementid, elementname, scaleid, datavalue and occ_code between these two data sets. In fact, only one observation actually matches: and it already has the same values for NRC NRI RC and RM in both data sets!

This all leaves me thinking that there is important information about this situation which I have not grasped, or perhaps that I completely misunderstand what you are trying to accomplish. Please clarify.