[Bhaloe Singh]

I've, what I think, the same kind of question. I need to calculate the standard deviation of one-year cash ETR over the time period t-4 to t. One year Cash ETR is defined as cash taxes paid (TXPD) divided by pretax income (PI) less special items (SPI), which is PI_SPI. I've tried the commands above;

bysort GVKEY (date): egen SD=sd(TXR)

but this gives back for every GVKEY the same number, not for every year (date). Below is how my varlist looks like. Hope someone can help me in the right direction.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long GVKEY float(date TXPD PI_SPI ETR)
1004 15856    3.46   14.13  .24486907
1004 16222     4.2   10.39   .4042348
1004 16587   4.791  10.414   .4600538
1004 16952   6.094  59.803  .10190124
1004 17317   8.042 146.591  .05486012
1004 17683  19.454 262.884  .07400222
1004 18048   48.56 381.958   .1271344
1004 18413  78.709 450.448  .17473494
1004 18778  88.521 560.487  .15793587
1004 19144  99.939  667.86  .14964065
1004 19509 124.039  771.16   .1608473
1004 19874 141.339  876.46   .1612612
1004 20239 246.939  889.74  .27754062
1004 20605 282.639  949.44   .2976902
1004 21335 299.639 1026.64  .29186374
1013 16009       0    8.38          0
1013 16375     1.2   50.38 .023818975
1013 16740    10.2  153.28  .06654488
1013 17105    15.6  232.18 .067189254
1013 17470    28.5 347.945   .0819095
end
format %tdMon_DD,_CCYY date

*edit: forgot to include ETR

*edit2: Think I got the solution...

egen SD=sd(ETR), by(date)
                        bysort GVKEY (date) : generate SD_0= sum(SD)
                            by GVKEY: generate SD_5 = SD-SD[_n-5]

edit3: edit2 was not the solution, I think this was;

            
bysort GVKEY (date): generate ETR = TXPD/PI_SPI
                    egen SD=sd(ETR), by(date)

*edit4 (hopefully the last, and sorry for flooding it with edits haha): the previous edit, edit3, was the sd over a 2 year period(?), but I needed it over 5-years ; t-4 to t. So I tried this, and at first glance looks good.

                            by GVKEY: generate ETR_5 = ETR-ETR[_n-5] 
                                                        egen SD_5=sd(ETR_5), by(date)

[Clyde Schechter]

Your data is a little confusing because, on the one hand you have daily dates, but on closer look, for any given gvkey, they are spaced a year apart. I think if you extract the year from the date and then use -rangestat- you will get what you want:

gen year = yofd(date)
rangestat (count) ETR (sd) ETR, by(GVKEY) interval(year -4 0)

And you might want to delete the standard deviations for those observations that have fewer than 5 years available (this is up to you, depending on how you plan to use these results):

replace ETR_sd = . if ETR_count < 5

[Bhaloe Singh]

Your data is a little confusing because, on the one hand you have daily dates, but on closer look, for any given gvkey, they are spaced a year apart. I think if you extract the year from the date and then use -rangestat- you will get what you want:

gen year = yofd(date)
rangestat (count) ETR (sd) ETR, by(GVKEY) interval(year -4 0)

And you might want to delete the standard deviations for those observations that have fewer than 5 years available (this is up to you, depending on how you plan to use these results):

replace ETR_sd = . if ETR_count < 5

Thanks Clyde, I actually only need the year data instead of the var date, which also contains the ending month of fiscal year. With the previous date var the command xtset was working, but with

 xtset GVKEY year

it says; repeated time values within panel...

The thing is, I am planning to merge this dataset with another dataset with the GVKEY and year being the merging var m:m.

[Bhaloe Singh]

ignore #18
Had to use command

 xtset GVKEY year, yearly

which gave what I needed

[Clyde Schechter]

The thing is, I am planning to merge this dataset with another dataset with the GVKEY and year being the merging var m:m.

No, no, no, no, no! -merge m:m- is almost always a mistake and just produces data salad. Don't do it. You'll be very, very sorry--except you probably won't notice it until you've actually used the invalid data in layers of calculations and finally something shows up that's obviously, blatantly wrong. And at that point it will be a long, painful process to figure out what went wrong.

