Hi Everyone, A bit of an interesting one. I have the below table: Source code: data Hierarchy;
infile datalines dlm='' truncover;
length L1_ID $20 L1_Name $20 L2_ID $20 L2_Name $20 L3_ID $20 L3_Name $20 L4_ID $20 L4_Name $20 L5_ID $20 L5_Name $20 L6 $20 L6_Name $20;
input L1_ID $ L1_Name $ L2_ID $ L2_Name $ L3_ID $ L3_Name $ L4_ID $ L4_Name $ L5_ID $ L5_Name $ L6_ID $ L6_Name $;
cards;
101 Anne 125 David 202 James 312 Anthony 404 Kiki
101 Anne 123 Chun 202 James 345 Judas 505 Drake
101 Anne 125 David 231 Chris 345 Judas 542 Alice
101 Anne 123 Chun 231 Chris 391 Jacob
101 Anne 123 Chun 234 Atem
101 Anne 125 David 239 Lucas
101 Anne 125 David 266 Tim
101 Anne 125 David 231 Chris
; Context: The key to unlocking this question lies in the last level of every row i.e. the rightmost level present. - The level above the last level in every case is the manager. e.g. on row one, Anthony (at L4) is the Manager of Kiki (at L5) and at the last row, David (at L2) is the Manager of Chris (at L3) Objective: Objective is to create a new variable called key. The value assigned to key is: - a concatenation of all the lowest (leftmost) levels assigned to a manager. e.g. take a look at obs 2 and 3, Judas is the Manager of Drake and Alice. So the value of key for these two observations will be "Drake|Alice". Another example, for observations 6 to 8, David is the manager of Lucas, Chris and Tim. For each of these, the value of key will be "Lucas|Tim|Chris"(OR - Where only one staff is assigned to a manager (e.g at obs 1,4 and 5) the value assigned to key is that staff's name. e.g the value of Key for obs 1 is "kiki" Expected result: Please can anyone help? Thanks
... View more
