Sum of losses above a certain quantile

[Nicolas Rodriguez]

Hello, I have a panel data of losses of a company and I want to get the sum of losses above a certain quantile (specifically p90 p95 p99 and p99.9). Also, the information depends on a categorical variable so I need that the proposed command take this into account.
I appreciate any help.

[Andrew Musau]

Also, the information depends on a categorical variable so I need that the proposed command take this into account.

This is ambiguous, but the following using egen may help.

webuse grunfeld, clear
foreach p in 90 95 99 99.9{
    egen val`=strtoname(string(`p'))' = pctile(invest), p(`p')
    egen wanted`=strtoname(string(`p'))'= total(cond(invest>val`=strtoname(string(`p'))', invest, .))
}
table, stat(mean wanted*)
drop wanted* val*

Res.:

. table, stat(mean wanted*)

----------------------
wanted_90   |  13315.8
wanted_95   |   8212.8
wanted_99   |   2791.1
wanted_99_9 |        0
----------------------

[Nick Cox]

In addition to Andrew Musau's helpful answer, I would want to underline what may well be obvious: you need a really large sample to get a good handle on the 99.9% point -- literally thousands of values.

[Nicolas Rodriguez]

Thank you very much andrew. Let me be more clear, I need that code include the event, for example, the sum of total losses above the p99 represent x% of total losses generated per event 1 and so on....

COMPANY	EVENT	LOSSES (MUS$)	YEAR
A	1	55	2000
A	3	76	2001
A	2	65	2002
A	1	99	2003
B	2	79	2000
B	2	14	2001
B	1	44	2002
B	3	34	2003
C	3	65	2000

[Andrew Musau]

Sorry, I cannot wrap my head around that now. Maybe someone else can. If not, present a more comprehensive example including what you expect the final output to look like.

[Nicolas Rodriguez]

For example, depending on which quantile losses belong (i.e. p90, p95 etc.) what percentage of total losses represents. For example, event 3 total losses is 175MUS losses (the sum of 76,34 and 65) and, for example 76 and 65 belong to p95 which means that above that only the area only 34MUS$ which will be part of an upper quantile (a % of total losses are in p90 other in p95 and so on). Thank you very much for your response

[Andrew Musau]

I am still not following. As I suggested in #5, create some variables showing what you expect the result to be for a couple of cases. Or wait and see if someone can understand your descriptions and reply.