Post back with example data from both data sets, showing observations that should match with each other, and describe which observations should match with which, and I'll show you the correct way to do it.

There are very rare situations where -merge m:m- actually produces the usual result, but in 25 years of Stata use, I have only encountered 2 such situations, and in both situations there was a better way to do it anyhow. In short, I have never used -merge m:m-, and it would be very surprising if I ever do in my entire life.

[Attaullah Shah]

I shall also propose asrol here for getting rolling window statistics or group statistics. asrol is available on SSC and has several blog entries here.

* To Install asrol
ssc install asrol

* To find 5-years rolling window standard deviation, requiring at least 5 observation
bys GVKEY: asrol ETR, stat(sd) window(year 5) min(5)

Here is one more example using actual data

webuse grunfeld
bys company : asrol invest , st(sd) wind(year 5) min(5)

[Bhaloe Singh]

No, no, no, no, no! -merge m:m- is almost always a mistake and just produces data salad. Don't do it. You'll be very, very sorry--except you probably won't notice it until you've actually used the invalid data in layers of calculations and finally something shows up that's obviously, blatantly wrong. And at that point it will be a long, painful process to figure out what went wrong.

Post back with example data from both data sets, showing observations that should match with each other, and describe which observations should match with which, and I'll show you the correct way to do it.

There are very rare situations where -merge m:m- actually produces the usual result, but in 25 years of Stata use, I have only encountered 2 such situations, and in both situations there was a better way to do it anyhow. In short, I have never used -merge m:m-, and it would be very surprising if I ever do in my entire life.

Thank you, I was already getting weird outcomes. Since the first dataset should more or less be similar as previous studies, but it's way of. I'll try to explain what my objective is, I've got two datasets one containing the firm specific financial figures and one contains the CEO financial figures (5 CEOs per firm). Both datasets contains "GVKEY" and "YEAR", GVKEY the unique firm number and YEAR being the fiscal year. The main thing I want to do is make a regression with. from dataset 1; TOBINQ (dep), TAXRISK (indep), from dataset 2; CEOf(moderator), and the other variables are the controls. I think I wrongfully mentioned m:m merger.

dataset 1:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long GVKEY int YEAR float TOBINQ double TAXRISK float(TAXAVOID PTROA) double VOL_PTROA float(VOL_PTROAxPTROA NOL LN_SALES LEVERAGE FOREIGN CAPEX SALES_GROWTH RnD ADVERTISING INTANGIBLES DEPRECIATION)
1004 2002  534.9625                  .          . -.02838538                   .           .         0 6.407436  .3741715 0 -.50949204         . 0 0 .07270969 -1.394151
1004 2003  716.5151                  .          . .002406625                   .           .         0  6.47998  .3553656 0   6.025776         . 0 0 .06734885  15.62976
1004 2004  940.1655                  .          .  .02955219                   .           . .08290154   6.6172  .3153435 0   .6022922         . 0 0   .064533 1.2916493
1004 2005 1438.7303                  .          .   .0464805                   .           . .03987254 6.799372  .3278083 0   .3581853  .4798437 0 0 .04780148  .6422982
1004 2006 1799.5825 .15885049600141204 -.03022778    .081883  .04203762053354467  .003442167         0 6.967126  .3070868 0   .3419201  .6276647 0 0 .07131477  .3683211
1004 2007 1524.2985 .07570619555800268  -.3732283  .08523285  .03517654717773287 .0029981975         0 7.233397  .3898004 0   .2613018  .8518723 0 0 .09630325  .3441527
1004 2008 1292.2108 .09505089557477771  -.4198036  .08704758  .02633732760474748 .0022926007         0 7.261208  .3314558 0  .22963247  .5869847 0 0 .10961437  .3381815
1004 2009  1531.971   .177512044024802  -.8315912  .04276229 .022080702062020632 .0009442215         0 7.209452 .29114708 0   .4495389  .2742089 0 0  .1128736  .6064997
1004 2010 1917.7026 .16705069979054202  -.8943772  .06368567 .018862056884024998 .0012012428         0 7.481996 .26053295 0  1.1509267 .28222805 0 0  .1062946  .5464918
1004 2011 1816.2937 .15083701978266853  -.9782713  .04258824 .021744429553470862  .000926057         0 7.637475   .360874 0   .9754997  .4568349 0 0 .19704115  .8590938
end
format %ty YEAR

