Rank across variables

[Alberto Cano]

Hello, i have this dataset

MotherID	MIncome	FatherID	FIncome	BossID	SIncome
m1	80,113.37	f1	144,467.20	f1	144,467.20
m2	140,518.90	f2	139,320.60	f2	139,320.60
	0.00	f3	9,073.99	b1	240,253.20
	0.00	f4	190,642.30	f4	190,642.30
m3	74,388.64		0.00		0.00
m4	74,587.83		0.00		0.00
m5	93,724.77		0.00		0.00
	0.00	f5	248,177.80	f5	248,177.80
m6	278,946.80		0.00	m6	278,946.80
m7	65,540.44		0.00	b2	70,000.50
m8	215,373.70		0.00	m8	215,373.70
m9	281,698.60		0.00	b3	120,000.20
m10	207,903.10	f6	270,441.60	f6	270,441.60
m11	190,504.30	f7	212,657.80	f7	212,657.80
m12	214,129.70		0.00		0.00

and i want to result in a sort of rank, in which i get the id out of the three that has the highest income, as well as the id, and get the second highest id as well as the respective income. Basically i would like to have this table as a result

1ID	1Income	2ID	2Income
f1	144,467.20	m1	80,113.37
m2	140,518.90	f2	139,320.60
b1	240,253.20	f3	9,073.99
f4	190,642.30
m3	74,388.64
m4	74,587.83
m5	93,724.77
f5	248,177.80
m6	278,946.80
b2	70,000.50	m7	65,540.44
m8	215,373.70
m9	281,698.60	b3	120,000.20
f6	270,441.60	m10	207,903.10
f7	212,657.80	m11	190,504.30
m12	214,129.70

I hope i was clear, and thanks in advance for any help you can provide.

[Nick Cox]

This looks close to, but not close enough to, a Stata data example such as we request. See https://www.statalist.org/forums/help#version and then use dataex as asked.

[Alberto Cano]

Hello Nick, sorry for not reaching out before. But I think i was able to do the dataex. But the one i provided first is a madeup one, and the one im providing now is the actual one. Although the request is practically the same, its just that i dont have the result table, so im not sure if i made myself clear regarding what i need to do. I appreciate the support and thanks in advance.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str15 idmadre float ingbrutoM str15 idpadre float ingbrutoP str15 idjefx float ingbrutoJ
"1910623401-1-02"  159892.5 ""                        0 ""                        0
"0806007403-1-01" 159394.94 ""                        0 "0806007403-1-01" 159394.94
"1002401301-1-03" 159394.94 ""                        0 "1002401301-1-01"  185642.7
"0501261005-1-02" 159394.94 "0501261005-1-01" 37293.098 "0501261005-1-01" 37293.098
"2503763305-1-02"  159394.9 "2503763305-1-01" 144964.83 "2503763305-1-01" 144964.83
"2602215205-1-02"  159075.9 "2602215205-1-01"  807268.3 "2602215205-1-01"  807268.3
"1562062316-1-04"  158150.9 "1562062316-1-03"  189250.3 ""                        0
"1562062316-1-04"  158150.9 "1562062316-1-03"  189250.3 ""                        0
"1800785305-1-01"  158150.9 ""                        0 "1800785305-1-01"  158150.9
"3006170803-1-03" 157872.16 ""                        0 "3006170803-1-01"  63793.17
"0102186815-1-02" 157872.16 "0102186815-1-01"  927440.4 "0102186815-1-01"  927440.4
"0102186815-1-02" 157872.16 "0102186815-1-01"  927440.4 "0102186815-1-01"  927440.4
"3006170803-1-03" 157872.16 ""                        0 "3006170803-1-01"  63793.17
"1561808915-1-01" 156906.95 ""                        0 "1561808915-1-01" 156906.95
"3200906005-1-02" 156906.94 "3200906005-1-01"  234779.7 "3200906005-1-01"  234779.7
"0805732206-1-02" 156040.92 "0805732206-1-01" 226858.16 "0805732206-1-01" 226858.16
"1701368806-1-02" 155370.67 ""                        0 ""                        0
"1701368806-1-02" 155370.67 ""                        0 ""                        0
"1302122704-1-02" 155370.67 ""                        0 ""                        0
"0505091506-1-02"    154419 "0505091506-1-01"  203680.4 "0505091506-1-01"  203680.4
"2400454603-1-01" 154369.97 ""                        0 "2400454603-1-01" 154369.97
"1001852402-1-02" 153921.44 "1001852402-1-01"  385926.6 "1001852402-1-01"  385926.6
"2805028903-1-01" 153796.98 ""                        0 "2805028903-1-01" 153796.98
"2202341505-1-05" 153796.97 ""                        0 ""                        0
"0300928703-1-01" 153744.63 ""                        0 "0300928703-1-01" 153744.63
"1202224402-1-03" 152707.16 ""                        0 "1202224402-1-01"  158150.9
"1402850204-1-02" 152247.23 ""                        0 ""                        0
"1402850204-1-02" 152247.23 ""                        0 ""                        0
"0300928702-1-02" 152243.72 "0300928702-1-01"  453782.2 "0300928702-1-01"  453782.2
"1800510206-1-02" 151994.23 ""                        0 ""                        0
"3100590504-1-02" 151931.06 ""                        0 ""                        0
end

