Assign value of a variable by its orders.

[Davis Nguyen]

Hi,

This is my first post, so if I accidentally missed any posting rules, please let me know

I have 4 variables as below (avg_se, se_rank, j_order, j_rank_ln). I want to generate a new variable j2, which is:
- If se_rank is the highest, assign j2 the value of j_rank_ln with the lowest j_order.
- If se_rank is the second highest, assign j2 the value of j_rank_ln with the second lowest j_order.
- So on and so forth.

For example, se_rank=3, my new variabel j2 should be 3.02

I tried to do:

gen j2 =.

qui forval i=1/`r(max)' {
replace j2= j_rank_ln[`i'] if se_rank

}

But I just got stuck, becuase I don't know how to express second highest, second-lowest, third-highest, etc.

May you give me a lead of how to deal with this?

Thank you!

My data looks like:

 input float( se_rank j_order j_rank_ln)
 1 1 2.01
1 1 2.01
 1 1 2.01
 1 1 2.01
 1 1 2.01
 2 2 3.02
 2 2 3.02
 2 2 3.02
 2 2 3.02
 4 3 6.01
 4 3 6.01
 4 3 6.01
 4 3 6.01
 4 3 6.01
 4 3 6.01
 3 4 9.15
 3 4 9.15
 3 4 9.15
 3 4 9.15
 3 4 9.15
end

[Ali Atia]

When j_order is 4, you have two different values for j_rank_ln (-2.97593 and -2.748872). Which value should be selected for those cases?

Additionally, the example you provide doesn't conform with your description of what you want. When se_rank is second highest (i.e. 3), shouldn't the value of j2 be that of j_rank_ln when j_order is second lowest (i.e., j_order = 2 and j_rank_ln = -3.296837)?

[Davis Nguyen]

When j_order is 4, you have two different values for j_rank_ln (-2.97593 and -2.748872). Which value should be selected for those cases?

Additionally, the example you provide doesn't conform with your description of what you want. When se_rank is second highest (i.e. 3), shouldn't the value of j2 be that of j_rank_ln when j_order is second lowest (i.e., j_order = 2 and j_rank_ln = -3.296837)?

Hi,

Thanks for reply.

My data looks more complicated, so I modified a bit to post here, but accidentally made it more confusing. I did edit the data. So I hope it made more sense now.

If se_rank=3 I want my "j2" variable to be assigned 3.02, which is the value of j_order=2

[Ali Atia]