dataset 2:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int YEAR long GVKEY byte DFCEO float(CEOAGE CEOTEN) double CEOCOMP
2011 170617 0 27 14 928.833
2012 170617 0 28 14 769.306
2007  29517 0 29  5     210
2013  22447 0 29  2  61.364
2014  22447 0 30  2 389.583
2008  29517 0 30  5     210
2009  29517 0 31  5     595
2010   3424 0 32  8     900
2010  29517 0 32  5    1011
2008  29382 0 32 10  441.25
end
format %ty YEAR

After the merge, a regression is needed. I've cleaned both data sets, to my little stata knowledge it looks ready(?).
I've also enclosed my .do files.

[Bhaloe Singh]

I shall also propose asrol here for getting rolling window statistics or group statistics. asrol is available on SSC and has several blog entries here.

* To Install asrol
ssc install asrol

* To find 5-years rolling window standard deviation, requiring at least 5 observation
bys GVKEY: asrol ETR, stat(sd) window(year 5) min(5)

Here is one more example using actual data

webuse grunfeld
bys company : asrol invest , st(sd) wind(year 5) min(5)

Thanks! I just checked with the values of the other method and both give the same, however this (asrol) is a quicker way, and for me, a stata novice, it's a less error prone way of calculating it.

[Clyde Schechter]

Re #22: The data set examples you show look like they are both uniquely identified by gvkey and year. So what you need to do is: -merge 1:1 gvkey year using …-

\I have not reviewed the do-files you attached. I do not download attachments from people I don't know. If you would like the do-files reviewed, please post them here between code delimiters.

[Nick Cox]

The claim in #23 is obscure. What "other method" are you referring to that is "error prone"?

The syntax of asrol (SSC) and of rangestat (SSC) is different, but equivalent in all important respects for problems like that here.

[Bhaloe Singh]

Re #22: The data set examples you show look like they are both uniquely identified by gvkey and year. So what you need to do is: -merge 1:1 gvkey year using …-

\I have not reviewed the do-files you attached. I do not download attachments from people I don't know. If you would like the do-files reviewed, please post them here between code delimiters.

I totally understand, below is my .do file in code delimiters. I get an error when using merge 1:1 GVKEY YEAR. I suspect this is because in the second dataset, CEO data, there are multiple inputs of CEO data per GVKEY YEAR since wrds gave back 5 CEOs per firmyear. ;

variables GVKEY YEAR do not uniquely identify observations in the master data

