How to select a specific value from a list of variables?

[Bruno P Nunes]

Dear Statalist fellows

I would like to create a new variable with the 2nd highest value from a list of 14 variables. These variables are continuous and specify the individidual's time with some condition.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(a b c d e f g h i j k l m n)
.5 .5 .5 .5 .5  .  .  . . . . . . .5
.5 .5 .5 .5  .  .  . 37 . . . . .  .
.5 .5 .5  .  . .5 10  . . . . . .  .
.5 .5 .5  .  . 24  .  . . . . . . 20
.5 .5 .5  .  .  .  .  . . . . . .  .
.5 .5 .5  .  .  .  .  . . . . . .  .
.5 .5 .5  .  .  .  .  . . . . . .  .
.5 .5  1  .  .  .  .  . . . . . .  .
.5 .5 19  .  .  . 24  . . . . . .  .
.5 .5  .  1  1  .  .  . . . . . .  .
end

How I can perform this selection?

I didn't find similar explanation on the problem in previous posts. Please let me know if a post already discussed this problem.

Best, Bruno.

[Clyde Schechter]

I don't recall seeing any other post with exactly this problem. There have been some similar ones, but it's easier for me to just write some code than to search for it even if it does exist:

// PREPARE FOR RESHAPE TO LONG LAYOUT
rename * value_*
gen obs_no = _n

// GO LONG
reshape long value, i(obs_no) j(vble) string

// ELIMINATE MISSING VALUES--THEY GET IN THE WAY
drop if missing(value)

// SORT WITH DESCENDING ORDER ON VALUE WITHIN OBS_NO
gsort obs_no -value

// PICK SECOND HIGHEST
by obs_no: gen second_highest = value[2]

// RESTORE ORIGINAL LAYOUT OF DATA
reshape wide
rename value_* *
order second_highest, first
drop obs_no

Note: I don't know where you're going with this from here. Suffice it to say that most things in Stata are easier to do in long layout than in wide. That's why the first part of my solution was to -reshape long-. Whatever comes next for you is also likely to be easier in long layout. So you might consider skipping everything from // RESTORE ORIGINAL LAYOUT OF DATA on and just keep working with long data.

[Bruno P Nunes]

Dear Clyde Schechter

Many thanks for your reply and tips - which are very useful.

You can sorted out the problem.

Best, Bruno.

[Nick Cox]

See also this recent thread: http://www.statalist.org/forums/foru...87-row-sorting

[Nick Cox]

Here is customised code for the second largest. (At a wild guess, egen's rowmax() was good enough for the largest.) As in Robert Picard's post in the thread cited in #4, rangestat must be installed from SSC.)

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(a b c d e f g h i j k l m n)
.5 .5 .5 .5 .5  .  .  . . . . . . .5
.5 .5 .5 .5  .  .  . 37 . . . . .  .
.5 .5 .5  .  . .5 10  . . . . . .  .
.5 .5 .5  .  . 24  .  . . . . . . 20
.5 .5 .5  .  .  .  .  . . . . . .  .
.5 .5 .5  .  .  .  .  . . . . . .  .
.5 .5 .5  .  .  .  .  . . . . . .  .
.5 .5  1  .  .  .  .  . . . . . .  .
.5 .5 19  .  .  . 24  . . . . . .  .
.5 .5  .  1  1  .  .  . . . . . .  .
end

mata:  
    mata clear
    real rowvector second(real rowvector X) {
        real colvector sorted
        sorted = select(X', (X' :< .))  
        sorted = sort(sorted, 1)
        return(rows(sorted) < 2 ? . : sorted[rows(sorted)-1])
    }
end

gen id = _n

rangestat (second) a-n, interval(id 0 0)

list

     +-------------------------------------------------------------------------------+
     |  a    b    c    d    e    f    g    h   i   j   k   l   m    n   id   second1 |
     |-------------------------------------------------------------------------------|
  1. | .5   .5   .5   .5   .5    .    .    .   .   .   .   .   .   .5    1        .5 |
  2. | .5   .5   .5   .5    .    .    .   37   .   .   .   .   .    .    2        .5 |
  3. | .5   .5   .5    .    .   .5   10    .   .   .   .   .   .    .    3        .5 |
  4. | .5   .5   .5    .    .   24    .    .   .   .   .   .   .   20    4        20 |
  5. | .5   .5   .5    .    .    .    .    .   .   .   .   .   .    .    5        .5 |
     |-------------------------------------------------------------------------------|
  6. | .5   .5   .5    .    .    .    .    .   .   .   .   .   .    .    6        .5 |
  7. | .5   .5   .5    .    .    .    .    .   .   .   .   .   .    .    7        .5 |
  8. | .5   .5    1    .    .    .    .    .   .   .   .   .   .    .    8        .5 |
  9. | .5   .5   19    .    .    .   24    .   .   .   .   .   .    .    9        19 |
 10. | .5   .5    .    1    1    .    .    .   .   .   .   .   .    .   10         1 |
     +-------------------------------------------------------------------------------+