[Carlo Lazzaro]

Qazi:
your interpretation of percentile seems a bit misleading.
The 75th percentile (a.k.a. quartile) of a given variable represents the value>=75% of the observations and <=25% of the remaining ones.
Hence, elaborating on your example, when adjusted for age, 25 children weight more than the one at 75th percentile.
Interesting pediatric growth charts are downloadable from: https://www.cdc.gov/growthcharts/index.htm

[Qazi Awais Amin]

Dear Carlo, I appreciate if you would be able to comments about 3 decile example which i have uploaded yesterday in green and yellow highlighted decile.

[Carlo Lazzaro]

Qazi:
I do share Weiwen's last reply.

[Qazi Awais Amin]

Dear Weiwen,
I appreciate if you would be able to suggest that how to develop top(bottom) decile in excel. I asked many people but no one know it and i am badly stuck now.

[Weiwen Ng]

Dear Weiwen,
I appreciate if you would be able to suggest that how to develop top(bottom) decile in excel. I asked many people but no one know it and i am badly stuck now.

Qazi,

I am not clear what you are asking.

If you want to know what percentiles (or quartiles, deciles, etc are), this is basic statistics. This is probably better taught in person. In terms of your Excel sheet, you had firms listed in columns A to D. I see you already sorted them. There are 40 firms. 10% of 40 firms is 4, so each decile represents 4 firms. What are the top 4 firms by sales volume? That's the top decile. What are the top 12 firms by sales volume? That's the top 3 deciles.

If you want to have Excel say what is the n-th percentile of sales volume, you would be better off stacking your firms in 2 columns. Then, you could use the percentile function. It might look something like

=PERCENTILE(B4:B43,.1)

That would present the cutoff for the 10th percentile (that is, starting from the bottom). So, you could say that 10% of firms have sales volume below the result. Note the decimal point, and note that you would have to change the cell references to capture the actual data.

I'm not able to offer more help than this.

[David Benson]

Qazi:

See if this helps. Some of the confusion is due to the paper you cite not being very clear. Moreover, that paper frequently uses the top / bottom 3 deciles thing, but seems to frequently then omit the middle group (i.e. 31 percentile to 70th percentile).

Reference
Yanhao Chang, Karen Benson, Robert Faff (2017). Are excess cash holdings more valuable to firms in times of crisis? Financial constraints and governance matters. Pacific-Basin Finance Journal. 45, 157–173 (Please see appendix page 172 description of payout ratio and Firm size)
https://doi.org/10.1016/j.pacfin.2016.05.007

For example, from Table 4 heading, top of p. 166
DOwner and DComp are governance dummies that equal 1 if a firm is identified as good corporate governance and 0 if identified as poor governance based on whether the firm was in the top or bottom three deciles of institutional ownership or top five executive compensation

[How do they code those in the middle 4 deciles? Are they omitted from the sample?]

As you mention, the authors define their constrained / unconstrained variable on p. 172:

P.172 of the Are excess cash holdings more valuable to firms in times of crisis? Financial constraints and governance matters
Small firms tend to be younger and less well known, and therefore have limited access to external
financial markets. Firms are ranked annually based on their total assets and assigned to the financially
constrained (unconstrained) group those firms in the bottom (top) three deciles.

I created a toy dataset to test this out (10 firm obs in 2000 (made it easy for deciles), 8 in 2001, 6 in 2002).
See if this gives you what you want:

* Example generated by -dataex-. To install: ssc install dataex
* dataex firm_id year sales, varlabels  // code I used to generate this
clear
input byte firm_id int year byte sales
 1 2000 12
 9 2000 13
 3 2000 19
 2 2000 22
 7 2000 25
 6 2000 44
 4 2000 49
 5 2000 53
 8 2000 65
10 2000 75
 7 2001 10
 5 2001 12
 3 2001 28
 1 2001 29
 6 2001 63
 8 2001 66
 4 2001 72
 2 2001 80
 1 2002 23
 5 2002 28
 4 2002 41
 6 2002 46
 3 2002 53
 2 2002 75
end
label var sales "Sales in ($) millions"