[Nick Cox]

This raises several small questions, not least what to do about ties, but thus may help.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str15 idmadre float ingbrutoM str15 idpadre float ingbrutoP str15 idjefx float ingbrutoJ
"1910623401-1-02"  159892.5 ""                        0 ""                        0
"0806007403-1-01" 159394.94 ""                        0 "0806007403-1-01" 159394.94
"1002401301-1-03" 159394.94 ""                        0 "1002401301-1-01"  185642.7
"0501261005-1-02" 159394.94 "0501261005-1-01" 37293.098 "0501261005-1-01" 37293.098
"2503763305-1-02"  159394.9 "2503763305-1-01" 144964.83 "2503763305-1-01" 144964.83
"2602215205-1-02"  159075.9 "2602215205-1-01"  807268.3 "2602215205-1-01"  807268.3
"1562062316-1-04"  158150.9 "1562062316-1-03"  189250.3 ""                        0
"1562062316-1-04"  158150.9 "1562062316-1-03"  189250.3 ""                        0
"1800785305-1-01"  158150.9 ""                        0 "1800785305-1-01"  158150.9
"3006170803-1-03" 157872.16 ""                        0 "3006170803-1-01"  63793.17
"0102186815-1-02" 157872.16 "0102186815-1-01"  927440.4 "0102186815-1-01"  927440.4
"0102186815-1-02" 157872.16 "0102186815-1-01"  927440.4 "0102186815-1-01"  927440.4
"3006170803-1-03" 157872.16 ""                        0 "3006170803-1-01"  63793.17
"1561808915-1-01" 156906.95 ""                        0 "1561808915-1-01" 156906.95
"3200906005-1-02" 156906.94 "3200906005-1-01"  234779.7 "3200906005-1-01"  234779.7
"0805732206-1-02" 156040.92 "0805732206-1-01" 226858.16 "0805732206-1-01" 226858.16
"1701368806-1-02" 155370.67 ""                        0 ""                        0
"1701368806-1-02" 155370.67 ""                        0 ""                        0
"1302122704-1-02" 155370.67 ""                        0 ""                        0
"0505091506-1-02"    154419 "0505091506-1-01"  203680.4 "0505091506-1-01"  203680.4
"2400454603-1-01" 154369.97 ""                        0 "2400454603-1-01" 154369.97
"1001852402-1-02" 153921.44 "1001852402-1-01"  385926.6 "1001852402-1-01"  385926.6
"2805028903-1-01" 153796.98 ""                        0 "2805028903-1-01" 153796.98
"2202341505-1-05" 153796.97 ""                        0 ""                        0
"0300928703-1-01" 153744.63 ""                        0 "0300928703-1-01" 153744.63
"1202224402-1-03" 152707.16 ""                        0 "1202224402-1-01"  158150.9
"1402850204-1-02" 152247.23 ""                        0 ""                        0
"1402850204-1-02" 152247.23 ""                        0 ""                        0
"0300928702-1-02" 152243.72 "0300928702-1-01"  453782.2 "0300928702-1-01"  453782.2
"1800510206-1-02" 151994.23 ""                        0 ""                        0
"3100590504-1-02" 151931.06 ""                        0 ""                        0
end 

gen long obsno = _n 

rename (idmadre idpadre idjefx) (idM idP idJ) 
reshape long id ingbruto, i(obsno) j(which) string 

egen order = rank(ingbruto), field by(obsno) 

reshape wide id ingbruto order, i(obsno) j(which) string 

