tabstast: How to display the highest values in a single table?

[carla salas]

Dear Statalist,

I would like to make a table that shows the frequency of the 2 highest sales in the countries by year and month. As an example this is the data:

year	month	country	sales
2019	dic	ECU	1500
2018	dic	ARG	100
2018	nov	BRA	6000
2018	dic	COL	1000
2018	dic	PER	150
2019	nov	COL	25
2019	nov	PAR	43
2018	nov	ECU	25
2018	nov	ARG	50
2019	nov	MEX	45
2019	dic	USA	10000
2018	nov	MEX	700
2019	nov	ECU	43
2018	dic	BRA	80000
2018	nov	ARG	25000
2019	nov	PAR	45000
2018	nov	BRA	10000
2019	dic	BRA	1450
2019	dic	BRA	64000

The table I would like to obtain is this:

2018	sales (sum)
dic
BRA	80000
COL	1000
nov
ARG	25050
BRA	16000
2019
dic
BRA	65450
USA	10000
nov
PAR	45043
MEX	45

I have tried to make the table through the following command:

bysort year month: tabstat sales, by (country) stat(sum) col(stat) long

But it shows the results in several tables and not only in one. It also does not show only the two highest sales.


Thanks in advance.

Best

[Nick Cox]

Here is some technique. Please note suggestions at FAQ Advice #12 on presenting data examples. Yours needs some surgery before it can be used.

clear 
input year    str3 (month country)    sales
2019    dic    ECU    1500
2018    dic    ARG    100
2018    nov    BRA    6000
2018    dic    COL    1000
2018    dic    PER    150
2019    nov    COL    25
2019    nov    PAR    43
2018    nov    ECU    25
2018    nov    ARG    50
2019    nov    MEX    45
2019    dic    USA    10000
2018    nov    MEX    700
2019    nov    ECU    43
2018    dic    BRA    80000
2018    nov    ARG    25000
2019    nov    PAR    45000
2018    nov    BRA    10000
2019    dic    BRA    1450
2019    dic    BRA    64000
end 


bysort year month (sales) : gen sales1 = sales[_N] 
by year month : gen country1 = country[_N] 
bysort year month (sales) : gen sales2 = sales[_N-1] 
by year month : gen country2 = country[_N-1] 

gen when = cond(month == "nov", ym(year, 11), ym(year, 12)) 
format when %tmCY/N  

tabdisp when, c(sales1 country1 sales2 country2) 

----------------------------------------------------------
     when |     sales1    country1      sales2    country2
----------+-----------------------------------------------
  2018/11 |      25000         ARG       10000         BRA
  2018/12 |      80000         BRA        1000         COL
  2019/11 |      45000         PAR          45         MEX
  2019/12 |      64000         BRA       10000         USA
----------------------------------------------------------



bysort year month (sales) : gen wanted = inlist(_n, _N-1, _N)

list year month country sales if wanted, sepby(year month) noobs 
 
  | year   month   country   sales |
  |--------------------------------|
  | 2018     dic       COL    1000 |
  | 2018     dic       BRA   80000 |
  |--------------------------------|
  | 2018     nov       BRA   10000 |
  | 2018     nov       ARG   25000 |
  |--------------------------------|
  | 2019     dic       USA   10000 |
  | 2019     dic       BRA   64000 |
  |--------------------------------|
  | 2019     nov       MEX      45 |
  | 2019     nov       PAR   45000 |
  +--------------------------------+

[carla salas]

Thank you very much, Nick! This was very helpful, and problem solved.