* Getting a sense for where cutoffs should be (if tabstat had a p30 and p70 cutoff, we'd be done)
tabstat sales, by(year) stats(n mean p25 p50 p75 p90)

Summary for variables: sales
     by categories of: year

    year |         N      mean       p25       p50       p75       p90
---------+------------------------------------------------------------
    2000 |        10      37.7        19      34.5        53        70
    2001 |         8        45        20        46        69        80
    2002 |         6  44.33333        28      43.5        53        75
---------+------------------------------------------------------------
   Total |        24  41.79167      22.5      42.5        64        75
----------------------------------------------------------------------

* Finding the decile cut points for year==2002 (could repeat for each year)
. centile sales if year==2002, centile(10(10)90)

                                                       -- Binom. Interp. --
    Variable |       Obs  Percentile    Centile        [95% Conf. Interval]
-------------+-------------------------------------------------------------
       sales |         6         10          23              23    39.79134*
             |                   20          25              23    45.50928*
             |                   30        29.3              23    51.34627*
             |                   40        38.4              23    62.52474*
             |                   50        43.5            23.5        72.8
             |                   60        47.4        25.83529          75*
             |                   70        52.3        31.07122          75*
             |                   80        66.2        41.49072          75*
             |                   90          75        46.65082          75*


* Could do this for each year like this:
by year: centile sales, centile(30 50 70)  

* Actual code
ssc install egenmore // in case you haven't installed it yet
egen sales_decile = xtile(sales), by(year) p(10(10)90)
sort year sales
brow
by year: egen firm_count = count(firm_id)
label var sales "Sales in ($) millions"
label var sales_decile "Sales decile by year"
label var firm_count "Number of firms in that year"
gen constrained = 0
replace constrained = 1 if sales_decile <=3 // constrained meant bottom 30% of firms that year
gen unconstrained = (sales_decile >=7)  // same as above, but does it on 1 line
gen size_group = 1 if sales_decile <=3
replace size_group = 2 if sales_decile >3 & sales_decile <7
replace size_group = 3 if sales_decile >=7
label var constrained "1 if firm in bottom 30% of sales that year"
label var unconstrained  "1 if firm in top 30% of sales that year"
label var size_group  "1 if firm in bottom 30%, 2 in middle, 3 in top 30% sales that year"
label define size_group_desc 1 "1-Bottom 30%" 2 "2-Middle sales" 3 "3-Top 30% sales"
label values size_group size_group-desc
label values size_group size_group_desc
centile sales if year==2002, centile(10(10)90)

[David Benson]

That was getting long, so broke this into 2nd post.

After all the above, you end up with:

. desc

Contains data
  obs:            24                          
 vars:             8                          
 size:           576                          
---------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
---------------------------------------------------------------------------------------------------------------
firm_id         byte    %8.0g                 
year            int     %8.0g                 
sales           byte    %8.0g                 Sales in ($) millions
sales_decile    float   %9.0g                 Sales decile by year
firm_count      float   %9.0g                 Number of firms in that year
constrained     float   %9.0g                 1 if firm in bottom 30% of sales that year
unconstrained   float   %9.0g                 1 if firm in top 30% of sales that year
size_group      float   %15.0g     size_group_desc
                                              1 if firm in bottom 30%, 2 in middle, 3 in top 30% sales that
                                                year
---------------------------------------------------------------------------------------------------------------
Sorted by: year  sales

* Sorted so sales go from smallest to largest (within a year)
list firm_id year sales sales_decile firm_count constrained unconstrained size_group, sepby(year)

     +--------------------------------------------------------------------------------------+
     | firm_id   year   sales   sales_~e   firm_c~t   constr~d   uncons~d        size_group |
     |--------------------------------------------------------------------------------------|
  1. |       1   2000      12          1         10          1          0      1-Bottom 30% |
  2. |       9   2000      13          2         10          1          0      1-Bottom 30% |
  3. |       3   2000      19          3         10          1          0      1-Bottom 30% |
  4. |       2   2000      22          4         10          0          0    2-Middle sales |
  5. |       7   2000      25          5         10          0          0    2-Middle sales |
  6. |       6   2000      44          6         10          0          0    2-Middle sales |
  7. |       4   2000      49          7         10          0          1   3-Top 30% sales |
  8. |       5   2000      53          8         10          0          1   3-Top 30% sales |
  9. |       8   2000      65          9         10          0          1   3-Top 30% sales |
 10. |      10   2000      75         10         10          0          1   3-Top 30% sales |
     |--------------------------------------------------------------------------------------|
 11. |       7   2001      10          1          8          1          0      1-Bottom 30% |
 12. |       5   2001      12          2          8          1          0      1-Bottom 30% |
 13. |       3   2001      28          3          8          1          0      1-Bottom 30% |
 14. |       1   2001      29          4          8          0          0    2-Middle sales |
 15. |       6   2001      63          6          8          0          0    2-Middle sales |
 16. |       8   2001      66          7          8          0          1   3-Top 30% sales |
 17. |       4   2001      72          8          8          0          1   3-Top 30% sales |
 18. |       2   2001      80          9          8          0          1   3-Top 30% sales |
     |--------------------------------------------------------------------------------------|
 19. |       1   2002      23          1          6          1          0      1-Bottom 30% |
 20. |       5   2002      28          2          6          1          0      1-Bottom 30% |
 21. |       4   2002      41          4          6          0          0    2-Middle sales |
 22. |       6   2002      46          6          6          0          0    2-Middle sales |
 23. |       3   2002      53          7          6          0          1   3-Top 30% sales |
 24. |       2   2002      75          9          6          0          1   3-Top 30% sales |
     +--------------------------------------------------------------------------------------+

.