Announcement

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

  • Calculate Difference Between Two Values in Same Group

    Good afternoon,

    I start with two variables idvariable and score.
    I run a small code to rank, get max score, get difference from max score
    I need the 'wanted' var to return (if the max_score_diff value is 0) the second lowest value in the max_score_diff and reverse the sign (turn it into a negative)
    In the example; the second lowest value in the max_score_diff variable is 2. So I need -2 to be returned in the wanted column (adjacent to the max_score_diff value of zero). All other values in the wanted var will stay the same as the adjacent value in max_score_diff.

    Thank you

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input byte(idvariable score) float(rank max_score max_score_diff wanted)
    1 77  1 77  0 -2
    1 75  2 77  2  2
    1 75  2 77  2  2
    1 75  2 77  2  2
    1 74  5 77  3  3
    1 74  5 77  3  3
    1 74  5 77  3  3
    1 73  8 77  4  4
    1 70  9 77  7  7
    1 66 10 77 11 11
    end
    bysort idvariable: egen rank = rank(-score),track
    egen max_score = max(score), by(idvariable)
    gen max_score_diff = (max_score-score)

  • #2
    Code:
    bysort idvariable: egen rank2score = max(score * (rank == 2))
    
    gen wanted2 = max_score_diff
    replace wanted2 = (rank2score - score) if max_score_diff == 0
    Last edited by Ken Chui; 06 Dec 2021, 06:35.

    Comment


    • #3
      Note that the "second lowest" value is ambiguous. For example, if two or more values tie for lowest, then perhaps you want one of them. In any case, rank 2 won't be assigned with your code in that case.

      Comment


      • #4
        Thanks both. That code will do the job fine.

        Comment

        Working...
        X