Was thinking about creating a dummy variable, when per firm year CEO ≠ Female use the average of all rows per firm year CEO = male. In order to get 1 observation per firm year. However I am skeptical whether this is a respectable solution not causing harm for further analysis. (I have also contacted my dissertation supervisor to see if it's feasible to drop CEO info per firm year or not.)

Many Thanks for taking the time to help me.

//use F:\ for university usb and H:\ for home pc... 

import excel "H:\Thesis\FRESH\Scriptie DATA\FIRMDATA-prepped2.xlsx", sheet("WRDS") firstrow clear


*drop  variables not needed for calc
        drop IndustryFormat 
        drop LevelofConsolidationCompany 
        drop PopulationSource 
        drop DataFormat 

            drop if missing(ISOCurrencyCode)
            drop if ISOCurrencyCode == "CAD" 
            drop ISOCurrencyCode

*sic-industry exclution 
        drop if inrange(sic, 4900, 4999)
        drop if inrange(sic, 6000, 6999)

* drop for calc dependant variables; cusip, csho, txpd, pi, spi, at, prcc_f, ceq

rename CUSIP cusip
            drop if missing(cusip)
            drop if missing(csho)
            drop if missing(txpd)
            drop if missing(txpd)
            drop if missing(pi)
            drop if missing(spi)
            drop if missing(at)
            drop if missing(prcc_f)
            drop if missing(ceq)

*change string variables to numeric variables (deleting old -replace force)
    destring at capx ceq csho dlc dltt dp ib intan oancf pi pifo sale spi tlcf txpd txt txtubtxtr xad xrd mkvalt prcc_f, replace force

*fixing date data
        replace DataDate = " 31/05/2009" in 7
                generate gdate = date(DataDate, "DMY")
                    format %tdMon_DD,_CCYY gdate
        
            rename DataDate wrongdate
            rename gdate date
            rename DataYearFiscal YEAR
            rename gvkey GVKEY
    
    gen year = yofd(date)
    format %ty_CCYY year

                        xtset GVKEY YEAR, yearly

*Eliminate variable inputs leading to blank calculations;
** Done, x-observations ommited; 

        drop if missing(dltt)
        drop if missing(dlc)
        drop if missing(capx)
        drop if missing(sale)
        drop if missing(dp)

    **NOL/FOREIGN/R&D/ADVERTISING/INTANGIBLES [egen]

** Winsorize all variables

ssc install winsor

                    winsor at, gen(at_w) p(0.1)
                    winsor capx, gen(capx_w) p(0.1)
                    winsor ceq, gen(ceq_w) p(0.1)
                    winsor dlc, gen(dlc_w) p(0.1)
                    winsor dltt, gen(dltt_w) p(0.1)
                    winsor dp, gen(dp_w) p(0.1)
                    winsor ib, gen(ib_w) p(0.1)
                    winsor intan, gen(intan_w) p(0.1)
                    winsor oancf, gen(oancf_w) p(0.1)
                    winsor pi, gen(pi_w) p(0.1)
                    winsor pifo, gen(pifo_w) p(0.1)
                    winsor sale, gen(sale_w) p(0.1)
                    winsor spi, gen(spi_w) p(0.1)
                    winsor tlcf, gen(tlcf_w) p(0.1)
                    winsor txpd, gen(txpd_w) p(0.1)
                    winsor txt, gen(txt_w) p(0.1)
                    winsor txtubtxtr, gen(txtubtxr_w) p(0.1)
                    winsor xad, gen(xad_w) p(0.1)
                    winsor xrd, gen(xrd_w) p(0.1)
                    winsor mkvalt, gen(mkvalt_w) p(0.1)
                    winsor prcc_f, gen(prccf_w) p(0.1)
                    winsor csho, gen(csho_w) p(0.1)

        drop at capx ceq dlc dltt dp ib intan oancf pi pifo sale spi tlcf txpd txt txtubtxtr xad xrd mkvalt prcc_f






*_create variables
* tobin's q
    sort GVKEY (YEAR)
        by GVKEY: generate TOBINQ = ((prccf_w*csho_w)+at_w-ceq_w) if YEAR>2006


  
                    

    * TAXRISK
    ssc install rangestat
            bysort GVKEY (YEAR): generate ETR = txpd_w/(pi_w-spi_w) 

                    rangestat (count) ETR (sd) ETR, by (GVKEY) interval (YEAR -4 0)
                        replace ETR_sd = . if ETR_count < 5
                            rename ETR_sd TAXRISK
            drop ETR_count
        rangestat (count) ETR (sum) ETR, by (GVKEY) interval (YEAR -4 0)
                replace ETR_sum = . if ETR_count < 5
                        rename ETR_sum TAXAVOIDD
                            generate TAXAVOID = TAXAVOIDD * (-1) 
                                drop TAXAVOIDD
{                        
/*
    //Taxrisk
    // bys GVKEY: asrol ETR, stat(sd) window(YEAR 5) min(5)
    rename sd5_ETR TAXRISK

    //TAXAVOID
    // bys GVKEY: asrol ETR, stat(sum) window(YEAR 5) min(5)
    rename sum5_ETR TAXAVOID

                        
*/                        
                    
}            
            
                    * PTROA
                        bysort GVKEY (YEAR) : generate PTROA = pi_w/at_w 
                            
                            
                            * VOL_PTROA
                                rangestat (count) PTROA (sd) PTROA, by (GVKEY) interval (YEAR -4 0)
                replace PTROA_sd = . if PTROA_count < 5
                        rename PTROA_sd VOL_PTROA
                            
                            * VOL_PTROA * PTROA
                            
                            generate VOL_PTROAxPTROA = VOL_PTROA * PTROA 
                                {
                                /*
                                by GVKEY: generate PTROA_5 = PTROA-PTROA[_n-5]
                                    egen VOL_PTROA = sd(PTROA_5), by(YEAR)
                                */
                                    }
                    * NOL
                        bysort GVKEY (YEAR) : generate NOL = (tlcf_w/at_w) if YEAR>2006
                            recode NOL (mis = 0)
                            
                    * LN_SALES
                        bysort GVKEY (YEAR) : generate LN_SALES = ln(sale_w) if YEAR>2006
                        
                    * LEVERAGE
                        bysort GVKEY (YEAR) : generate LEVERAGE = ((dltt_w+dlc_w)/at_w) if YEAR>2006

                    * FOREIGN
                        bysort GVKEY (YEAR) : generate FOREIGN = pifo_w/pi_w if YEAR>2006
                            recode FOREIGN (mis = 0 )
                            
                    
                    * CAPEX
                        bysort GVKEY (YEAR) : generate CAPEX = capx_w/pi_w if YEAR>2006
                        
                    
                    * SALES_GROWTH
                        bysort GVKEY : generate SALES_GROWTH = (sale_w[_n]-sale_w[_n-3])/sale_w[_n-3] if YEAR>2006
                        
                    * R&D
                        bysort GVKEY : generate RnD = xrd_w/pi_w if YEAR>2006
                                recode RnD (mis = 0 )
                        
                    
                    * ADVERTISING
                        bysort GVKEY : generate ADVERTISING = xad_w/pi_w if YEAR>2006
                                recode ADVERTISING (mis = 0 )
                    
                    
                    * INTANGIBLes
                        bysort GVKEY : generate INTANGIBLES = intan_w/at_w if YEAR>2006
                                recode INTANGIBLES (mis = 0)
                    
                    * DEPRECIATION
                        bysort GVKEY : generate DEPRECIATION = dp_w/pi_w if YEAR>2006
    

//Merge commands;

use "H:\Thesis\FRESH\Scriptie DATA\2\part1.dta", clear
sort GVKEY YEAR
save "H:\Thesis\FRESH\Scriptie DATA\2\part1.dta", replace

use "H:\Thesis\FRESH\Scriptie DATA\2\part2CEO.dta", clear 
sort GVKEY YEAR

merge 1:1 GVKEY YEAR using "H:\Thesis\FRESH\Scriptie DATA\2\part1.dta"

dataset2: CEO data. thinking about the merge 1:1 option, I need to reduce my CEO input per firm year. However, the main objective is to get the dummy Gender of CEO, now I am not sure how to perform this. Deleting CEO=male per firm year, and keep CEO=female per firm year if available. Otherwise use average of the given CEO info per firm year?
In the code below I only created a dummy for CEO Gender.

* Prep for merge -> fix CEO DATA

import excel "H:\Thesis\FRESH\Scriptie DATA\CEODATA-prepped2.xlsx", sheet("WRDS") firstrow clear

*not needed
drop AllOtherCompensation
drop TotalCompensationAsReported
drop OtherAnnual
drop TotalCompensationSalaryBon

*rename var
    rename CompanyIDNumber GVKEY
    rename ExecutivesAge age
    rename FiscalYear YEAR
    rename SICCode sic
    rename ExecutiveIDnumber execid
    rename TotalCurrentCompensationSala CEOCOMP
*sic-industry exclution 
        drop if inrange(sic, 4900, 4999)
        drop if inrange(sic, 6000, 6999)



        generate dateBecame = date(DateBecameCEO, "MDY")
                    format %tdMon_DD,_CCYY dateBecame
                        generate YearBecame = yofd(dateBecame)
                            drop if mi(YearBecame)
                            
                            
    
        generate dateCLeft = date(DateLeftasCEO, "MDY")
                    format %tdMon_DD,_CCYY dateCLeft
                        generate YearCLeft = yofd(dateCLeft)
                            recode YearCLeft (mis = 2018)
                                recode YearCLeft (2019 = 2018)    
                                    drop if(YearCLeft-YearBecame)<0
                                    
            
    
{
        /*

        generate dateJoin = date(DateJoinedCompany, "MDY")
                    format %tdMon_DD,_CCYY dateJoin
                        generate YearJoin = yofd(dateJoin)


        generate dateRejoin = date(DateRejoinedCompany, "MDY")
                    format %tdMon_DD,_CCYY dateRejoin
                        generate YearRejoin = yofd(dateRejoin)
                            
        generate dateLeft = date(DateLeftCompany, "MDY")
                    format %tdMon_DD,_CCYY dateLeft
                        generate YearLeft = yofd(dateLeft)
                            recode YearLeft (mis = 2018)
                                recode YearLeft (2019 = 2018)    
            
             drop dateLeft dateJoin dateRejoin
            
        */
}    
                        
                        
                        
                        
*CEO TENURE    ; CEOTEN                    
    drop DateBecameCEO DateJoinedCompany DateRejoinedCompany DateLeftasCEO DateLeftCompany                
    drop dateBecame dateCLeft                    
        
        gen cbc=YearBecame
        gen clc=YearCLeft
            drop if(clc-YEAR)<0
            drop if(cbc-YEAR)>0
                
                gen monthsyearend=mdy(12, 31, (YearBecame)) if cbc==YEAR
                format monthsyearend %td
                gen months=(monthsyearend- YearBecame )/30
                
                gen monthsyearbegin=mdy(1, 1, (YearCLeft)) if clc==YEAR
                format monthsyearbegin %td
                replace months=(YearCLeft-monthsyearbegin)/30 if clc==YEAR
                
                drop if months<6
        bysort GVKEY YEAR: egen dubbel=count(execid)
        sort GVKEY YEAR
             
        bysort GVKEY YEAR: generate CEOTEN = YearCLeft - YearBecame
        
             
*CEO AGE

        bysort age execid: generate bornyear=YEAR-age
        bysort execid : generate rage = YEAR-bornyear
                net from https://www.sealedenvelope.com/
                
        *Lot of changes made by hand picking... see dta file to proceed.
        
        use "H:\Thesis\FRESH\Scriptie DATA\2\part2CEO.dta"
        
*CEO COMPensation

        drop if CEOCOMP<1
        
        
*CEO GENDER

            tabulate Gender, generate(dum)
            rename  dum1 DFCEO
            drop dum2
    
save "H:\Thesis\FRESH\Scriptie DATA\2\part2CEO.dta", replace
            
clear

[Bhaloe Singh]

The claim in #23 is obscure. What "other method" are you referring to that is "error prone"?

The syntax of asrol (SSC) and of rangestat (SSC) is different, but equivalent in all important respects for problems like that here.

I was referring to the one in #17, and by mentioning error prone I meant not that the method itself is but rather that I am when working in stata. Because for me, as a stata novice, I constantly have the feeling that I am making errors and messing up my dataset.

[Nick Cox]

#27 Thanks for the reply. Point taken, and that is why you come here.

Clyde Schechter is a very experienced user who has often applied rangestat, so his advice is good.

[Attaullah Shah]

I agree with Nick Cox that the syntax of asrol and rangestat are different, however, both the programs have their own merits.

[Bhaloe Singh]

#27 Thanks for the reply. Point taken, and that is why you come here.

Clyde Schechter is a very experienced user who has often applied rangestat, so his advice is good.

Yeah, I am very thankful for him helping me out. And just to be clear, I did not doubt his advice or method whatsoever but my own stata-abilities

I agree with Nick Cox that the syntax of asrol and rangestat are different, however, both the programs have their own merits.

Since both are giving the same results, I can use either of these methods to get the same results?