levelsof se_rank, local(se_ranks)
levelsof j_order, local(j_orders)
gen j2 = .
forv x = 1/`:word count `se_ranks''{
    levelsof j_rank_ln if j_order==`:word `=`:word count `j_orders''-(`x'-1)' of `j_orders'', local(j2)
    replace j2 = `j2' if se_rank == `:word `x' of `se_ranks''
}

[Davis Nguyen]

levelsof se_rank, local(se_ranks)
levelsof j_order, local(j_orders)
gen j2 = .
forv x = 1/`:word count `se_ranks''{
levelsof j_rank_ln if j_order==`:word `=`:word count `j_orders''-(`x'-1)' of `j_orders'', local(j2)
replace j2 = `j2' if se_rank == `:word `x' of `se_ranks''
}

Thanks a lot, that works for the example. I realized that in my sample j_order can be 1,2,2,3. Not 1,2,3,4. So, it should be something like:

 input float( se_rank j_order j_rank_ln)  
1 1 2.01
1 1 2.01  
1 1 2.01  
1 1 2.01  
1 1 2.01  
2 2 3.02  
2 2 3.02  
2 2 3.02  
2 2 3.02  
4 2 3.02  
4 2 3.02  
4 2 3.02  
4 2 3.02  
4 2 3.02  
4 2 3.02  
3 3 9.15  
3 3 9.15  
3 3 9.15  
3 3 9.15  
3 3 9.15
end

This means that se_rank= 2 and se_rank=4 should be assigned the same j2=3.02.
So, how can we modify the code? I know it's kind of dumb question, but I am trying to understand your code.

[Ali Atia]

Do you mean j2 should be assigned a value of 3.02 where se_rank = 3 or se_rank = 2?

[Davis Nguyen]

Do you mean j2 should be assigned a value of 3.02 where se_rank = 3 or se_rank = 2?

Yes (I should have modified my reply above). I think the idea here is we have the order of se_rank and order of j_order do not match (i.e 4 vs 3). So if they have different se_rank but same j_order, we assign j2 the same.

[Ali Atia]

levelsof se_rank, local(se_ranks)
levelsof j_order, local(j_orders)
gen j2 = .
if `:word count `se_ranks''!=`:word count `j_orders''{
    local word: word `=`=`:word 1 of `j_orders'' + `:word `:word count `j_orders'' of `j_orders'''/2' of `j_orders'
    local j_orders = subinstr("`j_orders'","`word'","`word' "*`=`:word count `se_ranks'' - `:word count `j_orders''+1',.)
}
forv x = 1/`:word count `se_ranks''{
    levelsof j_rank_ln if j_order==`:word `=`:word count `j_orders''-(`x'-1)' of `j_orders'', local(j2)
    replace j2 = `j2' if se_rank == `:word `x' of `se_ranks''
}

[Davis Nguyen]

It works perfect, super useful. Thank you

[Davis Nguyen]

levelsof se_rank, local(se_ranks)
levelsof j_order, local(j_orders)
gen j2 = .
if `:word count `se_ranks''!=`:word count `j_orders''{
local word: word `=`=`:word 1 of `j_orders'' + `:word `:word count `j_orders'' of `j_orders'''/2' of `j_orders'
local j_orders = subinstr("`j_orders'","`word'","`word' "*`=`:word count `se_ranks'' - `:word count `j_orders''+1',.)
}
forv x = 1/`:word count `se_ranks''{
levelsof j_rank_ln if j_order==`:word `=`:word count `j_orders''-(`x'-1)' of `j_orders'', local(j2)
replace j2 = `j2' if se_rank == `:word `x' of `se_ranks''
}

Just another quick question. This is for one group of data. I actually have 20 groups (Variable "Group" from 1 to 20), I did:

foreach k in Group {

levelsof se_rank, local(se_ranks)
levelsof j_order, local(j_orders)
gen j2 = .
if `:word count `se_ranks''!=`:word count `j_orders''{
local word: word `=`=`:word 1 of `j_orders'' + `:word `:word count `j_orders'' of `j_orders'''/2' of `j_orders'
local j_orders = subinstr("`j_orders'","`word'","`word' "*`=`:word count `se_ranks'' - `:word count `j_orders''+1',.)
}
forv x = 1/`:word count `se_ranks''{
levelsof j_rank_ln if j_order==`:word `=`:word count `j_orders''-(`x'-1)' of `j_orders'', local(j2)
replace j2 = `j2' if se_rank == `:word `x' of `se_ranks''

}
if Group ==`k'
}

It gave me the error "invalid systax". I was wondering if I can combine "local" with many groups.

Thank you!

[Ali Atia]

It's very difficult to provide useful advice or code when the data example you provide is not a realistic and representative sample of the actual data on which the code will be run. In order to avoid wasting both your time and mine, please share such a realistic and representative sample, including an example of at least two of the multiple groups to which you are referring.

[Davis Nguyen]

It's very difficult to provide useful advice or code when the data example you provide is not a realistic and representative sample of the actual data on which the code will be run. In order to avoid wasting both your time and mine, please share such a realistic and representative sample, including an example of at least two of the multiple groups to which you are referring.

Sorry about that. Here is the actual data for group 9 and 10 out of 20 groups. The idea is the same, the only differene is I have more than 1 group of data, I want to do exactly you did but within each group.

input byte Topics float(se_rank j_order j_rank_ln)
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 14 1 -5.809143
9 20 2 -4.828314
9 20 2 -4.828314
9 20 2 -4.828314
9 18 3 -3.506558
9 18 3 -3.506558
9 18 3 -3.506558
9 18 3 -3.506558
9 18 3 -3.506558
9 12 4 -1.8773173
9 11 5 -.8989421
9 11 5 -.8989421
9 11 5 -.8989421
9 11 5 -.8989421
9 17 6 -.58519006
9 2 6 -.58519006
9 2 6 -.58519006
9 4 6 -.58519006
9 2 6 -.58519006
9 15 6 -.58519006
9 17 6 -.58519006
9 2 6 -.58519006
9 5 6 -.58519006
9 8 6 -.58519006
9 10 6 -.58519006
9 4 6 -.58519006
9 2 6 -.58519006
9 2 6 -.58519006
9 21 6 -.58519006
9 6 6 -.58519006
9 15 6 -.58519006
9 2 6 -.58519006
9 21 6 -.58519006
9 10 6 -.58519006
9 22 6 -.58519006
9 2 6 -.58519006
9 19 6 -.58519006
9 10 6 -.58519006
9 10 6 -.58519006
9 15 6 -.58519006
9 8 6 -.58519006
9 6 6 -.58519006
9 2 6 -.58519006
9 4 6 -.58519006
9 2 6 -.58519006
9 2 6 -.58519006
9 1 6 -.58519006
9 2 6 -.58519006
9 2 6 -.58519006
9 2 6 -.58519006
9 4 6 -.58519006
9 17 6 -.58519006
9 10 6 -.58519006
9 2 6 -.58519006
9 4 6 -.58519006
9 13 6 -.58519006
9 3 6 -.58519006
9 2 6 -.58519006
9 22 6 -.58519006
9 10 6 -.58519006
9 10 6 -.58519006
9 4 6 -.58519006
9 2 6 -.58519006
9 10 6 -.58519006
9 5 6 -.58519006
9 22 6 -.58519006
9 8 6 -.58519006
9 2 6 -.58519006
9 10 6 -.58519006
9 17 6 -.58519006
9 10 6 -.58519006
9 2 6 -.58519006
9 21 6 -.58519006
9 2 6 -.58519006
9 4 6 -.58519006
9 3 6 -.58519006
9 10 6 -.58519006
9 2 6 -.58519006
9 5 6 -.58519006
9 15 6 -.58519006
9 2 6 -.58519006
9 4 6 -.58519006
9 19 6 -.58519006
9 10 6 -.58519006
9 7 6 -.58519006
9 15 6 -.58519006
9 13 6 -.58519006
9 4 6 -.58519006
9 5 6 -.58519006
9 5 6 -.58519006
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 2 1 -1.3093333
10 5 2 -.4019712
10 5 2 -.4019712
10 5 2 -.4019712
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 1 3 .12839322
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 3 4 1.7107302
10 4 4 1.7107302
end

[Davis Nguyen]

I am sorry for annoying, but this problem is just beyond my coding abilities. I think there are some modifications that I should clarify:

1. Smallest se_rank will match with the biggest j_order and second smallest se_rank will match with second biggest j_order, so on. However, let say we have se_rank can go to 100, but j_order is only up to 30.

So, a modification to deal with that: if we have 3 same j_order, then we will match 3 consecutive se_rank with that j_order and assign j2= j_rank_ln of that j_order.

2. The real data is as same as my previous topic. I just want to illustrate an example with 1 topic here:

input byte Topics float(se_rank j_order j_rank_ln)
1 1 3 1.24
1 2 4 1.56
1 3 1 0.52
1 4 4 1.56
1 5 2 0.86
1 6 1 0.52

In this example, at j_order=4 (biggest j_order), we have 2 observation, so, we will match se_rank=1 and se_rank=2 with j_order=4 and generate j2=1.56 for both se_rank=1 and se_rank=2
Similarly, at j_order=3, we have just 1 observation, so, we will match se_rank=3 with j_order=3 and generate j2=1.24
So on, at j_order=2, we have just 1 observation with j_order=2, so, we will match se_rank=4 with j_order=2 and generate j2=0.86
at j_order=1, we have 2 observation with j_order=1, so we will match se_rank=5 and se_rank=6 with j_order=1 and generate j2=0.52

I hope it is clear now.

Thanks again for your patience!

[Ali Atia]

Your rule is unclear. In the data example in #12, for Topics = 10, j_order =4 occurs 36 times. However, you only have five values for se_rank when Topics = 10 (1,2,3,4,5). According to your rule, would j2 for all five of those values be assigned the value of j_rank_ln when j_order = 4 and Topics = 10 (i.e., 1.71073)?

Similarly, for Topics = 9, the highest value of j_order is 6, and it occurs 76 times. However, you only have 20 values for se_rank. Would all 20 of those values be assigned the value of j_rank_ln when j_order = 6 and Topics = 10 (i.e., -.5851901)?

[Davis Nguyen]

Your rule is unclear. In the data example in #12, for Topics = 10, j_order =4 occurs 36 times. However, you only have five values for se_rank when Topics = 10 (1,2,3,4,5). According to your rule, would j2 for all five of those values be assigned the value of j_rank_ln when j_order = 4 and Topics = 10 (i.e., 1.71073)?

Similarly, for Topics = 9, the highest value of j_order is 6, and it occurs 76 times. However, you only have 20 values for se_rank. Would all 20 of those values be assigned the value of j_rank_ln when j_order = 6 and Topics = 10 (i.e., -.5851901)?

I see what you meant.

input byte Topics float(idd se_rank j_order j_rank_ln)
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 182 14 1  -5.809143
9 171 20 2  -4.828314
9 171 20 2  -4.828314
9 171 20 2  -4.828314
9 167 18 3  -3.506558
9 167 18 3  -3.506558
9 167 18 3  -3.506558
9 167 18 3  -3.506558
9 167 18 3  -3.506558
9 183 12 4 -1.8773173
9 166 11 5  -.8989421
9 166 11 5  -.8989421
9 166 11 5  -.8989421
9 166 11 5  -.8989421
9 168 17 6 -.58519006
9 175  2 6 -.58519006
9 175  2 6 -.58519006
9 180  4 6 -.58519006
9 175  2 6 -.58519006
9 162 15 6 -.58519006
9 168 17 6 -.58519006
9 175  2 6 -.58519006
9 178  5 6 -.58519006
9 179  8 6 -.58519006
9 165 10 6 -.58519006
9 180  4 6 -.58519006
9 175  2 6 -.58519006
9 175  2 6 -.58519006
9 161 21 6 -.58519006
9 163  6 6 -.58519006
9 162 15 6 -.58519006
9 175  2 6 -.58519006
9 161 21 6 -.58519006
9 165 10 6 -.58519006
9 177 22 6 -.58519006
9 175  2 6 -.58519006
9 164 19 6 -.58519006
9 165 10 6 -.58519006
9 165 10 6 -.58519006
9 162 15 6 -.58519006
9 179  8 6 -.58519006
9 163  6 6 -.58519006
9 175  2 6 -.58519006
9 180  4 6 -.58519006
9 175  2 6 -.58519006
9 175  2 6 -.58519006
9 176  1 6 -.58519006
9 175  2 6 -.58519006
9 175  2 6 -.58519006
9 175  2 6 -.58519006
9 180  4 6 -.58519006
9 168 17 6 -.58519006
9 165 10 6 -.58519006
9 175  2 6 -.58519006
9 180  4 6 -.58519006
9 169 13 6 -.58519006
9 173  3 6 -.58519006
9 175  2 6 -.58519006
9 177 22 6 -.58519006
9 165 10 6 -.58519006
9 165 10 6 -.58519006
9 180  4 6 -.58519006
9 175  2 6 -.58519006
9 165 10 6 -.58519006
9 178  5 6 -.58519006
9 177 22 6 -.58519006
9 179  8 6 -.58519006
9 175  2 6 -.58519006
9 165 10 6 -.58519006
9 168 17 6 -.58519006
9 165 10 6 -.58519006
9 175  2 6 -.58519006
9 161 21 6 -.58519006
9 175  2 6 -.58519006
9 180  4 6 -.58519006
9 173  3 6 -.58519006
9 165 10 6 -.58519006
9 175  2 6 -.58519006
9 178  5 6 -.58519006
9 162 15 6 -.58519006
9 175  2 6 -.58519006
9 180  4 6 -.58519006
9 164 19 6 -.58519006
9 165 10 6 -.58519006
9 181  7 6 -.58519006
9 162 15 6 -.58519006
9 169 13 6 -.58519006
9 180  4 6 -.58519006
9 178  5 6 -.58519006
9 178  5 6 -.58519006
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 187 2 1 -1.3093333
10 186 5 2  -.4019712
10 186 5 2  -.4019712
10 186 5 2  -.4019712
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 185 1 3  .12839322
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 184 3 4  1.7107302
10 188 4 4  1.7107302
end

So, I added another variable idd, which is we should use to count the number of occurrence, instead of counting the replication of j_order observations. In Topics=10, we have 36 obs but we only have 2 distinctive idd( i.e 184 and 188). So, we should match 2 se_rank with j_order=4.


Similarly to Topics=9, as you said we have j_order=6, it occurs 76 times, but now we should the idd to count the number of occurrence. And I want to do it within each Topics.

Hope it clears now and thank you again for your time.