Panel Data: Dividing a Variable into Quartiles and Creation of Indicator Variable Based on the Quartile

[Andrea Bernasconi]

Dear all,

I have a panel data (based on firmID and year). For a cerain variable (VAR1) I would like to create quartiles for every years and subsequently create a new variable equal to 1 if the firm was in the first quartile, equal to 2 if the firm was in the second quartile and so on. Is there a feasible way to do this?

I really hope you can help me with this task. I thank you in advance

[Joro Kolev]

Probably the easiest way to do this is to install the user written -egenmore-

findit egenmore

and then follow the instructions on screen to install it.

Then you do:

egen quartiles = xtile(VAR1), nq(4) by(year)

[Kate Pryce]

As a follow-up to the above, is it possible to create quartiles by(year) as above but only if a certain variable is "on."

I'm working with S&P500 data and have a file with the sales data for all the companies that were in the S&P500 between 1965 and 2020. I'm interested in seeing which companies were in the top quartile of the S&P 500 each year, but all the ways I've tried to get a quartile variable include all data available that year, whereas I need to calculate the quartile just based on who was actually in the S&P500 each year. Does anyone have advice on how to do this? I could just drop all the variables when ActiveInSP500==0 but I hate dropping variables unnecessarily.

I tried:
egen quartiles = xtile(SalesTurnoverNet), nq(4) by(FiscalYear) if ActiveInSP500==1 but it returned the error: "option if not allowed."

 * Example generated by -dataex-. For more info, type help dataex clear input str6 gvkey int FiscalYear double SalesTurnoverNet float ActiveInSP500 "001010" 1964    304.1 1 "001010" 1965    323.2 1 "001010" 1966    342.6 1 "001010" 1967    276.5 1 "001010" 1968  270.225 1 "001010" 1969    320.2 1 "001010" 1970  333.718 1 "001010" 1971  332.704 1 "001010" 1972  343.679 1 "001010" 1973  436.146 1 "001010" 1974  535.097 1 "001010" 1975  571.564 1 "001010" 1976  639.903 1 "001010" 1977  713.019 1 "001010" 1978  824.945 1 "001010" 1979  991.705 1 "001010" 1980 1044.641 1 "001010" 1981  939.095 1 "001010" 1982  785.223 1 "001010" 1983  576.633 1 "001010" 1984  207.916 1 "001010" 1985  212.944 0 "001010" 1986  201.888 0 "001010" 1987  233.626 0 "001010" 1988  287.723 0 "001010" 1989   326.45 0 "001010" 1990  312.309 0 "001010" 1991  304.091 0 "001010" 1992  320.148 0 "001010" 1993  348.429 0 "001010" 1994    380.5 0 "001010" 1995    407.2 0 "001010" 1996    486.8 0 "001010" 1997    501.9 0 "001010" 1998    536.5 0 "001010" 1999    472.4 0 "001010" 2000    443.8 0 "001010" 2001    366.5 0 "001010" 2002    313.3 0 "001010" 2003   2339.1 0 "001013" 1974   13.787 0 "001013" 1975   15.692 0 "001013" 1976    18.51 0 "001013" 1977    23.19 0 "001013" 1978   25.344 0 "001013" 1979   40.021 0 "001013" 1980     55.9 0 "001013" 1981     61.5 0 "001013" 1982   81.654 0 "001013" 1983   76.342 0 "001013" 1984   87.595 0 "001013" 1985  124.883 0 "001013" 1986  143.677 0 "001013" 1987  166.863 0 "001013" 1988  179.852 0 "001013" 1989  196.388 0 "001013" 1990  259.802 0 "001013" 1991  293.839 0 "001013" 1992  316.496 0 "001013" 1993  366.118 0 "001013" 1994  448.735 0 "001013" 1995  586.222 0 "001013" 1996  828.009 0 "001013" 1997  1164.45 0 "001013" 1998 1379.678 0 "001013" 1999 1926.947 1 "001013" 2000   3287.9 1 "001013" 2001   2402.8 1 "001013" 2002   1047.7 1 "001013" 2003    773.2 1 "001013" 2004    784.3 1 "001013" 2005   1169.2 1 "001013" 2006   1281.9 1 "001013" 2007   1322.2 1 "001013" 2008   1456.4 0 "001013" 2009    996.7 0 "001013" 2010   1156.6 0 "001040" 1964    397.3 1 "001040" 1965    385.8 1 "001040" 1966    417.1 1 "001040" 1967    467.2 1 "001040" 1968    577.2 1 "001040" 1969    603.2 1 "001040" 1970  635.693 1 "001040" 1971  740.809 1 "001040" 1972   924.33 1 "001040" 1973  962.049 1 "001040" 1974 1020.302 1 "001040" 1975 1004.697 1 "001040" 1976  1111.36 1 "001040" 1977 1229.226 1 "001040" 1978 1316.382 1 "001040" 1979 1438.352 1 "001040" 1980  1534.03 1 "001040" 1981 1271.556 1 "001040" 1982 1054.218 1 "001040" 1983  970.519 1 "001040" 1984 1095.271 1 "001043" 1964    244.7 1 "001043" 1965    269.1 1 end

[Joro Kolev]

Try

egen quartiles = xtile(SalesTurnoverNet) if ActiveInSP500==1, nq(4) by(FiscalYear)

and this will generate quartiles just for the subset fulfilling the if condition. For the rest of the values you will have missings, which seems to be what you want.