list ingbruto* order*  

     +-----------------------------------------------------------+
     | ingbru~J   ingbru~M   ingbru~P   orderJ   orderM   orderP |
     |-----------------------------------------------------------|
  1. |        0   159892.5          0        2        1        2 |
  2. | 159394.9   159394.9          0        1        1        3 |
  3. | 185642.7   159394.9          0        1        2        3 |
  4. |  37293.1   159394.9    37293.1        2        1        2 |
  5. | 144964.8   159394.9   144964.8        2        1        2 |
     |-----------------------------------------------------------|
  6. | 807268.3   159075.9   807268.3        1        3        1 |
  7. |        0   158150.9   189250.3        3        2        1 |
  8. |        0   158150.9   189250.3        3        2        1 |
  9. | 158150.9   158150.9          0        1        1        3 |
 10. | 63793.17   157872.2          0        2        1        3 |
     |-----------------------------------------------------------|
 11. | 927440.4   157872.2   927440.4        1        3        1 |
 12. | 927440.4   157872.2   927440.4        1        3        1 |
 13. | 63793.17   157872.2          0        2        1        3 |
 14. |   156907     156907          0        1        1        3 |
 15. | 234779.7   156906.9   234779.7        1        3        1 |
     |-----------------------------------------------------------|
 16. | 226858.2   156040.9   226858.2        1        3        1 |
 17. |        0   155370.7          0        2        1        2 |
 18. |        0   155370.7          0        2        1        2 |
 19. |        0   155370.7          0        2        1        2 |
 20. | 203680.4     154419   203680.4        1        3        1 |
     |-----------------------------------------------------------|
 21. |   154370     154370          0        1        1        3 |
 22. | 385926.6   153921.4   385926.6        1        3        1 |
 23. |   153797     153797          0        1        1        3 |
 24. |        0     153797          0        2        1        2 |
 25. | 153744.6   153744.6          0        1        1        3 |
     |-----------------------------------------------------------|
 26. | 158150.9   152707.2          0        1        2        3 |
 27. |        0   152247.2          0        2        1        2 |
 28. |        0   152247.2          0        2        1        2 |
 29. | 453782.2   152243.7   453782.2        1        3        1 |
 30. |        0   151994.2          0        2        1        2 |
     |-----------------------------------------------------------|
 31. |        0   151931.1          0        2        1        2 |
     +-----------------------------------------------------------+

See also https://www.stata-journal.com/articl...article=pr0046

[Alberto Cano]

Hello Nick, i appreciate the support you've given me. Turns out I only need the highest amount and id. I fount a long way out through this:

gen idIngMax = cond(ingbrutoE==0,cond((ingbrutoM >= ingbrutoP) & (ingbrutoM >= ingbrutoJ),idmadre,cond((ingbrutoP > ingbrutoM & ingbrutoP >= ingbrutoJ),idpadre,cond((ingbrutoJ > ingbrutoP & ingbrutoJ > ingbrutoM),idjefx,"error"))),idpersonaestu)

It did give me the results i needed, so if you have by any chance a simpler way to do this, excellent. If not i thank you for your responses.

[Nick Cox]

I've given you one better way to do it. Are you asking for two?

PS As ties are sprinkled lavishly over your data, what you do about needs some flagging.

[Nick Cox]

Looking again at your code I first edit a little for readability

gen idIngMax =
cond(ingbrutoE == 0,
cond((ingbrutoM >= ingbrutoP) & (ingbrutoM >= ingbrutoJ), idmadre,
cond((ingbrutoP > ingbrutoM & ingbrutoP >= ingbrutoJ), idpadre,
cond((ingbrutoJ > ingbrutoP & ingbrutoJ > ingbrutoM), idjefx,"error"))), idpersonaestu)

and then note that this refers to variables not in your data example, which makes it harder to follow.

This code focuses on identifying which identifier is associated with the maximum numeric value and uses rules that if the mother (?) is first or first equal, you name the mother; otherwise if the father is first or first equal you name the father; otherwise you name the child.

And there are other rules I can't follow because they refer to other variables.

The link given in #4 discusses various commands, including commands written for the purpose, for row sorting and ranking. I just flag here that I deliberately avoided coding up return of the identifiers of the first value, second value, and so forth because ties in practice make that extraordinarily messy. It's one thing to report that values in a row are 1, 1, 31, 31, 52 after sorting and that ranks are whatever they are, but returning the identifiers requires arbitrary rules for ties, which remain the researcher's concern.